question

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 asked IsmailCassiem-3967 answered

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @IsmailCassiem-3967,

According to your question, are you trying to improve Excel's Cube Performance? If yes, please refer to Improving Excel's Cube Performance.

And also, you could use Excel 2016 to get faster OLAP PivotTables. Please refer to Faster OLAP PivotTables in Excel 2016 and Excel 2016 PivotTable MDX Changes Lead To Big Query Performance Gains.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

Hi,

How do i upgrade to 2016, is there just an install i can send to users or is there a serial key involved?

but our company is looking to go 0365 ?

Regards

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

Other version of excel is not option now, how now?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered CarrinWu-MSFT commented

Hi,

  1. I have o365 account but i dont want the web excel, how do i active o365 windows or 0365 16/19 windows?

  2. if i have o365 web or 0365 19 with no click install would this improve the performance as the links?

Please help

131199-excel.jpg



excel.jpg (92.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @IsmailCassiem-3967, sorry for the later reply. Base on my researched, Microsoft 365 is the latest version. Maybe you could try to follow above links to make test in staging environment. And I am not familiar with Microsft 365, kindly recommand that please post this upagration problem with 365 tags, that will get a better help.

0 Votes 0 ·
IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

Hi, Thank You for helping

I installed the o365 office suite install from web that contains Excel19 but there's not much difference in performance for cube

i read that 0365 Click-To-Install (ProPlus) must be installed and not the default link and that Excel16 Click to install is faster than o365 excel19

please how do i convert the install to click-to-install proplus because olap pivots are not faster

?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

Would PowerQuery or PowerPivot using O365 with a generic userid in connection to table give better performance?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

Any ideas please, i would like a cube but users wants links to document and scroll the detail

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @IsmailCassiem-3967, 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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered CarrinWu-MSFT edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Based on my research, the total number of rows and columns on a worksheet is 1,048,576 rows by 16,384 columns in excel. For tabular model performance, good design and maintenance can help you to improve it. But I am not familiar with Power BI and PowerPoint. Kindly suggest that please post your question in Power BI forum and post your question with PowerPoint tag.

0 Votes 0 ·
IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.