January 12, 2012 at 1:39 am
We had an issue where some data was not loaded into the relational side of our data warehouse around christmas.
Over the last few days we were able to recreate that data using a restore of the OLTP database from that time as well as the DW database from that time. Running the ETL process created the necessary data in the restored DW.
We inserted this data into the production data warehouse.
The fact data involved is a snapshot related to a single day. In other words, a set of rows for the date 31/12/2011 were inserted.
The cube is designed such that the time dimension is degenerate. That is to say, the relational view from which the fact data is pulled is also the source of the calendar dimension in this cube. The only level of the attribute hierarchy for this calendar dimension is the date.
After this I processed the relevant cube and used the SSMS cube browser to check the data. I pulled the calendar dimension into the filter but the date 31/12/2011 did not show up.
I then processed the calendar dimension indepenently of the cube. The member for 31/12/2011 now appeared, but it appeared as an empty cell. That is to say, the dimension member was in the list, but when I used it as a filter there was no fact data.
I do not understand how this is possible, since the table from which the dimension data is sourced is the same table as that from which the fact data is sourced. How can filtering by a degenerate dimension return an empty cell??
To put it another way, this MDX query:
SELECT ( { [Calendar].[31/12/2011] } ) ON COLUMNS FROM [Fleet]
returns null.
I am currently running a full reprocess of the entire database just to be sure, but since this takes some time I thought I might as well come and throw the question out ahead of time.
January 13, 2012 at 10:47 am
I don't think you have a degerate dimension just because you are populating it from the fact table. If fifty fact rows with the same date end up inserting one row in the date dimension, it would not be a degenerate dimension.
That said, however, why don't you populate your date dimension in advance? The values would already be waiting for the fact records' arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply