 |  | |
| | |
| | |
| | |
 | | |
|
|
|
| Data refresh breaks the cube |
|
|
I have a few cubes that worked great, except that after the relational store is reloaded and i redeploy the cubes, suddendly i get a ton of missing key errors. What is peculiar about them is that they all report the Unknown dimension member key as being not found...the unknown member keys all change each load but at the end of it they should all be the same, and i can see the key exists in the fact AND in the dim...what am i missing?
thanks
drew |
|
|
|
| |
|
|
| Re: Data refresh breaks the cube |
|
|
Drew, the relational data is reloaded and the keys change, but when the relational load is done, the keys match. Is that correct?
Now you try to reprocess the cube. What kind of processing are you doing? Full? Update? Add? |
|
|
|
| |
|
|
| Re: Data refresh breaks the cube |
|
|
yes that is correct.
i do a full all the time |
|
|
|
| |
|
|
| Re: Data refresh breaks the cube |
|
|
During your ETL process, do you load the data in the dimension tables and then add an Unknown record to each dimension table? And then, in the fact table load, do you do a lookup back to the dimension tables to see if the appropriate records exist? And if they don't, do you assign them to Unknown? Or do you let the processing options on the dimensions in SSAS handle the unknowns? |
|
|
|
| |
|
|
| Re: Data refresh breaks the cube |
|
|
| Thanks very much Craig
yes, the routines foillow a template where the dims are loaded from stage, whcih collects their sources, (some come from tables, some come from text, some from Excel etc) and land in a stage db. Once they are scrubbed the are inserted into scrubbed db, and from that db we build dims and facts. in the pop dim proces there is a second insert that adds a row with 'Unknown' that happens to get the highest pk value in the table, because its the last one in.
The Fact etl then reads its source, but references the newly populated dims in their Join clause (not a lookup...this was way pre SSIS and even pre DTS) to retrive their keys and inserts them into the fact. The assignment of unknown to Fact Orphans is managed like this; we create a local variable in the POP_DIM sprocs like this
SET @liAge_key = (SELECT MIN(age_key) FROM age_dim WHERE age_code = '199' AND age_month = 0) --this guarantees the unknown record's id is in @liAge_Key
so now, in the projection i can do this
[age_key] = ISNULL(age.age_key,@liAge_key)
No, i do not want to leave the unknown handling to the processing options, i want to control what happens explicitly.
thanks very much
drew |
|
|
|
| |
| | |
| | |
| | |
|