sql olap_如何解决由SQL Server OLAP数据源引起的Excel时间轴筛选器错误

sql olap

Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011. 图1说明了这些好处之一，其中时间线过滤器允许最终用户再次将数据透视表轻松地应用过滤器（即使用滚动条）。 它还具有有用的注释，这些注释描述了您过滤的内容–在这种情况下，我们选择了2011年2月至2011年3月之间的销售时段。 Figure 2 wherein it only indicates that multiple items have been chosen and the only way to understand on what those filter items are, is by clicking on the dropdown button. 图2中仅指示已选择了多个项目，并且了解这些过滤器项目是什么的唯一方法是单击下拉按钮。 We can’t create a Timeline for this report because it doesn’t have a field formatted as Date”, similar to what is shown in 我们无法为此报告创建时间轴，因为它没有格式为Date的字段” ，类似于Figure 3. 图3所示。

解决使用多维多维数据集时的时间轴过滤器错误 (Resolving Timeline Filter error when using Multidimensional Cubes)

解决时间轴错误涉及几个步骤。 下面是纠正数据透视表从多维数据集中获取数据时必须采取的措施：

1. Open your Multidimensional solution in SQL Server Data Tools (SSDT)
   在SQL Server数据工具（SSDT）中打开您的多维解决方案
2. Identify your date dimension, in our example, we are using Adventure Works’ DimDate shown in Figure 4
   
   Figure 4: Multidimensional data source view
   确定您的日期维度，在我们的示例中，我们使用的是Adventure Works的DimDate ， 如图4所示。 图4：多维数据源视图
3. 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
   打开日期维度，并确保至少包含两个属性，即整数键属性和日期属性。 图5显示了我的日期维度的属性，即Date Key以及Full Date Alternate Key 。 图5：示例日期维度的结构
4. 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
   下一步涉及将日期维度转换为时间。 将时间设置为维度可提供基于时间的粒度级别，例如年，季度，月等。 图6：将维度设置为时间
5. 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
   最后一步涉及配置键属性的NameColumn和ValueColumn属性，这些属性通常默认情况下设置为none 。 由于日期维度中没有太多属性，因此NameColumn和ValueColumn属性都设置为Full Date Alternate Key 。 无论您在维度中具有任何字段，诀窍都是始终确保ValueColumn属性设置为定义为日期的属性。 图7：配置日期键属性

Insert Timelines dialog box similarly to that in 图8类似的“ Figure 8, indicating that Excel is finally able to detect your Multidimensional date dimensions. 插入时间线”对话框，表明Excel终于能够检测到多维日期尺寸。

解决使用表格模型时的时间轴过滤器错误 (Resolving Timeline Filter error when using Tabular Models)

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: 图3中显示的错误也可能是由于基于表格模型的数据透视表所致。 当连接是表格模型时，解决该问题与我们对多维源所做的解决有很大不同。 实际上，针对表格模型日期源的修复要简单得多。 您要做的就是：

1. Open your Tabular model in SSDT
   在SSDT中打开表格模型
2. Identify and highlight the dimension that you want to set as date, in our example it’s DimDate shown in Figure 9
   
   Figure 9
   标识并突出显示要设置为日期的维度，在我们的示例中为图9所示的DimDate 图9
3. Navigate to the Table menu properties and click Date option
   导航到“ 表格”菜单属性，然后单击“ 日期”选项
4. 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
   在Date子菜单中，单击Mark as Date Table选项-如图10所示。 如果您尝试为没有日期字段的表配置“ 标记为日期表”选项，则该选项将被禁用。 图10：配置表属性
5. 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
   将出现“ 标记为日期表”对话框，您应该选择要用作日期维的唯一标识符的日期列。 在我们的示例中，我们使用FullDateAlternateKey列 图11：“标记为日期表”对话框
6. Reprocess the Tabular model and refresh the excel document, thereafter
   重新处理表格模型并刷新excel文档，然后

Insert Timelines dialog box should come up as shown in 插入时间轴”对话框， Figure 12 如图12所示。

PowerPivot日期尺寸 (PowerPivot Date Dimension)

当您的数据来自Excel PowerPivot工作簿时，解决时间轴筛选器错误的方法类似于我们在表格模型解决方案中所做的事情。 您要做的就是：

1. identify the dimension that you want to set as date
   标识要设置为日期的维度
2. Navigate to the Design menu tab
   导航到“ 设计”菜单选项卡
3. Within Design menu tab, navigate to Mark as Date Table option, as shown in Figure 13
   在Design菜单选项卡中，导航到Mark as Date Table选项， 如图13所示。
4. Click the drop down button and choose Mark as Date Table
   单击下拉按钮，然后选择“ 标记为日期表”
5. Save your Excel PowerPivot workbook
   保存您的Excel PowerPivot工作簿

Mark as Date Table option will be disabled if you are attempting to make that change on a dimension that doesn’t contain fields with date data types. 标记为日期表”选项将被禁用。

结论 (Conclusion)

尽管Excel可能是最终用户流行的数据可视化工具，但根据您的OLAP解决方案设计，Excel可能会成为BI开发人员最可怕的噩梦。 在本文中，当给定的数据透视表引用来自OLAP解决方案的数据（例如多维数据集，表格模型以及Excel PowerPivot）时，我们经历了各种变通办法来解决Excel时间轴筛选器错误。

资料下载 (Downloads)

• Multidimensional sample solution
  多维样本解决方案
• Tabular Model sample solution
  表格模型样本解决方案
• SQL Server Adventure Works 2014 (Data Warehouse) Database
  SQL Server Adventure Works 2014（数据仓库）数据库

参考资料 (References)

• Create a Date type Dimension
  创建日期类型维度
• Create a PivotTable timeline to filter dates
  创建数据透视表时间轴以过滤日期
• Configure Attribute Types
  配置属性类型

翻译自: https://www.sqlshack.com/resolve-excel-timeline-filter-errors-caused-sql-server-olap-data-sources/

sql olap