As you said, poor design bring poor results and serious bugs. Give yourself a favor and fix it in the ETL right away... :-)
Miky
I agree with you that the thought process didn't work well here.. Still, I always post even mistakes, because I know that even if I made a mistake - I'm probably not the only one who ever made it...
Love your blog Ella. Must comment on this entry though.
You don't explain *what* use of UNION has to do with the missing rows. I can only assume you were using UNION rather than UNION ALL, and that something about the structure and data of your problem query causes the distinct that UNION does to make your rows disappear ? That would not happen though if the query included a unique identifier of each row. You don't reveal enough to understand.
Also, at least based on the facts provided, I see nothing wrong in principle with the use of views rather than doing the work in ETL, except perhaps in performance - but performance was not mentioned as an issue. You seem quite ready to agree with mikypuff that this was a case of poor design, but based on what you've revealed, I don't see it. The devil is in the details of course.
Hi Mark,
First of all, thank you for the compliment :)
Secondly, the Union was used to add a row to the original dimension. Basically, it added a fictitious worker with a key (-1) to the dimension to connect it to all the debts in the fact that were issued a fictitious office worker. We used Union and not Union All, though we could have used the latter (as there is never a worker with a negative ID). The original table didn’t have a metadata of a column being a unique key. In any case, I’m not sure that sort of metadata can be read in a named query we create in a DSV over a table. I don’t remember having that problem in the past so I am asking myself if it’s a matter of a bug in SSAS 2008 R2 or if I just didn’t notice that bug till now?....
I’m not saying using named queries or a view is bad in general. Still, in this example, we see that using a readymade table works fine whereas using a named query does not. In that case, I agree with Miky, that had I done it the ETL way, I would have avoided the whole problem.
I hope that added enough details on the matter. Otherwise, please don’t hesitate asking more questions.
Thank you for your feedback,Ella