Armanino Blog

Business Intelligence Best Practices: Utilizing Data Cubes

by Noah Kluge
April 07, 2015

Do you have existing data cubes but are struggling to take full advantage of them? Keep reading for some business intelligence best practices to take full advantage of your tools.

Data cubes return results quickly and can be a great way to get information out to users. This is because they are pre-processed, aggregated, and ready for queries, which make them much faster to use than traditional relational tables. SQL Server Analysis Services cubes can be used with readily available tools such as an Excel pivot table or Reporting Services (SSRS) to produce basic dashboards and reports. 3rd party products, like XLPublisher, help to address some common issues with the traditional approaches. (I'll cover XLPublisher in a future blog).

For most end users, SSRS are too technical for writing reports; and where deployed, users typically just "consume" existing reports written by IT. Let's take a look at some basic capabilities within Excel that end users can utilize on their own.

Since Excel 2010, Microsoft has provided powerful business intelligence (BI) capabilities with pivot tables/charts as well as slicers, sparklines, and OLAP Query formulas. These can become the initial entry into BI or even the foundation for a successful project. For this article, we will focus on using pivot tables and slicers to create a sales-oriented dashboard. (Excel 2013).

Business Intelligence Best Practices - Utilizing Data Cubes

  1. Create a Connection
    • Input the SQL Server Analysis Services server name and credentials click Next.
    • Choose your cube and then click Next or Finish.
    • Once you do this once, the office data connection (ODC) file will be available on the computer.
  2. Import data?
  3. Add a slicer.
    • From the Insert ribbon, choose Slicer. Business Intelligence Best Practices - Utilizing Data Cubes
    • Make sure to choose the same connection as the pivot table or chart. Business Intelligence Best Practices Pivot Table
    • Once the slicer is added you may notice that, when you click on it, it doesn't change the chart! The next thing to do is to set its report connection. Do this by right-clicking on the slicer and then going to 'Report Connections...' Business Intelligence Excel Pivot Chart Report Connections
    • From there, choose the appropriate charts and/or pivot tables. (Check out this screenshot "“ who named these anyway)?! Remember to maintain a consistent naming convention to ensure the name is useful and relevant in a production environment! Business Intelligence Best Practices - Utilizing Data Cubes
    • At this point, you should a basic report with a slicer and chart. If your slicer is not connected to your chart, you need to make sure that they are using the same data connection and that the report connection is set. Business Intelligence Best Practices - Utilizing Data Cubes Basic Report
  4. Add additional charts or pivot tables.
    • You can copy/paste a chart to create your next chart and it will inherit the slicer's report connection and all filters you have applied.
    • You can also insert new charts and pivot tables from the insert ribbon.

    Common Chart Changes:

    1. For the example dashboard below, an additional chart and a pivot table are needed.
      • Expand/Drill down within a hierarchy to see lower levels of detail. Menus are also accessible via right-clickBusiness Intelligence Best Practices - Utilizing Data Cubes.
      • Hide buttons that are on te chart. Menus are also accessible via right-click. Business Intelligence Best Practices - Utilizing Data Cubes
      • Apply a top 'X' filter. The right-mouse menus are contextual so you need to click on the axis (categories) in this example. Business Intelligence Best Practices - Utilizing Data Cubes
    2. After applying the chart changes from above and including a pivot table, the final result is below. Business Intelligence Best Practices - Utilizing Data Cubes

    A few final thoughts:

    1. The Top 'X' will only work with a slicer when the slicer and the chart are using different hierarchies/attributes. This means in the screenshot above that 'Item Class' slicer cannot affect "Item Sales" chart AND "Item Sales" returns only the top 5 (i.e. a top 5 filter within the chart itself). There are ways around this, but a change in cube design may be required.
    2. Any calculations (e.g. Variance LY%) will have to be defined within the cube itself as a calculated member.
    3. Slicers are very powerful when the data is coming from one cube. However, if you have a common dimension (e.g. AX Company, customer) across cubes (A/R, Sales, Inventory), a single slicer cannot be used.
    4. Pivot tables are great for ad hoc analysis, but they have severe limitations with formatting.

    In my next business intelligence best practices for data cubes post, I will review an Excel add-in that can be used with ANY cube to produce dashboards and boardroom quality reports!

    Discover more on best practices for business intelligence.

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Noah Kluge - Senior Manager, Audit - San Ramon CA | Armanino
Senior Manager
Related News & Insights
Overview of Sage Intacct R1 Release
Insights on the Biggest Changes in Sage Intacct’s 2024 R1 Release

February 23, 2024 | 10:00 AM - 11:00 AM PT
Tech M&A Deals: Achieving Buy-Side Success
Keys to using M&A in your organization’s growth strategy.

February 22, 2024 | 11:00 AM - 12:00 PM PT
Accelerating Ahead: A CFO’s Guide to the Future of Finance
Act now to embrace innovation, prioritize agility and future-proof your organization.

February 20, 2024