Resolving Timeline Filter error when using Multidimensional CubesFixing the Timeline error involves several steps. The following is what you will have to do to fix the error when PivotTable is sourcing data from Multidimensional Cubes:
- Open your Multidimensional solution in SQL Server Data Tools (SSDT)
Identify your date dimension, in our example, we are using Adventure Works’ DimDate shown in Figure 4
Figure 4: Multidimensional data source view
Open the date dimension and ensure that at the very least it contains two attributes, namely, an integer key attribute and a date attribute. Figure 5 shows the attributes of my date dimension, namely, Date Key as well as Full Date Alternate Key.
Figure 5: Structure of my sample date dimension
The next step involves converting your date dimension to Time. Setting dimension to time provides the capability of time based-levels of granularity i.e. Year, Quarter, Month etc.
Figure 6: Setting dimension to Time
The final step involves configuring NameColumn and ValueColumn properties of your key attribute which are usually set to none by default. Because we don’t have many attributes in our date dimension, both NameColumn and ValueColumn properties are set to Full Date Alternate Key. Whatever fields you may have in your dimension, the trick is to always ensure that the ValueColumn property is set to an attribute that is defined as date.
Figure 7: Configuring date key attributes
Resolving Timeline Filter error when using Tabular ModelsThe error displayed in Figure 3 could also be as a result of a PivotTable that is based off a Tabular Model. Resolving the issue when the connection is Tabular Model is very different from what we did for a Multidimensional source. In fact, the fix for a Tabular Model date source is much simpler. All that you have to do is:
- Open your Tabular model in SSDT
Identify and highlight the dimension that you want to set as date, in our example it’s DimDate shown in Figure 9
- Navigate to the Table menu properties and click Date option
Within the Date sub-menu, click Mark as Date Table option – shown in Figure 10. The Mark as Date Table option will be disabled if you are trying to configure it for a table that doesn’t have date fields.
Figure 10: Configuring table properties
A Mark as Date Table dialog box will come up and you should choose the date columns that you want used as unique identifier for the date dimension. In our example, we are using FullDateAlternateKey column
Figure 11: Mark as Date Table dialog box
- Reprocess the Tabular model and refresh the excel document, thereafter
PowerPivot Date DimensionResolving the Timeline filter error when your data is sourced from an Excel PowerPivot workbook is similar to what we did in a Tabular Model solution. All you have to do is:
- identify the dimension that you want to set as date
- Navigate to the Design menu tab
- Within Design menu tab, navigate to Mark as Date Table option, as shown in Figure 13
- Click the drop down button and choose Mark as Date Table
- Save your Excel PowerPivot workbook
ConclusionAlthough Excel can be an end-users popular data visualisation tool, depending on your OLAP solution design, Excel has the potential of being a BI developer’s worst nightmare. In this article we went through different workarounds to resolving an Excel Timeline filter error when a given PivotTable references data from OLAP solutions such as Multidimensional cubes, Tabular Models as well as Excel PowerPivot.
- Multidimensional sample solution
- Tabular Model sample solution
- SQL Server Adventure Works 2014 (Data Warehouse) Database
Latest posts by Sifiso Ndlovu (see all)