BI Office vs Pivot Tables
Yes, Excel itself has much functionality - but the querying options in Pivot Tables against SSAS seem limited.
Can someone highlight the most important differences.
So I would say that the key differences can be divided into two categories - content capabilities and query specifics.
Enterprise and Content Capabilities
This one is the easiest to answer. Pivot Tables can provide simple table and chart options for your query results but not much else. BI Office supports a variety of visualizations beyond just tables and charts including maps, KPI gauges, and advanced visualizations such as tree maps, sunbursts, and word clouds. You also have features such as Story Boards, Publications, and Alerts that can't be replicated in Pivot Tables.
As a desktop product, content created in Excel can only be shared by passing around XLSX files or requires using SharePoint. In BI Office, you have the ability to easily share content with other users and create shareable custom elements that allow multiple users to reuse the logic in their own reports.
There are a number of enterprise features in BI Office that IT or your BI team will love as well that include version control, content lineage, security and usage statistics. You don’t get these things with Excel.
MDX is a challenging language to learn. As such, BI Office contains a number of wizards to simplify the process of creating custom sets and members, whether it is simple calculated measures such as ratios or growth percentages or more complex things like time intelligence or binning groups. Excel does not provide any of these things. You must know MDX to create sets and member in Excel.
One of the coolest features you get with BI Office is both Parameters and Variables. This allows you to parameterize any portion of an MDX expression, whether it is a query, custom set, or calculated member. You can solve a lot of MDX challenges this way.
The biggest difference though is in how each tool writes their MDX queries. The MDX that Excel generates can be problematic. When a user creates a query that contains multi-select filters, Excel uses a sub-query. In some cases, this can produce incorrect results. BI Office uses the WHERE clause by default to avoid the problems but also allows a user to switch to using the sub-query when they need it. Please read the blog posts in Sherry’s BI Corner (LINK) to get more specific examples (see MDX #25 and #28).Reply
I think there are other differences as well.
Getting around your cube or model is MUCH easier in BI office. I hate the complexity of choosing different items in the hierarchies in Excel. For example, its really difficult selecting the months of 2 specific years in Excel - requiring 24 painful clicks. In BI Office, its 2 simple clicks - and its EASY. We have deep hierarchies, and users needed to make a unique click for each element they wanted. This sometimes meant 100 clicks.
The context menu is also super useful - especially dicing. Our users LOVE the dice menu. We saw that Excel has something similar now - but its really hard to find and use.Reply
SP - quick question for you. What types of users do you have that are utilizing the dicing? We have some cubes with many measure groups, dimensions and attributes that I am comfortable with power analysts navigating the dicing menu. For business users, I feel like the dicing menu can become overwhelming outside of our more concise cubes. Are you using perspectives or anything to make the dicing a little easier?Reply
We have all types of users: "low" to "high". We actually find that the consumer type users really like the dice menu - its simpler than using the element trees. While the advanced users like the trees for doing more complex picking.
Its true with super large models that the dice menu is a little overwhelming. So yes, we sometimes use perspectives to slim things down too.
But, compared to using the element trees in Pivot Tables, the dice menu is super accessible for our users.Reply