DMR model - Issue while rolling up/down

[akp]

Hi,

I am facing an issue with the DMR model.

I am working on a dimension Corp > Country > State > City > Store. Now, each of these levels are free to do independent business also.

I have a table that contains details like Entity_Id, Sales. Here entity_id could be anything (Corp, Country, ..., Store). When I am using this as the fact table and doing a roll-up, I am not getting proper sales value on higher levels. It is not considering business done by any department directly and rolling up for values those are present on the Store level.

That is the reason, on Corp level the count is not correct.

I need something like this:

Level 1: Store id  - Sales
Level 2: Department id - Sales (all stores attached with this dept) + Sales made by this dept directly.
...
So on and so forth

Same logic should be applicable while rolling down also.

Pls suggest how to resolve this.

Thanks,
--
Sapp

[Lynn]

Can you create a Store member for each of the possibilities higher up the hierarchy? This would allow you to have all your facts at the same grain and still roll them up properly.

[cognostechie]

Hi,

I am facing an issue with the DMR model.

I am working on a dimension Corp > Country > State > City > Store. Now, each of these levels are free to do independent business also.

I have a table that contains details like Entity_Id, Sales. Here entity_id could be anything (Corp, Country, ..., Store). When I am using this as the fact table and doing a roll-up, I am not getting proper sales value on higher levels. It is not considering business done by any department directly and rolling up for values those are present on the Store level.

That is the reason, on Corp level the count is not correct.

I need something like this:

Level 1: Store id  - Sales
Level 2: Department id - Sales (all stores attached with this dept) + Sales made by this dept directly.
...
So on and so forth

Same logic should be applicable while rolling down also.

Pls suggest how to resolve this.

Thanks,
--
Sapp

If you are looking for 'Count' then that has to be understood and calculated in a completely different way than sales. The Entity_ID should be attached to the store, not to the country, corp or anything else. Every store is in a location and the location would attach that to the country with your demographic dimension. I am working for a retail company too and we have stores in different countries and we have different companies. The store dimension has a field called 'Entity_Code' and every store belongs to an entity (different companies in our case). The Fact table has the sales at a much more detailed level but it contains the Store_key in a field which ties it to the Store Dimension.

Your post indicates that you can have multiple stores in one department so the department is at a higher level and should not be below the Store level.

This is for the Sales. Count is not additive so you will have to do count distinct on the store key in the business layer instead of creating it as a roll-up measure.
dimension. The Sales for Entity (

[akp]

Hi Cognostechie,

The main problem in my case is all individual levels can also do business on its own, So, its not only a store that can do business but a city office, a country office, etc can also do separate business. So, the entity_id could be anything like store1 id or cityoffice1 id, or countryoffice1 id. If I use entity_id=store_id only and roll up to upper levels, business done by the upper levels will not be added.

This is a case of having multi grain data in the fact table which is not advisable at all. But somehow, we need to deal with it.

@Lynn,

I agree to your suggestion. This will need some extra analysis and time to flatten the hierarchy for all levels.

Let me try and come back.

Thanks both of you for your inputs.

-
Sapp

[MFGF]

Hi Cognostechie,

The main problem in my case is all individual levels can also do business on its own, So, its not only a store that can do business but a city office, a country office, etc can also do separate business. So, the entity_id could be anything like store1 id or cityoffice1 id, or countryoffice1 id. If I use entity_id=store_id only and roll up to upper levels, business done by the upper levels will not be added.

This is a case of having multi grain data in the fact table which is not advisable at all. But somehow, we need to deal with it.

@Lynn,

I agree to your suggestion. This will need some extra analysis and time to flatten the hierarchy for all levels.

Let me try and come back.

Thanks both of you for your inputs.

-
Sapp

The crux of this is that you need to separate your single fact table into multiple fact query subjects, one for each of the different grains you are holding facts at. These should then link to your dimension query subject based on Corp or Country or State or City or Store ID. You would also need determinants for each of these.

Your DMR should then based on these query subjects.

MF.

[Lynn]

The crux of this is that you need to separate your single fact table into multiple fact query subjects, one for each of the different grains you are holding facts at. These should then link to your dimension query subject based on Corp or Country or State or City or Store ID. You would also need determinants for each of these.

Your DMR should then based on these query subjects.

MF.

This is certainly a viable option to consider. I think that will leave you with a separate sales metric on each of the different fact tables, corresponding to each of the different grains, such as store sales, city office sales, country office sales, etc. This would be useful if your users have requirements to analyze sales that occur at different levels. If they also (or only) want a single sales amount to use in their reports then ideally you'd want a calculation in the model that brings all these together. I'm guessing a stand alone calculation would be the way to go to achieve that.

I think a clear understanding of how your users plan to use this metric might help you decide on the right approach.

[cognostechie]

Hi Cognostechie,

The main problem in my case is all individual levels can also do business on its own, So, its not only a store that can do business but a city office, a country office, etc can also do separate business. So, the entity_id could be anything like store1 id or cityoffice1 id, or countryoffice1 id. If I use entity_id=store_id only and roll up to upper levels, business done by the upper levels will not be added.

This is a case of having multi grain data in the fact table which is not advisable at all. But somehow, we need to deal with it.

What MFGF said is how we do it for our multi grain data (Sales and Sales Plan). Sales is at day,SKU, location level whereas Plan (like Forecast) is at Month and Product Class level.

Think of it this way. All offices, stores, cities etc which are selling something are individual work places for sales. Let's call it 'Selling Location'. This doesn't have to be in any hierarchy and can be fully independent from any grouping. So if you have 10 stores in one city, every store will have a store_id but also a 'Selling_Location_ID'. If you have 2 offices within the same city which are not stores, then they will also have a  'Selling_Location_ID'. if you have 1 office in another country without any stores there then that will also have a 'Selling_Location_ID'. Every 'Selling_Location_ID' will have it's demographic details like where it is on the map (city, state, country etc). So if you had a Selling_Location dimension with one line for each of those Selling Locations, then you would join this to your Fact table and then you can have the Selling_Location_ID as the lowest level in your hierarchy. It seems that the Entity_ID in your case seems to be the same as Selling_Location_ID. If not, you may want to assign each of those selling locations with a Selling_Location_ID and create a dimension. You can then make sure to populate the fact table with the Selling_Location_ID/Entity_ID and then use this as the lowest level in the hierarchy. This will ensure proper rollup. Instead of Store as a level, you can create Location as a level and create a calculated column which will contain Store_ID if it is a store and if that location is not a store, then the office_ID or the country_ID depending on what it is.

Ex: If it is a Store then 'Store ' + Store_ID else If it is an office then 'Office ' + office_ID ..... etc

This way the same field in the hierarchy will show where the sales come from.

So the hierarchy can be:

Corp > Country > State > City > Location

In the report, it can appear like this:

Location                                Sales
------------------------------------------

Store 001                          100,000
Store 001                            20,000
Office Las Vegas 101            10,000                       
City  New York 001                 5,000
Argentina                           200,000

In our case, we sell not only thru stores but also thru web sites and phone. Each of them is marked as a location so the sales orders received thru any of those contains the Location_ID which gets connected to the fact table. The web sales are independent of any stores or country but get rolled up properly because we are assigning them a Location_ID.