Adding a new dimension based on a key in fact table linked to one of the dimension tables

Question

I have a fact table that holds all date & time attributes as keys which links to actual DATE & TIME dimension. When I create a cube on top of it using SSAS 2005, these datetime attributes are split into individual dimensions for the CUBE, which is OK.

Problem is when I add a new datetime attribute to the fact table, my cube doesn't accept that and would not create a new datetime dimension just like the other ones, unless I recreate the cube from scratch.

Can anyone please suggest, how can I add this new attribute separately as a dimension, without having to recreate the cube?

Answer 1

I'm struggling to understand your issue.

It sounds as if you are trying to add a new datetime column(fact) (referenced to your apporpriate Dimension/s attribute) to the Fact table. If so, this changes the structure of the cube and so requires that the cube be re-processed.

To qualify correct use of terminology, a Dimension contains Attributes. A Fact table contains Facts not attributes.

The following reference may be of use.

http://msdn.microsoft.com/en-us/library/aa905984(SQL.80).aspx

Re: Comments

Any structural changes need to be applied/registered within the Data Source View (DSV) in the Business Intelligence Development Studio (BIDS), prior to processing the cube. Clicking the refresh button on the DSV, should prompt you with an option to apply any discovered changes to your tables. Also, should any of your additions/modifications be to the underlying tables of Dimensions, then you may also need to add the attributes in question to the appoprirate Dimension .dim file, prior to re-processing the cube.

Hope this makes sense.

Answer 2

The problem usually comes because of Unknown Member and Null Processing options setup along with the snowflake schema if you have it in your cube. I figured out what the problem actually was. If you have a case as one mentioned, then SSAS doesn't bring up the structural changes by itself when you refresh the Data source view. In my case, since it was date & time dimensions, I had to add new dimensions manually (Cube dimensions) and setting their NULL Processing options correctly (in my case UnknownMember and not Automatic).

Since it can be tad difficult to do these changes for all such new columns added to underlying fact table, you can try updating the XMLA script using Find & Replace method, carefully crafted.