Logic behind the cube processing

Question

Hi All,
I am in the initial stages of creating a new cube, I have one FACT and 2 dimensions, DATE and SALES AMOUNT. All these are views in SQL Server. The Fact has 4million plus and the SALESAMOUNT Dimension has 100k plus rows. When I processed the cube , my dba complained that it is making calls to the SQL Server in millions. I am joining the PK on the dimension to the FK on the FACT, not sure why we are seeing 85 million reads. please advice.
Thanks

Answer 1

Which kind of SSAS cube are you using; classic multidimensional or tabular mode?

my dba complained that it is making calls to the SQL Server in millions.

I can't imagine this, on MD cube processing SSAS runs one query per dimension attribute + one per fact data.

Answer 2

Hi @Amy Jandreau ,
Did you create partitions?

Partitions make it possible for an administrator to better manage an OLAP cube and improve an OLAP cube's performance. For example, you can remove or reprocess the data in one partition of a measure group without affecting the rest of the measure group. When you load new data into a fact table, only the partitions that should contain the new data are affected.

Partitioning also improves processing and query performance for OLAP cubes. SSAS can process multiple partitions in parallel, leading to a much more efficient use of CPU and memory resources on the server. While it runs a query, SSAS fetches, processes, and aggregates data from multiple partitions as well. Only partitions that contain the data that is relevant to a query are scanned, which reduces the overall amount of input and output.

See more: Partitions (Analysis Services - Multidimensional Data).
Partitions in tabular models.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.