Best Performance: Data Centric Cube (very detailed) Tabular cube or Pivot reading a View

Ismail Cassiem 226 Reputation points
2021-09-09T11:02:35.897+00:00

Good day,

I have a data set of 2million records, however finance users setup OLAP Tabular via Excel with lots of attributes to see detail
they don't filter down the view, they like seeing all Age records for current day vs history day snapshot

or should i scrap the cube, make users think its a cube but have pivot connected to View

i think performance wise it will be better than tabular cube as the cube struggles when viewing 5000 rows, But the view option doesn't allow for calculations but knowing the audience they going to setup multiple templates with the goal analytical view in PowerBI performance is going to be key

Thoughts or better ideas please?

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,245 questions
0 comments

12 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Ismail Cassiem 226 Reputation points
    2021-09-21T10:43:27.41+00:00

    Hi,

    1. Is my thought idea correct, operational view of 200,000 records via pivot tabular or pivot from dw table?
    2. Is OLAP Tabular step forward from powerpivot and that 2019 pivots are faster using DAX compared to OLAP MDX?

    Please Help

    0 comments
  2. Ismail Cassiem 226 Reputation points
    2021-09-19T09:04:44.97+00:00

    Hi,

    I have posted on PBI and it can handle the operational view, i have 2 sets of users.
    Analytical & operational, so i want PBI & Tabular

    Is OLAP Tabular is step above PowerPivot and would O365 EXCEL19 not ProPlus be better due to it being DAX in pivot and not MDX?

    Please help!
    Thank You

    0 comments
  3. Ismail Cassiem 226 Reputation points
    2021-09-16T18:36:02.73+00:00

    Thank You, i have read that OLAP Tabular is the step forward of PowerPivot and hoping it works good with 0365 Excel19 as it uses DAX and not MDX that killed the Pivot, is my understanding correct?

    Please Advise as i will be inserting within Tabular over the weekend and PBI too

    Thoughts please?
    Thank You

    0 comments
  4. Ismail Cassiem 226 Reputation points
    2021-09-15T06:00:02.993+00:00

    Hi
    I would like a cube functionality in tabular and installed o365 to users but I'm afraid users wants it for znalyricdl and very detai.

    In detail view they want to filter and view detail records 100,000 and use operational where o can insert a link to a document in cube or pivot

    My issue is, working with tabular would be ok but not fast enough hence why I'm thinking power query or PowerPoint or connection to a dw rable and simulate a cube pivot to get better pivot performances as it a admjn dept using ghe cube daily to do their functions

    Kindly advise if I'm on the correct path
    Thought of powerbi but not sure if it will dork with easy filters of thousands of rows, very operational

    Also, in the cube or pivot I want an attribute to link uo a document, do I store the location in a table and how does it reference?

    Please help

  5. CarrinWu-MSFT 6,856 Reputation points
    2021-09-15T01:55:50.78+00:00

    Hi @Ismail Cassiem , sorry, I am not clearly with your question. Are you trying to create a cube? But your users would like to use Excel to connect the databases directly? If I didn't misunderstand, please refer to this link that you could know the difference between Analysis Service (SSAS) and PowerPivot. I think the performance is not the only one determining conditions.

    In addition, which model do you choose, tabular model or multidimensional model? Please refer to Comparing tabular and multidimensional solutions.

    0 comments