Working with DirectQuery in SQL Server Analysis Services and Pyramid Analytics BI Office (Part 2): Living Without User-Defined Hierarchies
by Matt Burr , Senior Solutions Engineer at Pyramid Analytics
In a previous blog post, we saw that while BI Office works with SQL Server Analysis Services (SSAS) tabular models in DirectQuery mode, SSAS restricts user-defined hierarchies in tabular models in DirectQuery mode. This post will look at how to work with a model that has no user-defined hierarchies in BI Office.
Drilling Without Hierarchies
Without user-defined hierarchies, you can’t drill up and down through your data. Thankfully, BI Office gives you other ways to get a similar result.
Say you’ve got a grid of Product Categories like this:
Figure 1: A Grid of Product Categories
If you want to drill into one of those categories, it’s pretty easy if you’ve got a user-defined hierarchy: just click on it. In DirectQuery mode, you need to Dice the data instead; right-click on the category you want to drill into, select Dice from the context menu, and select the attribute you want to drill into next. For example, to “drill” from a Product Category into its subcategories, right-click and select “Product Sub Category” from the Dice option:
Figure 2: Use Dice in place of Drill Down to "drill" into Product Sub Categories
Drilling up is a little less convenient, though, since you didn’t really “drill down,” but you have a couple of options. The easy approach here is to use the “backwards” button at the very top of the screen (above the ribbon):
Figure 3: Click Backwards to return to the previous view
Alternatively, you can simply switch the sets on the columns or rows back to the previous set; for example, we could remove Product Sub Category from our grid and put Product Category back on in its place.
Expanding Without Hierarchies
Expansion is a little more difficult to reproduce without user-defined hierarchies. You can get close to expansion through crossjoins. Drag two or more sets side-by-side onto your rows or columns to crossjoin them. If you merely want to see
Figure 4: Crossjoined sets on rows group children beside their parent
children grouped by their parents, then you can stop here. If you want to see the parents’ totals, then you need to add the “All” member to the child set (for example, you would add “All” to the Product Sub Category set above).
To add the “All” member to a set, double-click on the set in the selection panel (e.g. you would double-click on Product Product Sub Category in the “Rows” selection panel). Next, in the Elements selection panel, click on the “All” member while holding down the Control key on your keyboard.
Figure 5: Add "All" to the set of members in the Element panel
While DirectQuery mode offers you real-time access to the data behind your tabular models, we’ve seen that it places a number of restrictions on the MDX language. Most of these restrictions have little or no impact on your experience in BI Office, but the loss of user-defined hierarchies can affect the way you navigate your tabular model in BI Office. Thankfully, BI Office provides a rich variety of analytical abilities that allow you to closely reproduce the analytical experience of hierarchies even when they aren’t present and get to the critical answers you need.