| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Tabular
Contents
[hide]Introduction
SQL Server 2012 Tabular models can be deployed either in-memory to the xVelocity engine, or using Direct Query mode. Any MDX client tool can access Tabular models deployed to xVelocity. If the model is deployed using Direct Query mode, they can only be queried through DAX, and not MDX. XLCubed version 7 can use both MDX and DAX, and so can access any of the BI deployment modes available in SQL 2012. The diagram below outlines this.
The new Table object in XLCubed can connect to Analysis Services cubes (MDX), Tabular Models (DAX), or relational SQL (SQL). The DAX tables provide access to all Tabular models in SQL 2012.
Grids
Grids can be used against In-Memory cubes, and all the standard XLCubed functionality can be accessed.
See the normal Analysis Services pages for information on how to use these features, they function consistently with Multidimensional cubes.
Tables
Tables provide a simple way of querying a tabular model. All of the standard functionality provided by XLCubed tables can be used.
Insert a table by going to the XLCubed ribbon and selecting Insert Data Table -> Analysis Services Tabular
This will allow you to select a connection to a Tabular cube, and bring up the report designer.
Slicers
Slicers give users simple to use pick lists which can be used to filter reports.
By adding a slicer based on Tabular Analysis Services you create a slicer that can be linked directly to a Table.
As with standard cube slicers, you can restrict the members shown to those with data by putting a measure into the Filters section.
The following slicer gives all the years for which there is Sales data.
Hierarchies
As of Version 7.6 XLCubed supports tabular hierarchies, provided the server is on SSAS2012 SP1 or above.
Hierarchies can be used in two ways:
- Placed on the Filter area - you get a treeview to select the report filter.
- This can also be linked to a treeview slicer if required.
- Placed on the Selected Columns area - you can drill into the data by double clicking a value, and drill back up by double clicking the column header.
Calculations
You can create new DAX calculations for your report without updating the cube.
These calculations are stored with the workbook, and are available from any grid, table or formula in the book.
Access the wizard through the Custom Calculations ribbon item. Click the DAX button (highlighted) to create a new calculation, and then use the controls on the right to select
- Table the measure will appear in
- Name for the measure
- Formula to define the measure
You can drag tables, columns and measures from the tree on the left into the formula area to insert their names.
Formula
Please see the Tabular section of the formula reference.
