Best Practices for BI Office from Tim Andrews

Tim Andrews is one of our most active users in the community. We asked him to share some tips with you to help you make the most of BI Office.

As a daily user of BI Office for the past two years, I have really enjoyed working with the toolset for a number of reasons, not the least of which is its best-of-breed integration with Microsoft Analysis Services.  There are a few best practices, or tips and tricks, that I find myself using regularly when developing a new data product with BI Office.  I thought I would share some of them with other data analysts and developers out there who are using BI Office.  Keep in mind that best practices may be unique for each organization, and what works for me here may not work for you, unless otherwise tweaked.

  • Paring down my slicers for end-user experience
  • Setting my y-axes for column charts
  • Marking use of BI Office themes
  • Striking a balance between SSAS calculations and BI Office calculations
  • Defaulting to prior month, but allowing current month selections
  • Embedding a data dictionary

Paring Down My Slicers for End-User Experience

With the introduction of Analysis Services 2005, Microsoft introduced the ability for us to create multiple measure groups within a single cube.  This provides great native drill-across functionality, at the expense of more complexity for navigation during data discovery.  To that end, I often find myself with fact tables that connect to dimensions on fewer than half of the members within the dimension.  For example, the AdventureWorks2012 sample database contains over 600 products, but stores internet sales facts against just 150 of those products. 

The solution is to use the Advanced Set Designer within BI Office to restrict the slicer to display just internet products.  This is very quick and easy to do with the Non Empty filter functionality within the set designer.  The generated set may then be used as the basis for a parameter.

 Use the Advanced Set Designer to Get at the Data You Need

Setting My Y-Axes for Column Charts

Line charts can work well with a y-axis that starts higher than zero.  On the other hand, column charts can convey inaccurate information if they start at a higher data point.  In the example below, Germany has purchased $3,000 worth of goods compared with $13,000 for the United States.  However, the ratios depicted paint a different story where Germany is almost non-existent.  Setting the y-axis to start from zero solves this issue.

 Before Changing the Y-Axis: Ratios Are Incorrect


 After Changing the Y-Axis: Supports Proper Understanding of Germany and France Performance


Making Use of BI Office Themes

I highly recommend utilizing the theming function within BI Office to create one or more themes that work well with your company or department’s design language.  We chose to implement a color-blind friendly theme that we use across most of our storyboards.  This helps ensure a consistent, professional looking appearance for our end users.


Striking a Balance Between SSAS Calculations and BI Office Calculations

Each company falls at a different point on the spectrum of data governance vs. self-service BI.  At my company, we fall more towards enterprise, governed BI.  My natural instinct is to store calculations as far upstream as possible—in the data warehouse first, followed by the SSAS database as my second choice, and then finally the presentation layer (BI Office in this case).

However, if a calculation is very simple and leaves no room for misinterpretation (Month to Date Sales), or will never be used anywhere else, I tend to create calculations within BI Office.  This helps my SSAS database remain a little more clutter free and helps with performance against the cube in general.

Given that I work with a centralized BI team, we have chosen to create all BI Office calculations as public as opposed to private.  This helps with easier maintenance should a developer transfer to a different company.  With private calculations, we would have to move the definitions from a private library back into the new developer’s private library.  Public calculations eliminate this step.


Defaulting to Prior Month, But Allowing Current Month Selections

Some storyboards are more additive in nature, and some rely on averages, ratios, or per unit measures.  For additive reports, users often want to view the information in discrete chunks of time such as months.  Partially completed months may provide irrelevant information.  To that end, users will often want to default their analysis to the most recently completed month, with the option to check on the current month’s progress. 

To accomplish this, I have used the advanced set designer to filter on the latest 13 months that have had internet sales.  I then created a parameter and defaulted it to the second to last position within the list, based on this new set.  With this approach, the default month will always be the most recently fully-completed month, based on Internet Sales.

 Filtering on the Latest 13 Months of Internet Sales



Creating a Parameter to Get the Most Recent Fully-Completed Month


Thank you, Tim for sharing your ideas! If you have ideas to share, post them in a discussion thread or feel free to reach out to Karen Owens !

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Thanks Tim. Some nifty ideas here!

    Here are a few of the things we have done:

    • We continually need users to pick a start and end date in most of their analyses. To fast track this setup, we build a public custom set that uses 2 parameters (start and end date).  That way, whenever someone needs to do range logic, they use the set and they have an instant solution without all the headache of re-setting up the range calcs. This totally beats all the old work-arounds we used to bake into our cubes to get dynamic cross-selection working.
    • Up until BI Office V6, we created numerous perspectives in SSAS so different users saw the base cubes/models differently depending on their analytic requirements. This of course created a management nightmare for us BI devs. Now in v6, we have swapped to using the new Metadata capability, to change the selection of dimensions and measures exposed to different role groups for a given cube/model. Apart from eliminating the admin headache, its 100x faster to setup and manage, and we don't have to continuously change the data source when reports shift around the user groups.
    • Our users often need drill through capabilities. Of course DrillThrough in SSAS is limited to the grain of the cube itself. However, using the Rowset actions with an SSAS assembly that constructs a dynamic SQL statement and runs its against the targeted relational database, we can give our users transactional data. The fact that users can then get that result set in the same app, without having to pop-out to a separate web page or report (through SSRS for example), really simplifies the experience.
    • The new shared Excel file uploader for data modeling is super useful and totally eliminates the headache of hydrating tabular models sourced from static Excel files.
    • We have eliminated a huge amount of redundant content with the V6 versioning feature. By getting our content publishers to use the in-built versioning, they have stopped creating 50 copies of each report and dashboard with countless backups and exports and this change over time.



    Like 1
  • SP Great ideas, I would be interested in seeing your .net assembly for drill-through!

  • I noticed the "Embedding a data dictionary" item but didn't see any detail.  Could you share some info about that?  We're currently investigating a couple alternatives and would love to see what you've done.  Thanks!

  • Eric Kieft  Hey Eric, it looks like a part was cut off.  Here is the original write up that I meant to include.  It basically involves the following:

    1. Use the built-in "description" properties in SSAS for measure groups, measures and calculations
    2. Write metadata SQL queries to pull out those descriptions
    3. Embed an SSRS to display those queries into a final tab on your dashboard
Like4 Follow
  • 3 yrs agoLast active
  • 4Replies
  • 1313Views
  • 4 Following