Need help to create a mdx query in my cube

Question

Need help to create a mdx query in my cube

markuss21

Old Hand

Points: 340
More actions
March 26, 2006 at 10:07 am

#171715

Hi ! I working since a month on a olap cube.
Now I Have the following problem. I have a table with turnovers like this:
Customer   | ue_typ |   amount
1             |H           |    150
2             |H           |    25
3             |U           |    300
4             |U           |    70
No I need two columns in the cube.
One for the H-amounts and one for the U amounts.
How can I make this ?

Thanks for help !
Markus

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Depends a little on how/what you're trying to do what you're doing.

If the U and H within the ue_typ is a desriptive type of field, then this should remian a dimension, you still only have 1 measure (amount) and you could (with a front end tool or using MDX) create a query that puts the customer on rows and ue_type on columns with the values of Amount in the centre of the crosstab (e.g SELECT {Customer.Members} On Rows, {{ue_type.Members} * {Measures.Amount}} ON Columns FROM [YourCubeNameGoesHere] )

Alternatviely, if the ue_type really describes the amount allowing you to make it a measure, then you need to 'pivot' this data to get the eu_type measures. So the input sql for your view could look like:

SELECT Customer, CASE ue_type WHEN 'H' THEN Amount ELSE NULL END AS [H_Measure], CASE ue_type WHEN 'U' THEN Amount ELSE NULL END AS [U_Measure]

HTH,

Steve.

markuss21 Old Hand Points: 340 More actions · Answer 2

Thanks in advance !

But where put it in this sql ?

Regards,

Markus

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Again, it depends a little. Seeing as you've gone towards the SQL, then it sounds like you should modify (or create) the view that you are using as the source for your measures (i.e. the 'fact' table).

So, if you were using the table 'turnovers' directly as the fact table, create a new view in SQL that references turnovers but 'pivots' the data as described. Then go to the model and right click on the table in the model that has the yellow header (identifying it as the fact table) and select 'Replace' and from the list pick your new view. (*Note* you will need to refresh the data source for the catalog from within Analysis Manager prior to being able to see the view within this list).

When you do the replace you should now have two measure columns (whatever you named the 'H' and 'U' type CASE statement columns). You can now drag these into the measures folder of the model. Note that you'll most likely want to remove the 'Amount' measure otherwise you will be counting things twice (ie in the 'H' type measure *and* in Amount).

Steve.