Since Excel Pivot Tables can connect to Analysis Services cubes, and are essentially free to most companies, they are often the default client tool. The Pivot Table functionality may suffice for simple business needs, however even in the newer versions of Office the Pivot Table lacks much of the sophistication and flexibility of a dedicated tool like BIXAnalytics.
Cube connected Pivot Tables have a number of restrictions and issues which BIX addresses, this results in significant time saving, increased performance, and better insight through improved query and analysis capabilities.
Some larger companies have written custom OLAP Pivot Table extensions to address specific issues or functionality gaps, but this can lead to ongoing support issues. BIX addresses Pivot Table users’ frustrations at multiple levels and offers concrete solutions.
Our customers confirm this and have explained the main reasons they use BiX alongside or instead of Pivot Tables:
Flexible and secure use of Excel formulas
Users are often frustrated by the limitations in terms of adding calculations to OLAP or data connected pivot tables. When connected to external data, the pivot table calculated field button is unavailable. BIX lets users add standard Excel formulas into cube connected grids (like a pivot table without the restrictions). Users can simply add a new column or row and type any Excel formula, including Vlookups. The formula is dynamic and will grow and shrink as needed with the data volume. As the formula is handled by BiX, it can’t be wrong or out of date.
Common business calculations
Although Excel formulas offer a great deal of flexibility and power, they are limited to Excel cell ranges. BiX MDX based formulas, on the other hand, can leverage any set of a cube’s data without the need to first retrieve it in an Excel worksheet.
The BiX calculation wizard offer multiple ready to use templates in order to easily define standard business calculations such as Periods to date, Moving averages, % of growth from previous Period, Contribution, Ranking,… These types of calculations are just too complex to safely be dealt with in Excel formulas in a professional environment.
Unlike Excel formulas which must be recreated every time they are used. BiX calculations can be, once defined, saved and then reused in different workbooks. Users have their own calculations library and can choose to share part of it with others.
Pivot Tables can generate inefficient MDX, leading to slow queries and frustrated users. In some cases, using a BIX “InfoSet” as a ‘like for like’ Pivot Table replacement results in significant performance gains. Whereas Pivot tables calculations are resolved in Excel, BiX calculations are resolved on the server, this means many significant PivotTable bottleneck issues simply don’t exist with BiX.
Maintenance free reports
Whereas Pivot Tables were designed to provide simple data browsing capabilities for different source types, BiX’s InfoSets were built to deal with sophisticated business reporting needs. When dealing with reporting, Pivot Tables users need to use a separate worksheet to handle calculations and formatting requirements. A great amount of repetitive work is then needed whenever a report needs to be refreshed or changed. This not only takes a lot of time but also often leads to reporting errors.
Strong and secure reporting features
BiX users can manage any complex reporting needs thanks to powerful built-in reporting tools and concepts. They don’t need to rely on fragile Excel formulas for handling common reporting needs such as complex calculations or non-symmetric queries. They can leverage all Excel formatting or labelling capacities easily and safely when dealing with dynamic queries.
Reports designed by BiX users will automatically adapt to changes in dimensions, especially time periods, in only a few clicks. Such reports will automatically query the relevant time periods and update the number of columns or rows based on the changes in the underlying database structure.
Unlike Pivot Tables, BiX “InfoSets” do not overwrite excel cells but simply “push” whatever is located on the right or at the bottom as needed.
Limitations with Slicers
Excel slicers have a number of limitations, particularly with regards to the restrictions of buttons, or the inability to display elements from more than one level.
BiX users can freely decide which slicer should be displayed or hidden and what its content and layout should be.