In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.
Close Excel and Install add-in
3) Add simple measure
Open Excel.
Connect to Cube.
Let’s add a simple measure that calculates the difference between two measures.
So I created a simple Pivot Table that looks like:
Now let’s add a calculated measure:
Select the Pivot Table > Right Click > OLAP Pivot Table Extensions
On the next screen, please enter the name Difference. and the simple formula:
[Measures].[Meausre1]-[Measures].[Measure2]
(please replace measure1 & measure2 with the measure names from your cube)
And click on Add to Pivot Table
You can now see that the calculated measure Difference got added to the Pivot Table!
Note
1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.
2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.
That’s about it for this post! Your feedback is most welcome!
Once in a while I write about back to basics topics to revisit some of the fundamental technology concepts that I’ve learned over past few years. Today, we’ll revisit why do we use OLAP and Data Warehouses for business reporting systems. Let me share some of the most common reasons and then I’ll point to resources that offer other reasons.
Let’s see some of the most common reason:
#1: Business Reports should not take lot of time to load.
From a Business User Perspective: They don’t want to wait for their report to populate data. Reports should be fast!
But if business users have to wait for data to show up on their report because of slow query response, then that would be bad for everyone involved. Business Intelligence solutions cannot permeate in an organization if the reports take a lot of time to load:
So from a Technology standpoint: What can you do? And also why did the problem arise in the first place?
Let’s first see why the problem occurred in the first place?
So we have a bunch of database tables. To create a report, we’ll have to summarize (aggregate) values in lots of rows (think millions) and join few tables – turns out that if you query a transactional system (Database / OLTP), then you’ll get a slow response. In some cases, if the data model + data size + queries are not complex, then you could just run a query to create operational business reports and you won’t see any performance issues. But that’s not the case always! So if data model + data size + query requirements for reporting are not simple for OLTP/databases to handle and you see a poor performance – in other words, database/OLTP system takes up a lot of time returning data that the business reports require and the business users would see bad performance. The issue goes beyond complex data model + data size + query. You see, transactional systems may be running other tasks in parallel to returning data to business reports. So there’s the issue of resource contention on the OLTP database.
so that’s no good, right? Not only is the OLTP system bad at running queries needed for business reports but it also does not dedicate it’s resource for us!
So let’s create a copy of databases and have them dedicated to answer questions to business reports. so there’s not an issue of resource contention as we have dedicated resources to handle that. And while, we are at it – why don’t change the data model so that it best suits the queries that are needed for business reporting and analysis?
Well, that’s exactly what OLAP database is. It’s a database that’s created for business reporting and analysis. It’s does some neat things like pre-aggregating some values PLUS the data model in OLAP is also best suited for reporting purposes. (Read more about Star schemas/ data mart / data warehouse / ETL if you’re curious to learn more).
OK – so that’s one reason: To improve performance! Now let’s see another one.
#2: Creating Business Reports over Transactional systems (OLTP) data is NOT developer-friendly:
Ok, so we already covered in the previous section that creating business reports over OLTP can cause performance issues. But there’s more to it then just performance. You see – the requirements of creating business reports is different then the requirements of transactions systems. So? well, that means that the data model used for OLTP is best suited for transactional systems and it is not an optimal data model for analysis and reporting purpose. for example: creating hierarchies, drill-down reports, year-over-year growth among other things are much more efficiently handles by OLAP systems. But if we were to use OLTP database, then it would take a lot of developer hours to write efficient (and correct!) SQL commands (mostly stored procedures) to get OLTP to give data that the business reports need. Also, some of the common business metrics that are used in reporting can be stored in a cube. so that each time a report get’s created, you can re-use the business metric stored in the OLAP cubes.
OK – so OLAP cube saves time (to create reports).
Not only, OLAP cubes perform better at returning data they also help us speed the process of creating reports.
That’s great! let’s see one more reason:
#3: Ad-hoc reporting over OLTP systems creates confusion!
This reason is more about why we should have a data-mart and data-warehouse.. So why do ad-hoc reporting over OLTP systems creates confusion among business users?
Imagine creating reports over a LIVE system that’s getting updated every seconds. If there are ad-hoc (as-needed basis) reports being created by different users – then everyone would see different results. so it’s important to have a common version for everyone. Also imagine, everyone combining data from different data sources. If they’re doing it differently then they would see different data. And not only that, if they’re creating derived (or calculated) columns and their formula’s are different then they would see different data. you see a common pattern here? There’s no conformity in data & formulas in the reports that gets created. What does it cause? Confusion! So what’s needed is what they call in the data-warehouse “single version of truth”. OLAP cubes (which gets data from data-warehouse) provide that common single data source for everyone and thus the conformity in data is maintained while creating business reports.
Also while we are at this, one more consideration that typically reports require historical data at aggregated level. So we don’t want to store each transaction over the last 10 years in an OLTP database, do we? NO! right? In such cases, the historical data is aggregated based on requirements and stored in datamarts (/data-warehouse) which is later consumed by the OLAP cubes and that way OLTP databases do not have to store lot of historical data.
Ok – that’s one more reason OLTP are bad w/ business reporting and analysis and that’s why we need data-marts (data warehouse) and OLAP Cubes.
That’s about it. for this post. Let me point you to Related Resources:
This blog post is for people who have seen the reports built on PowerPivot model where the Month Names are not in correct logical order. So instead of “January, February, March, April …” (which is correct logical order), the order in the report would be displayed as “April, February, January, March..” (which is NOT correct).
This is what I am talking about:
Here, Month names are not sorted correctly, right? So how do we solve this? Let’s see this in this blog post!
Note that “Daily new number” is used for the demo purpose. I had to anonymize the data before I could share it with you all!
Originally, the table had just two columns but since I wanted to add “time Intelligence” to the model. I added two columns Month and Month Name.
Now here’s the DAX behind these two columns:
Column
DAX
Month
MONTH(‘Sample Table'[Date])
Month Name
RELATED(Month[MonthName])
There’s a relationship between Month Column of “Sample Table” and Month column of “Month” table
And here’s what Month Table looks like:
Month
MonthName
1
January
2
February
3
March
4
April
5
May
6
June
7
July
8
August
9
September
10
October
11
November
12
December
Creating a Report on Top of Sample Data-set.
Now if you create the report of Month Name vs Daily New Number, it will look like:
Oops, Problem detected: Month name are not in correct order.
So now let’s solve it.
Solution to sort month name correctly
Let’s solve this issue in our PowerPivot Model. Here’s what you do:
1. Go to PowerPivot model, Select “Month Name” column from the sample table
2 Toolbar > Home > Sort By column > click on “Sort by column”
3. And set the properties in the dialog box as:
Now, go back to the report and refresh the connection to the model.
Done!
This is so because by default since the month name is “Text” – it was sorted in A to Z format. But you saw how we can use the sort by column property in PowerPivot model to fix this issue.
That’s about it for the post! your feedback is welcome!
Now let’s add a calculated measure:
You can now see that the calculated measure Difference got added to the Pivot Table!
Note
