CODEWITHSUNDEEP
ssasdata-warehousedimensional-modeling
Gabe
Gabe

Reputation: 6055

Best way to change granularity of fact table?

We have a cube created years ago and we have a request to change the grain. To simplify my scenario, the fact contains dimensions for date (daily), Market (Latam, North America, etc) and a measure for Count of Visits.

There's a new request to include Country and State. This is what I'm thinking:

Any other things or gotchas I should think about?

Upvotes: 0

Views: 1593

Answers (1)

zernager
zernager

Reputation: 54

If all reporting comes from the cube and the fact table is only to support that I would advocate adding a new fact table and stitching them together downstream. The method depends on your requirement.

Your country and state are associated with the same attribute of fact data that drives Market.

In this case the natural way to model this would be to add the extra grain to the existing dimension for Market. You can do this in a few steps: like this if memory serves:

  1. Your market data should be presented to the cube as a view. If it is not create one and in the DSV replace the table with "another table" and point it to the new view. Enhance this view so that it provides your combined geo info - a row for each state/country/market.

  2. Ensure there are records to which we can relate the old-grain facts. Theses will have the value for market reflected as the values for state and country too. (That is to say cascade your ragged hierarchy down to the key value).

  3. Create a row source for your old + new grain fact data. I presume these will come from separate sources. This can be a union all view of the two with the old [Market] reflected as [State] for the old fact data. Or you can have a view of views for each realizing them in you cube as separate partitions.

  4. Change the name of the key attribute hierarchy from [Market] to [State] (assuming State-->Country and State --> Market).

  5. Change its key/name from the key/name fields for market to those for [State] Add the attribute hierarchy for Country and a new one for Market again ensuring it has the same as before to avoid breaking old reports.

  6. Add an attribute relationship between County and Market (only if it is a strict parent for all fact records)

  7. In the Measure Group / Dimensions usage pane of the cube designer remove and re-add the direct relationship between fact and dimension.

This is designed to give you the smoothest fit with any MDX written against the old data. Your dimension will keep its name which may seem outdated. You could of course re-engineer things to give the dimension a new name if you are prepared to break old reporting that consumes from the cube.

Your country and state are independent of that attribute and (for instance) data could arise in a market of LATAM and a country of USA.

Then you simply need to add a new dimension which is easier.

  1. Create a rowset to build the new dimension from and make sure it has a row for Country = "UNKNON" and State = "UNKNOWN". It's always best to key dimensions with an integer and let's say we give this Record [GeoId]=0.

  2. Serve your new-grain data to the cube with a key for the new dimension. Your old fact data should be presented to the cube as a view and this view can have a literal value of 0 served for new column [GeoId] on all records.

  3. Union-all these together or serve them to separate partitions of the measure group.
    Add the relationship to the new dimension in measure group / dimensions usage.

This is a non-breaking change for almost all consumers from your cube data.

Upvotes: 2

Related Questions