none
Tabular Model Design RRS feed

  • Question

  • Before I ask this question, I'd like to say that I am a completely self taught BI person and don't have any official training in dimensional data modeling or data warehousing. I have been in programming for 17 years and have been working with building and maintaining a complete Microsoft BI Solution for the last year.

    Initially, the BI solution started out as a Multi Dimensional Cube with HEAVILY flattened Dim and Fact tables.  It took a very long time to get the data from a relational design and into a dimensional format.  After that, I had a lot of problems with data validation/cube errors that made the process even longer.  After 3 months, I had a sales cube and some sales reports written in SSRS

    A few months later, I was asked to train a non-programmer to build cubes and write reports and after 3 months of on/off assistance and requests for more cubes/reports, I started moving to tabular models which is much more forgiving of unclean data and inexperienced developers.

    While researching tabular models, I read that tabular design is much closer to relational design than multi dimensional data and there was no more need for separate FACT tables.  I got excited because I thought this would speed up the model/cube design.

    I imported the same sales data BUT, instead of having 1-2 huge flattened tables, used relationships between the core tables (the way I had read about).  I tested the model in Excel and immediately found problems with the functionality.  Although this happened months ago, I still don't understand the behavior and are still having to heavily flatten the data because of it.

    Here is a copy of the model for reference (I'm also using SQL 2016 now instead of 2014)

    When I view the SSAS model in Excel and just select warehouse ID from the Invoice Header and Warehouse Name from the Warehouse table, the output is incorrect.  It shows every Warehouse Name listed under every Warehose ID instead of JUST the warehouse name for the Warehouse ID E.G.

    01
        Texas
        Oklahoma
        Colorado

    02
        Texas
        Oklahoma
        Colorado

    03
        Texas
        Oklahoma
        Colorado

    01 is Texas, 02 is Oklahoma and 03 is Colorado

    When I select a measure from the Invoice Header table, e.g. Invoice Price the problem is resolved and the data shows

    01
        Texas     $5,000

    02
        Oklahoma    $6,000

    03
        Colorado     $3,000

    (Sample Data obviously real numbers are much larger).

    As you can see in the model, I have set the filtering direction to both directions between the warehouse and the Invoice Header table but it doesn't change the behavior.

    In another model, I have a very similar setup using repair/service data relating to the products.  When I Select Warehouse from the Repair table and Sum of item cost from the repair items, I get the same total for every warehouse.  When I enable filtering to both directions, it then splits out the cost between the warehouses.  It's practically the same query as the prior sales model, but requires filtering to be set to both directions on the repair item table whereas, the sales model doesn't have that issue.

    The only way I can stop this inconsistent behavior is to heavily flatten the data so it is all in the same table but, that takes a lot of time to build and harder to maintain when changes are requested.

    Can anyone explain why I'm seeing these problems?  I am building the model in a relational fashion (like you are supposed to in a tabular model), I'm sure other people are having the same problem too.

    Sorry for the long post.


    • Edited by Phill D Tuesday, July 12, 2016 10:41 PM
    Tuesday, July 12, 2016 10:37 PM

All replies

  • Does anyone have any suggestions why I am seeing the problems in the Pivot table? This has to be a very common issue when building tabular models.
    Wednesday, July 13, 2016 3:36 PM