Less data in cube than in fact table: Facts with corresponding member in all of the role-playing Date-Dimensions are considered only

1
votes

relational Datawarehouse / SSAS 2008 R2 / SQl Server 2008 R2

Less data in cube than in fact table: Facts with corresponding member in all of the role-playing Date-Dimensions are considered only.

I realized that I have less data in the “Cube” than in my fact table. The Count of the “Line Items” of the of the fact table SELECT Count(*) FROM [FactSales] does not correspond to the “Line Item Count” Measure in the Cube.

So holds less than one percent of the facts he should contain. The Data of many days is missing.

So I did some research. I excluded the most possible causes first (e.g. check out default members that are not the “All” member, check out processing option,…). Before I come up with the issue first the details about the model and the fact table.

The relational Data-warehouse follows the star-schema the Fact table holds Sales data. It has several foreign keys that point to one Date Dimension. This Date Dimension is represented in SSAS by several role-playing Dimensions. All these FK’s are NOT NULL. The Date Dimension has an Unknown member (in the underlying table). So the Null-Processing for the Date Dimension Key (Int) is Set to “Error”. The Dimension Usage is set up appropriately (Null-Processing: Error).

So the last thing I check out is: Can I can come up with query based on my fact table and its Dimensions that returns the same Count of Line Items I got in the Cube.

So finally that is the Query (Q1): 

SELECT COUNT(*) --cut-set
FROM Fact AS F 
INNER JOIN DimDate 
    ON F.Date1Key = Date.DateKey 
    AND F.Date2Key = Date.DateKey
    AND F.Date3Key = Date.DateKey

Of course that is wrong. It should be like this (Q2):

SELECT COUNT(*) --complete Set
FROM FactSales AS F 
INNER JOIN DimDate AS DimDate1 
    ON F.Date1Key = DimDate1.DateKey
INNER JOIN DimDate AS DimDate2
    ON F.Date2Key = DimDate2.DateKey
INNER JOIN DimDate AS DimDate3
    ON F.Date3Key = DimDate3.DateKey

But that is obviously not the Data (Q2) SSAS shows in the Cube. Unfortunately SSAS shows the data returned by the Q1 Test-query. So Q1 is just a small subset of the data. Facts that have a corresponding member in each of the Date-Dimensions (Date1,Date2, Date3) are considered only.

I do not know why this happen. It is very fundamental so basically it should work out in SSAS. Besides that it worked well in a previous version of the same Data Warehouse solution (already had role-playing Date Dimensions).

I guess it is some wrong configuration (whatever property). Anybody has an idea?

Cheers Patrick

1
sql-serversql-server-2008ssasolapssas-2008
I am investigating the same question, and have also opened a question here: stackoverflow.com/questions/19456009/… I see you don't have any answers yet, either. If it helps, my current theory is that it has something to do with Null Processing and handling the Unknown Member. But I haven't found the solution yet. If I do, I'll post it as an answer here as well. – Tab Alleman

1 Answers

0
votes

Here is a good article that explains a few reasons, but none of them were the case for me.

For me the issue was that I had SSAS partitions set up for date ranges. For instance, I had one for "less than 2012", "2012", "2013", and "greater than 2013" for a date field. What I neglected to consider was a few oddball instances where the date value was null. Those records with null dates were not appearing in the cube but obviously showed up in the fact table. I added one more partition for "date is null" and reprocessed. Jackpot!