January 30, 2017 at 2:24 am
I inherited a project where the FACT table contains references to (should be) related records in a Dimension table that does not exist in the Dimension table. As a result, the record in the FACT table is being excluded in the cube and not giving the right results.
I thought in these situations the results would just be in an unknown category of some kind but the results would still work.
Is there a way I can get the results to still be right without having to update the dimension table?
Simple example (sorry for crude example)
Dim_Yr
-------------------------------
Year ID
2016
2017
Fact_amount
-------------------------------
Year ID Amount
2016 1,000
2017 1,000
2018 1,000
2019 1,000
Problem: I want the result in the cube created to equal 4,000, but because the years 2018 and 2019 don't appear in the Dim_yr table for some reason when creating the cube those associated records are left out of the cube entirely and I'm left with only $2,000
January 30, 2017 at 2:34 am
Cube data warehouses relying heavily on foreign keys, I'm surprised that the 2018/2019 years could be entered if they don't exist in your Year Dimension (this implies that you don't have foreign key constraints).
One point of a dimension is it gives the distinct possible values for a known range, if it's missing in the dimension, it's not a true dimension. Why wouldn't you want to update it to be correct?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 30, 2017 at 4:55 am
I've seen far too many SSAS experts leave out foreign keys in the sources they build to fill the cubes,because they think the source it's coming from already has referential integrity, so the can speed things up on their side by not carrying the FK references into their model.
I hate that trend, it's a bad, bad habit.
Lowell
January 30, 2017 at 11:20 am
It was a very strange and messy setup so not surprised best practices were not followed. Anyway, to clarify...
When you say you're suprised "the 2018/2019 years could be entered". What I'm saying is that they are not being entered. There are two things here:
1) The underlying data warehouse (built in the the SQL database engine) which are designed with tables set up as dimensions and fact tables, and
2) The SSAS database designed with cubes and dimensions, etc..
The data integrity issue is in the datawarehouse. The person who set it up did not ensure any integrity rules or restraints, so the FACT table in the DW is happily accepting records with no associated record in the related dimension.
Then when procesing processing the cube in SSAS, becasue the relationship doesn't exist, it doesn't pick up the data in the cube.
Is there a way to get it to still pick up and throw it to "unknown" or something like that?
The reason I do not want to fix the underlying missing data is a long story. Basically, this will be ongoing problem and will occur regularly and client does want to spend time fixing long term. Long story, but please help.
thanks
January 31, 2017 at 4:36 am
ptownbro - Monday, January 30, 2017 11:20 AMIt was a very strange and messy setup so not surprised best practices were not followed. Anyway, to clarify...
When you say you're suprised "the 2018/2019 years could be entered". What I'm saying is that they are not being entered. There are two things here:
1) The underlying data warehouse (built in the the SQL database engine) which are designed with tables set up as dimensions and fact tables, and
2) The SSAS database designed with cubes and dimensions, etc..The data integrity issue is in the datawarehouse. The person who set it up did not ensure any integrity rules or restraints, so the FACT table in the DW is happily accepting records with no associated record in the related dimension.
Then when procesing processing the cube in SSAS, becasue the relationship doesn't exist, it doesn't pick up the data in the cube.Is there a way to get it to still pick up and throw it to "unknown" or something like that?
The reason I do not want to fix the underlying missing data is a long story. Basically, this will be ongoing problem and will occur regularly and client does want to spend time fixing long term. Long story, but please help.
thanks
It sounds as if whomever you inherited this project from has changed the error configuration of the measure group from the default setting, since in the default setting if a fact contains a dimension member that doesn't exist in the dimension (assuming there is a relationship defined in the dimension usage) then it will throw an error during processing and stop the process. In order to get these facts through and convert them to unknown you need to change a few things in this configuration.
A good breakdown of these configuration settings can be found here: https://paultebraak.wordpress.com/2012/05/31/ssas-measure-group-error-configuration-fact-and-dimension-key-errors/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply