Many similarities exist in how dates are handled in relational databases and cubes. There may be several differences also, depending on how the cube is set up. This article discusses the differences in how the dates are shown in the view. There are two major differences: aggregation and continuous dates.
Date aggregations
Tableau provides many different aggregations for relational dates, allowing Year, Quarter, Month, Week, and several Day options.
When you create a cube, the cube must define the aggregation levels to offer. When Tableau connects to the cube, the menus offer only the date hierarchy as defined in the cube. If the cube built the date hierarchy to include only Year, Month, and Day, Quarter is not available as an option in Tableau. If you want these time points to be available, the cube must include the time points in the date hierarchy.
Note: Essbase has an additional option, where dimensions you create using the Time Intelligence capabilities of Essbase offer a continuous version of the date.
Create a continuous date view in a relational database
Step 1
Connect to your data source. This example uses the Sample - Superstore Sales data source.
Step 2
From the Dimensions pane, drag Order Date onto the Columns shelf.
Step 3
- For Tableau Desktop 7.0 and later, on the Columns shelf, right-click Order Date, and the select Exact Date.
- For Tableau Desktop 6.1 and earlier, on the Columns shelf, right-click Order Date and select All Values.
Step 4
From the Measures pane, drag Sales onto the Rows shelf.
The date view from a relational database is complete.
Create a continuous date view in a cube
Cubes are very powerful tools and they return information very quickly, often much more quickly than a relational data source. However, the reason for a cube's speed is that all its aggregations are pre-built. The aggregations and dimensional hierarchies are all specified in the cube's design. After the cube is built, these definitions remain static unless the cube is rebuilt with a tweaked design. Thus, while a cube has many advantages over relational data sources, it is not always as flexible if the types of questions you need to ask change after you build the cube.
Step 1
- For 7.0 and later, select Worksheet > New Worksheet.
- For 6.1 and earlier, select Edit > New Worksheet.
Step 2
Connect to cube data source. This example uses a data source that contains sales information.
Step 3
Right-click a date field in the Dimensions pane. For example, Order Date.
Step 4
Select Change Data Type > Date. The field changes color to green and is now continuous.
Note: If you click the plus sign next to the Date field instead, you may see that the hierarchy also contains an Order Date field. The date field property for this field cannot be changed.
Note: If you click the plus sign next to the Date field instead, you may see that the hierarchy also contains an Order Date field. The date field property for this field cannot be changed.
Step 5
Drag this field to the Columns shelf, and drag the Sales field to the Rows shelf.
The date view for the cube is complete. Compare this view to the view created from a relational database. In appearance, they are the same type of view, but the steps to create them were different.
The date view for the cube is complete. Compare this view to the view created from a relational database. In appearance, they are the same type of view, but the steps to create them were different.