MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.

In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes

Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.