SSAS Tabular - When do you model data columns as separate tables/dimensions -- vs leave them in fact table?

Yordnoff, Brian 1 Reputation point
2021-02-17T16:15:33.537+00:00

I have an SSAS Tabular model with one fact table. I have columns for things like, customer, plant and item, but the nature of this data is such that: 1) we're only storing one attribute for each dimension -- i.e. a customer name, a plant name, a part code, and 2) each dimension has no more than ten or so distinct values -- for example five customers, three plants, and maybe a dozen items, and these will never grow significantly in number (our customers are the likes of Ford and Toyota to give you an idea). The underlying database correctly has customer, plant and item tables in a star schema, but I don't see the value of breaking these into separate dimensions in my tabular model (can easily join these up in a view). Seems like the hash encoding will handle this in an optimal way since each dimension is one column with a low number of distinct values. In general though, are there guidelines or best practices for when to break something off into separate tables/dimensions in an SSAS Tabular model? -- specifically in terms of the number of distinct values and data attributes? Thanks.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-02-18T03:33:26.98+00:00

    Hi,

    SSAS tabular has shifted the focus of dimension/cube to table and relationship.
    Since your dimension data has few distinct value, it would be find to have them in separate table and in database use keys to join them. This would save some hard disk space and faster to filter/aggregate. This is theoretically, in your case, the performance improvement would not be very significant if you have very few and simple dimension values. But it would definitely be helpful if your model grows more complicated in the future.

    Regards,
    Lukas


  2. Alexei Stoyanovsky 3,416 Reputation points
    2021-02-18T12:16:15.963+00:00

    There was that piece of DAX that had me move some column(s) into a separate table to make it work, but I can't seem to recall the particulars.
    Generally, the prevailing opinion seems to be that a star is the best shape for a tabular model, but I'd rather not break a single column off to a separate table, unless it really needs to stand out from the croud.

    0 comments No comments