All IT Courses 50% Off
Tableau Tutorials

Cube Data Sources in Tableau

A cube data source is defined as a data source in which the cube’s designer has created hierarchies and aggregations in advance.

Cubes are very powerful and can quickly return information, often much more rapidly than a relational data source. However, the reason for the cube’s speed is that all its aggregations and hierarchies are pre-built. These definitions also remain static until the cube is rebuilt. Therefore, cube data sources are not as flexible as relational data sources if the original designer did not anticipate the types of questions you need to ask or change after the cube was built.

The cube data sources that are supported in Tableau:

  • Oracle Essbase
  • Teradata OLAP
  • Microsoft Analysis Services (MSAS)
  • SAP NetWeaver Business Warehouse
  • Microsoft PowerPivot

How to Create a Calculated Member?

Using a multidimensional data source helps in creating calculated members using MDX formulas instead of using Tableau formulas. A calculated member can either be a calculated measure, a new field in the data source similar to a calculated field, or a calculated dimension member, a new member within an existing hierarchy. For instance, if a dimension Product has three members (Soda, Coffee, and Crackers), you can define a new calculated member Beverages that will sum the Soda and Coffee members. When you place the Products dimension on the Rows shelf, it displays four rows: Soda, Coffee, Crackers, and Beverages.

All IT Courses 50% Off

You can also define a calculated dimension member by selecting Calculated Members from the Data pane menu. In the dialog box Calculated Members that opens, you can create, delete, and edit calculated members.

For creating a new calculated member, do the following:

Step 1: Click New to add a new row to the list of calculated members at the top of the dialog box.

Step 2: Type a Name for the new calculated member in the dialog box’s Member Definition area.

Step 3: Specify the Parent member for the new calculated member. All Members are selected by default. However, you can also choose Selected Member to browse the hierarchy and select a specific parent member.

Step 4: Give a solve order to the new member.

Sometimes a single cell in the data source can be defined by using two different formulas. The solve order determines the precedence given to each formula. Formulas having a lower solve order are solved first. The default solves order is zero.

Step 5: If you are connected to a Microsoft Analysis Services data source, the calculation editor contains a Run before the SSAS check box. Choose this option for executing the Tableau calculation before any Microsoft Analysis Services calculations. 

Step 6: Type or paste an MDX expression into the large white text box.

Step 7: Click Check Formula to verify that the formula is valid.

Step 8: When finished, click, OK.

The new member displays in the Data pane either in the Measures area, if you chose [Measures] as the parent member, or in the Dimensions area under the specified parent member. You can use the new member just like any other field in the view.

Create Slicing Filters

Slicing filters also termed as slicers or calculation filters are dimension filters that behave differently for multidimensional data sources than relational data sources.

A slicing filter exists only when:

  • The dimension that you have placed on the Filters shelf is not also on any other shelf in the view.
  • The filter is defined to include multiple values. For instance:

Tableau recalculates values for each measure in the view using the configured aggregations.

However, because multidimensional data sources contain aggregated data in the data source, no aggregation can be defined for a slice that the dimension filter cuts through the values of individual measures. Tableau, therefore, automatically performs a summation. Moreover, because multiple values are defined, the result is the sum of a group of sums.

For example, consider the view shown below that shows profit by region and state.

A slicing filter, including the Q3 and Q4 members of the Quarter dimension, is then applied to the data.

In the resulting view shown below, the profit for Oregon in the West region is 2,567. This number is calculated by summing the data values for Qtr3 and Qtr4.

The summary symbol (Σ) you see on the Filter shelf in the view above indicates a slicing filter. If the original aggregation is non-additive (Count Distinct), the values you see after applying a slicing filter may be unexpected.

Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles

Back to top button