Azure Analysis Services: Columns vs. Rows

James H. Robinson 161 Reputation points
2020-10-27T22:05:38.907+00:00

What causes a larger performance hit for AAS, having lots of columns or having lots of rows? For example, my client wants an AAS model that could potentially have 2,000+ columns. Would that cause a large performance hit on AAS?

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
444 questions
0 comments
Accepted answer
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-10-28T21:57:30.127+00:00

    Hello @James H. Robinson

    Thanks for the ask and also using the Microsoft Q&A.

    The answer to this questions is broad and often “it depends”.
    E.g. What performance KPI are we talking about – queries or refreshes (and metadata operations)? Having more columns can make metadata and refresh performance worse because of increase in cost of analyzing dependencies between objects.

    For queries also, it depends:

    • How many columns are involved in the typical queries? Joins involved?
    • Are the increases in columns in the fact table or in dimension tables? That can be a problem for refreshes
    • How many cores are available? If you have more rows, then having more cores will improve parallelism of each scan
    • Etc.

    Our recommendation is to avoid a very large number of columns. This usually leads to problems with client tools (metadata discovery can now become a lot slower), user experience (seeing 2000 fields in a field list)

    Thanks Himanshu
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest