Analysis Services and Excel Tips and Tricks

I was speaking with a Business Analyst the other day and I couldn’t remember exactly how to do a cool trick I’d learned years ago working with Excel and Analysis Services.  It got me thinking about some other cool techniques that I suspect many do not know about.  If you’ve got a cool trick to share, please let us know in the comments.

Trick #1 – Use OLAP PivotTable Extensions to Add Calculated Measures

I want to demonstrate how to add a new calculated measure to a pivot table using MDX.

The Setup

  1. Download and install the add-in. http://olappivottableextend.codeplex.com/
  2. Open Excel and connect to the AdventureWorks SSAS database.  (Data, From Other Sources, Analysis Services, etc.)
  3. In the PivotTable Field list use the “Show fields related to:” drop down at the top select “Internet Sales.”
  4. Add the Internet Sales Amount to the values
  5. Add the Product Category user hierarchy to the rows and expand.
  6. Add the Date.Fiscal Weeks User hierarchy to the Report Filter and select Week 4 FY 2008.
  7. Right click a cell within the pivot table and select OLAP PivotTable Extensions.
  8. Enter the following in the Calculation Name drop-down field:  [SPPY Internet Sales Amt]  (SPPY = Same Period Prior Year)
  9. Enter the following in the formula text box: (ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].currentmember),[Measures].[Internet Sales Amount])
  10. Click the “Add to PivotTable” button.
  11. Place a check mark in the PivotTable Field list under the newly created “Values” measure group next to SPPY Internet Sales Amt.
  12. Verify that those numbers match Week 4 FY 2007.
  13. Remove the Date.Fiscal Weeks user hierarchy from the report filter.
  14. Add the Date.Fiscal user hierarchy to the Report Filter and select July 2007.
  15. Verify that those numbers match July 2006.

Hungry for more?

There are lots of examples of useful MDX you can use in your spreadsheets!  A great graphical tool to help you build (and learn) complex MDX queries is Cube Player.

Trick #2 – Filtering, the Easy Way!

Have you ever needed to analyze a particular set of products or customers based on a long list of customer names or USNs?  Going through the filter drop-down and individually clicking each one can be a tedious, time-consuming, and frustrating process.  If you’ve ever had to do this, I am about to become your best friend.

The Setup

  1. Open Excel and connect to the AdventureWorks SSAS database.  (Data, From Other Sources, Analysis Services, etc.)
  2. In the PivotTable Field list use the “Show fields related to:” drop down at the top select “Internet Sales.”
  3. Add the Internet Sales Amount to the values
  4. Add Customer to the row labels
  5. Highlight all the customer names that start with the first name “Michael” and copy them to your clipboard.
  6. Right click a cell in the PivotTable and select OLAP PivotTable Extensions.
  7. Click the Filter list tab and select [Customer].[Customer] from the filter hierarchy drop-down.
  8. Paste in your Michaels.
  9. Click Filter PivotTable.
  10. Add a comment to this blog post about how much you appreciate the time I spent writing this blog post.  Smile

Trick #3 – Using SSAS Time Intelligence in Excel

For this example, I added time intelligence to the AdventureWorks cube by following the wizard.  If you don’t know if the cube at your company has time intelligence, look in the date dimension for an attribute named something like “Fiscal Date Calculations” or just ask somebody.  If your cube doesn’t have time intelligence, maybe you can convince them to add it.

The Setup

  1. Open Excel and connect to the AdventureWorks SSAS database.  (Data, From Other Sources, Analysis Services, etc.)
  2. In the PivotTable Field list use the “Show fields related to:” drop down at the top select “Internet Sales.”
  3. Add the Internet Order Count measure from the Internet Orders measure group to the values.
  4. Add the Date.Fiscal user hierarchy to the report filter.
  5. Add Fiscal Date Calculations to the rows.
  6. Select November 2003 in  the Date.Fiscal report filter in cell B1.

image

Wow!

Trick #4 – Formatting, As You Like It!

For this example, let’s create a nicely formatted re-usable Balance Sheet for Adventure Works.

The Setup

  1. Open Excel and connect to the AdventureWorks SSAS database.  (Data, From Other Sources, Analysis Services, etc.)
  2. In the PivotTable Field list use the “Show fields related to:” drop down at the top select “Financial Reporting.”
  3. Check the box next to the Amount metric in the Financial Reporting measure group.
  4. Check the box next to Accounts in the Account dimension.
  5. Click the plus sign next to the Fiscal display folder in the Date dimension and check the box next to the “Date.Fiscal” user hierarchy.
  6. Click the drop down arrow in cell A2 and uncheck everything but Balance Sheet.
  7. Click the drop down arrow in cell B1 and uncheck everything except FY 2006, FY 2007, and FY 2008.
  8. Click the plus sign next to “Balance Sheet” in cell A3.  Click the plus sign on every plus you see until there are no longer any plus signs on the sheet.
  9. (Optional sub-tip)  The CFO wants to see the GL account next to the lowest leaf level rollups.  Right click “Cash” and select “Show Properties in Report” and choose “Account Number.”

image

Your screen should look like this.

What’s Wrong With This Picture?

This looks okay; but, it could certainly be better.  If you look for balance sheet examples on the internet you might notice that the totals are normally put underneath the category.  They also typically wouldn’t have a total column for the combined years aggregation.

First let’s do something about those totals.

  1. Click on a cell in the pivot table.  This action adds the PivotTable Tools section on your Excel ribbon.
  2. Click on the Design tab and click the Report Layout button third from the left on the ribbon.  Select “Show in Tabular Form.”
  3. Click the Grand Totals button and select “Off for Rows and Columns.”

It is starting to look better; but, now if I want to insert a row underneath the receivables total I get a very big error message!

image

This is the trick.

  1. Click the Options tab of the PivotTable Tools ribbon and click the OLAP Tools button fourth from the right.  Select “Convert to Formulas.”
  2. You’ll get an error.

image

To resolve the error:

  1. Click the filter icon in cell A2 and select all.
  2. Click the filter icon in cell G1 and select all
  3. Save a copy of your Balance Sheet with a file name such as “Dynamic Balance Sheet.XLSX” (I’ll explain why shortly.)
  4. Click the OLAP Tools button and select “Convert to OLAP formulas” again.

Continuing our beautification…

  1. Insert a row underneath “Receivables Total.”
  2. Insert rows underneath every total.
  3. Insert a column between FY 2007 and FY 2008.
  4. Label the column “YOY Var %” in cell I2
  5. Add the formula: =(H6-G6)/G6 and copy it down the balance sheet
  6. Format the column I as a percent
  7. Hide the rows 49 – 51
  8. Make the totals bold
  9. Hide column A
  10. Hide Row 1, Bold and Center Row 2.
  11. Highlight cells B2-F2 and press delete
  12. Highlight columns B-F and select Format,  Auto Fit Column Width and make bold.
  13. Copy column I to (the new) Column M
  14. Modify the formula in M3 so that it reads: =(J3-H3)/H3
  15. Hide columns I and M.  I just wanted to show you how easy it was to add a formula.
  16. Add top and bottom borders for each of the sub category total amounts, top and thick bottom borders to Current Assets, Property, Plant, Equipment, Liabilities, and Owners equity totals, and finally top and double bottom borders for Assets and Liabilities and Owners Equity Totals.
  17. Select columns G-J, right click a cell, select Format cells and decrease the precision so that there are no cents displayed.
  18. From the view tab uncheck the Gridlines checkbox.
  19. Print.
  20. Get some coffee on your way to the printer.

You’re spreadsheet should look like this:

image

There is one caveat you need to know.  If accounting adds a new GL rollup it will not automatically appear on the balance sheet.  You can manually add it if you’re careful or open the “Balance Sheet Dynamic” version and re-apply the formatting.

——————————————————————————-

That’s all for today, folks!  Please add to the comments if you would like to share a great Analysis Services in Excel tip or trick.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s