January 17, 2011 at 9:19 am
Hi guys
I have created a Data Source Views containing quite a few Tables. Some are fact Tables other Dimensions Tables.
When I created my Cube (only 1 Fact Table and nultiple Dimensiojns) and the "wizzard" automatically created the associated Dimensions based on the Foreign Key constraints.
I have process the Cube but when I browse it it just returns null (no record) in the Count. I know that I have a few records in my database and cannot figure out why the Cube is empty.
When I create the Cube manually and include a single Dimension I get the expected results.
The only difference is that my Fact Table has NULL values in some of the Foreign Key columns. When I associate the related Dimension to the Cube I get nothing.
My question is:
How can you tell the Cube to process Dimensions but when the FK is NULL it doesn't go and look for a corresponding record in the Dimension?
(i.e I'd like to do an OUTER JOIN or 1-to-0,... whatever relashionship)
Thanks in advance.
Ludo
January 18, 2011 at 2:41 am
You need to look at the Unknown Member processing options in the Dimension model.
This article on technet should help you Unkown Member
Unfortunaltey you need to add the unknown member to each dimension for it to be effective.
another alternative is to add an unknown row to each dimension table, usually with an integer key of -1 or -99 then set all the Nulls to the unknown key.
Both ways are valid options, and it depends on how permanent the solution is and the business requirements.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 18, 2011 at 3:27 am
Hi
Thanks for your reply.
I have finally managed to get round my issue by modifying my DSV Dimension Tables in order to include a -1 Id associated to an "Unallocated" entry. I have also modifyed my DSV Fact Tables so they are now Queries which perform an ISNULL on the FK Columns so they show -1 instead of NULL.
This mean that my Fact Tables now are linked to my Dimension Tables via the -1 value wherever there is a NULL value.
Now I can see all my Data in the Cube.
Cheers
Ludo
January 18, 2011 at 6:46 am
No problem Ludo, glad to help.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 19, 2011 at 3:14 am
This mean that my Fact Tables now are linked to my Dimension Tables via the -1 value wherever there is a NULL value.
You may ultimately want to have more than one value for Nulls. I use 0 for Null values that shouldn't be Null (Unknown) and -1 for values that could or even should be null (N/A). A job that is open won't have a complete date (N/A in this case), but a completed job does have a complete date, and if that is Null then it is Unknown.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply