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

September 19, 2011

8 comments

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โ€ฆ

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

8 comments

  1. mikypuffSeptember 19, 2011 ื‘ 20:56

    As you said, poor design bring poor results and serious bugs. Give yourself a favor and fix it in the ETL right away… ๐Ÿ™‚

    Miky

    Reply
  2. Ella MaschiachSeptember 20, 2011 ื‘ 15:48

    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…

    Reply
  3. Mark Frawley (NYC, US)October 12, 2011 ื‘ 20:50

    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.

    Reply
  4. Ella MaschiachOctober 16, 2011 ื‘ 17:26

    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

    Reply
    1. Mike AustinJanuary 16, 2014 ื‘ 23:40

      I am using named queries in my *.dsv and it appears that the queries are not automatically re-processed during a cube deploy. I’ve gotten around this by explicitly reprocessing the dimensions that are using named queries before I deploy the cube.

      Reply
      1. Ella Maschiach BIJanuary 17, 2014 ื‘ 21:23

        Interesting solution Mike – thanks for the input! Hope it helps everyone reading this

        Reply
  5. JohnnieJuly 14, 2013 ื‘ 5:44

    Hi Ella,

    I’m having the same problem you mentioned in the post, though in the view I don’t have any UNION, just a ‘select *’ and casting a few fields to a different data types.

    I don’t think this issue has anything with the UNION operator, but rather the something with DSV in general, and this also happens randomly (only 3 of our customers (out of hundreds) in the cube was affected by this.

    Have you found any solution to this peculiar issue?

    Reply
  6. Ella MaschiachJuly 14, 2013 ื‘ 15:17

    Hi Johnnie,

    My solution, as I stated in the post, was to recreate the view as a regular table. All the Cast or Convert I made to different fields were regular fields with the needed data type in my table. That’s what helped me see all the rows.

    Hope that helps,
    Ella

    Reply