CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008mdx
Shahsra
Shahsra

Reputation: 1009

Need MDX query for total count

Last time I had posted a question which was a bit confused. But today I got the same question from my manager to get the MDX query.

Here is the scenario:

Fact Table 

Ticket No / Ticket ID   
1           S   
2           S
3           S   
3           D   
4           D

Dimension Table 

Ticket ID / [Count]
S           1
D           1

My manager said they are not using dimension table that they are not using just for understanding they have mentioned that. there is no use of considering it here.

So please ignore the Dimension table data.

The Output will be like this if we do group by based on Ticket ID: 

Ticket ID / [Count]
S           3
D           2

If we do so we will get the total Count is 

5

But I need the total count as 4 based on Ticket No.

Need help here.

Thanks in Advance.

Upvotes: 2

Views: 1322

Answers (1)

ic3
ic3

Reputation: 7680

My educated guess is that you're starting with OLAP/MDX. It's worth taking a bit of time reading in the web about MDX, something like MDX Gentle Tutorial.

Without a dimension you can not have a cube. The minimum is one dimension and one measure in your facts.

In your case

 Ticket ID -> dimension with two possible values (S,D)
 Ticket No -> the measure, as Aaron pointed out. use unique count as aggregation type.

The MDX would looks like :

Select
 {[Ticked ID].allmembers} on 0,
 {[Measures].members on 1
from [MyCube]

We could find other ways of solving this but they would be certainly slower and more complicated.

Upvotes: 4

Related Questions