• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using OLAP Tools for Pivot Table...

PP3321

PP3321

Active Member
Dear Chandoo Community,

Thank you always for your help.
Today, I have 1 problem.

I would like to start using Cube functions instead of Pivot Table.


But the problem is that OLAP Tools are greyed out.

*This page says that the source has to come from OLAP source data.
https://social.technet.microsoft.co...acae372c6432/olap-tool-activation?forum=excel

How can I create OLAP source data...?


*********************************************************************screenshot.png
*I created .cub file using notepad, but it did not work...
Do I need special software to create OLAP source data...?

Thanking you in advance...
 
@AVK
Sure.

Data source is on the worksheet.
I think this is why OLAP Tools do not work.

*I am using Excel 2010
 

Attachments

  • Chandoo Forum.xlsx
    13.9 KB · Views: 16
OLAP Tools will require PivotTables to be created from data model.

Be it Relationship builder from Data tab (requires 2 or more related tables), data loaded via PowerQuery into data model, or PowerPivot data model.

Since you have Excel 2010, you can download PowerQuery/PowerPivot as free add-in. Do note that any PowerPivot generated tables using newer version, will not work with 2010 (you won't be able to edit data model).
 
@Chihiro

Thank you so much for your reply. I hate disturbing you with further questions but when you have time could you please advise me further...

I installed PowerQuery.
I created merged table from 2 tables from worksheets.

But it did not work. OLAP Tools are still greyed out.

Does Data Model have to come from somewhere else, or can it come from worksheets...?

power query.png
 
So once PowerQuery is added, load the table into PowerQuery using "From Table".

Then in the editor, select "Close & Load to" and select as connection only and also check Add to Data Model.

Once that's finished. Go and insert PivotTable. Make sure that "Use this workbook's Data Model" is selected.

In PivotTableFieldList, make sure you use the table from data model.
upload_2017-5-1_9-44-32.png

Then highlight any cell in the PivotTable and go to Analyze ribbon tool.
OLAP Tools will be available and you can convert PivotTable to formula.
upload_2017-5-1_9-45-58.png
 
@Chihiro
Thank you so much for your detailed reply...

I followed your step but I realised that I do not have Add to Data Option...

I will keep trying but if there is something I missed could you please let me know...?


Load to.png
 
Hmm, I don't have 2010 version and can't test. But seems like PowerQuery does not have add to Data Model option for 2010.

In this case you need additional step to load to data model once connection is created.

Open PowerPivot (Manage Data Model) and either in Home tab or Design tab, locate "Existing Connection" button. Click and find the query built in PowerQuery and open.
 
@Chihiro
I downloaded PowerPivot, followed your instruction, and now it is working!

Thank you so much for your help.
I really appreciate and I can not thank you enough...

screenshot.png
 
You are welcome.

PowerQuery and PowerPivot are great tool to add to your arsenal. Lately, I've been spending most of my time in Power BI (desktop and subscription) and in Excel using Power tools.
 
No. Cube functions are used to evaluate based on data in the cube (data model). Without it, it won't do you any good.
 
@Chihiro
It strikes me that this exchange or a summary article would be worth archiving.
Since my job spec never involved access to corporate data, the creation of cube data models is unfamiliar territory for me and, I suspect, others.
 
Cube isn't necessarily corporate data. But any data loaded into data model.

In this case cube specifically refers to OLAP cube, which is multi-dimensional array of data. Typically stored as "Star Schema" or "Fact Constellation Schema" for analytical/reporting model, though it can be single fact table as well. Other schema are often used for transaction model (i.e. normalized for data entry rather than reporting).

Cube is somewhat misleading as it could have more than 3 dimensions and each dimension can differ in length/size as well.

https://en.wikipedia.org/wiki/OLAP_cube

Will see if I can write up something.
 
You must log in or register to reply here.
Back
Top