Question on MDX

I am trying to write a calculated field. I need to calculate the Sum of a Item Sold over a period of time where 2 measures called BV value and Unit Price are zero. Can someone suggest what functions I should use to get this done

Thanks

Ann

Hello Ann,

Did you mean to use a calculated member? If yes, please, try the following:

WITH MEMBER [Measures].SumOfSoldItems AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[BV Value] = 0 AND [Measures].[Unit Price] = 0 ) , [Measures].[Item Sold] ) '
SELECT [Measures].SumOfSoldItems on 0
FROM [Adventure Works]

In the first argument of the filter function you should put a set representing the period of time that you are interested in.

I hope this helps

Greg

|||

Hi Greg

Thanks for your post. Thats exactly what I am trying to achieve. I tried using the above method, but it gives me a syntax error . It says, Syntax for "WITH" is incorrect.

Thanks

Ann

|||

Could you tell me what client application you are using? I wrote 2 more MDX queries for you so you can use them as a small tutorial. They both work with Adventure Works.

1. This one doesn't use calculated member but should give you the right results. After you try it, you can change the query to work with your own cube. Change the time dimension members ( [Date].[Fiscal Year].members ) to a set representing the time period you need. The two other measures in the filter you can change to [Measures].[BV value]=0 and [Measures].[Unit Price] = 0. The measure in the WHERE clause you may want to change to [Measures].[Item Sold]

SELECT filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 3500000 AND [Measures].[Order Quantity] > 90000 ) on 0
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

2. This is the query that I showed you yesterday but I changed it back slightly so now it works with Adventure Works. Try it first against AW and then change the time period and measures for your own needs as I explained in the first paragraph:

WITH MEMBER [Measures].x AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 0 AND [Measures].[Order Quantity] > 0 ) , [Measures].[Sales Amount] ) '
SELECT [Measures].x on 0
FROM [Adventure Works]

I hope this works for you. If it doesn't or you have more questions, feel free to ask :)

- Greg

Question on cube write back

Hi, all experts here,

I encountered a problem with cube writing back which contains measures with count aggregation rather than sum which said write back can not be allowed on measure groups with other aggregations rather than sum? Is it saying we can only enable write back on cubes with measure groups all based on sum aggregations?

But in my case, the measures needed to be counted rather than sum. What can I try to enable write back on this cube?

I am looking forward to hearing from you for your advices and thank you very much in advance.

With best regards,

Yours sincerely,

I'm having trouble thinking of a situation where you would need to writeback to a count measure, but that is beside the point.

One work around might be to create a new column in your fact table for this measure and pre-populate it with a value of 1 during ETL. Then you would be able to use a sum aggregation and get the same value as you would with a count measure. It would then also be possible to write back to this measure.

|||

Hi,

In my data, the facts are bit data, which only with values of 1 and 0 meaning different things respectively. Therefore I need to count these different situations happened in the fact table across different levels. There are no numeric data types at the moment which are for sum.

Thanks for your advices and I am looking forward to hearing from any of you here more ideas in this situation. Maybe you would give me some better ideas on how to deal with it.

With best regards,

Yours sincerely,

|||

Helen,

Explain me better what is the goal, I didnt understood... Explain me as I a dummy!

:-)

Regards!

|||

HI, PedroCGD,

The facts in the facts tables are with 0 or 1 values only. (0 and 1 therefore represent different meanings), therefore I dont see any point to sum these facts in any cube. Instead, count aggregation makes more sense which can count the numbers for the fact being 0 or 1.

Hope my explanation is clear for your help.

With best regards,

Yours sincerely,

|||It sounds like you have some flags which should modelled as separate attributes, not as measures. You would have one count measure and set either a single dimension or a combined dimension (also known as a 'junk' dimension). In this way you should be able to get the count by any combination of attributes.|||

Hi, Darren,

Thanks for your kind advices.

As you kindly advised, I will then need to update the underlying relational database (as the underlying data warehouse is designed by others)?Since at the moment, the data residing in the data warehouse does not have any junk dimensions at all. All those flags attributes are residing in the fact tables.

I would need to create single dimension for each flag attribute or combine some of the flag attributes together into one single dimension? But only one count measure for all these dimension attributes?

Will it be any good to complete these in data source view of the cube? Thanks again. And hope you can give me more further advices on it.

With best regards,

Yours sincerely,