在FACT表中使用ANYDATA / SQL_VARIANT [英] Using ANYDATA/SQL_VARIANT in FACT table

问题描述

我正在设计事实表的模式，但我不太确定使用ANYDATA / SQL_VARIANT来存储度量。我使用这种类型的原因是重用该列用于基于同一个CUBE的多个报告的度量。

I am designing the schema for fact table, but I am little bit unsure about using the ANYDATA/SQL_VARIANT to store measures. My reason for using this type is to reuse the column for measures of multiple reports based on same CUBE.

使用ANYDATA / SQL_VARIANT的陷阱/性能，可扩展性，可维护性等。

What are the pitfalls/brawbacks of using ANYDATA/SQL_VARIANT in terms of performance, scalability, maintainability etc.?

推荐答案

你可能想看看 .com / questions / 8229028 / thoughts-on-dimension-measures-for-bi>类似问题。我会避免这些数据类型，因为：

You might want to look at this similar question. I would avoid these data types because:

• 他们可能使您的ETL过程更慢和更复杂，因为需要隐式或显式CONVERT


• SQL_VARIANT（即Microsoft SQL Server）无法存储所有数据类型（无可否认，它不能存储的数据类型不太可能是措施


• 工具和应用程式可能无法有效处理，甚至无法处理。




• 从你的描述中不清楚什么优势你会从数据类型中获益，但你似乎在寻找一个通用的度量数据类型，并且这可能不是一个好的方法，因为上面链接的问题中概述的原因

• They may make your ETL process slower and more complicated because of the need to implicitly or explicitly CONVERT to other data types for comparisons, passing to functions etc.
• SQL_VARIANT (i.e. Microsoft SQL Server) cannot store all data types (admittedly, the ones it can't store are unlikely to be measures anyway, but the point remains)
• Tools and applications may not be able to process them efficiently or even at all
• They are 'unusual' (at least in my experience), and you should only implement unusual things when they have a very significant advantage, otherwise you add maintenance complexity for no benefit
• It isn't clear from your description what advantage you would gain from the data types but you seem to be looking for a generic measure data type and that probably isn't a good approach for the reasons outlined in the linked question above

这篇关于在FACT表中使用ANYDATA / SQL_VARIANT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！