Exploring Excel 2013 for BI Tip #8: Adding Calculated Measures

, 2013-06-20

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Adding Calculated Measures to the Excel 2013 Workbook

If you have worked with SQL Server Analysis Services in the past you already know what calculated measures are.  More importantly, you know how to update the MDXScript without requiring a cube refresh.  (If you are unaware of this, check out the BIDS Helper project on CodePlex.)

A calculated measure uses existing measures and MDX to provide additional, shared calculations in a cube.  However, there are many times that the ability to create a calculated measure in Excel would be great.  In Excel 2013, this is now possible.

Once you have connected to a cube using a pivot table, you can add calculated measures using the OLAP Tools menu on the ANALYZE tab.

image

When you select the MDX Calculated Measure item, it will open an MDX dialog designer in which you can create a measure.  (MDX Calculated Members are will be in the next tip.)

image

Before we create our measure, let’s talk about the ancillary parts such as the name, folder and measure group.  You will want to give your measure a name.  It needs to be unique within the work you are doing and unique from other measures in the cube or you will get an error.

image

The folder and measure group are really optional.  It really depends on how you want display the new measures in the Excel Fields window.  I would recommend that folders are used when large volumes of measures are being used.  It is a great way to organize the measures into consumable, related groups for your users.

image

When you designate the measure group, the measure and folder will be put in the same group as the measure group.  This is appropriate when the measure is related exclusively to the measure group, conceptually if not technically. I usually will only do this if all of the measures come from the same measure group (technically related) or if the user understands that the measure “should” be a part of the measure group even if it is dependent on measures outside of the current measure group (conceptually).

Next, you create the measure.  The Fields and Items tab contains the measures and dimensions available while the Functions tab has the MDX functions you can use.  Use the Test MDX button to verify syntax prior to saving the measure.

The really nice part is that this measure is now contained within the workbook.  It does not get published back to the server.  However, if the measure becomes popular, you can use the MDX from this measure to create a new measure on the server.  It will be business verified before being published.  By using Excel to create calculated measures, you also prevent a glut of single use measures from being created on the server.

Finally, to manage created measures, use the Manage Calculations option on the OLAP Tools menu.  It will open a dialog with all of the calculated measures and calculated members created with this data connection in the workbook.  In my scenario, I used the MyVote Cube connection to create the measure.  Basically, the pivot table is associated with a connection and that is the defacto filter for this list.

image

Use Excel to test MDX simply.  This will allow you to create measures, verify data, then deploy working code.  It is a great addition to the product.

Next up… Calculated Members.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads