DCSIMG
September 2011 - Posts - Ella Maschiach's BI Blog

Ella Maschiach's BI Blog

BI, Olap Technologies, Tools, System Analysis and Design

News

About Ella Maschiach

Business Inteligence

September 2011 - Posts

Why are rows (still) missing from a SSAS cube?

Not too long ago my boss Yaniv got a telephone from one of our customers saying that he thought a cube was missing some values from it. The thing is that at first that didn't sound logical to us because usually we would get an error on that. We expected the process of the cube to fail, or something of that sort. But not this time. Now, there's a few reasons why there are rows missing from a cube as recently blogged by Richard Lees. But our reason was slightly different.

We were using a view in the DSV. The view in the DB was a union of the original dimension table and a referential integrity of the dimension against the relevant fact table. That way we could ensure not falling in the process. What we found out was that the cube process really didn't fail, but we didn't see all the values added through the union, just the original values. What was even weirder was that turning the view into a table in the DB and using that in the DSV gave us back those missing keys. Also, using a named query in the DSV of type: Select * From X also worked in bringing those values back. But adding to that query another filed such as: last_name + ' ' + first_name as Z, again made the added keys disappear…

So yes, I know the conclusion is to do it all in the ETL phase and get a readymade table and not a view (I know, I know!), but just in case you also have a view in your DSV with a union clause in it, then maybe you should run a few checks…
Posted: Sep 19 2011, 07:50 AM by Ella Maschiach | with 4 comment(s)
תגים:, , , , ,