An approach to SSAS dealing with rapid change dimension (Quickly changing Dimension)

Fast Change Dimension (QCD) is relative to the slow change dimension (SCD), such as "member dimension" in the "membership level" of the change is not very frequent dimension attributes belong to SCD, and such as "Age", "last deal date" such a change of the dimension can not be the idea of SCD to solve the problem, The dimensions that SCD can handle are usually change_log in an OLTP library, such as when a member upgrades a downgrade in a production library, but a dimension such as "Age", "last traded date", if it is also recorded in an OLTP library, will explode in the dimension table. Speaking of which, crossing may have some questions about the age dimension: Why not use trading hours in the trading table to calculate "age of the day"? Do not worry, first of all to clarify two concepts: "Age" and "age of trading", if you want to classify these two dimensions, the latter "trading age" should be classified as "transaction dimension", and the former "age" is to be classified as "membership dimension", "Transaction Dimension" in the "Trading age" Can only be used to analyze trading facts (for example, what age groups are analyzed for transactions), while "age at the time" in the "membership dimension" is to be able to analyze both the trading facts and the membership facts, that is, the use of this dimension is not only to analyze the number of members of a certain age to generate the volume, You also need to be able to analyze the number of members of a certain age (not every member will have a deal). This problem can be extended to SCD with the surrogate key to the correlation between the dimension table and the fact table can do what analysis, the same reason, if the "member dimension" is through the surrogate key and "trading facts" to generate correlation, then use "Member dimension" to analyze "member facts" is not so easy (mainly performance will be discounted), Because this time the "member dimension" of the unique identity is not account_id but the surrogate key, but the "member facts" to do analysis is distinct account_id instead of the distinct agent key, OK, a little off-topic, or continue to talk about QCD. Since it is not possible to persist the change history as SCD and hope to realize self-service BI in the way of the UDM, is there any good idea to solve the efficiency problem of this kind of analysis in SSAS? My answer may not be right for everyone, but it's very meaningful to have a little bit of inspiration for you, or you might want to discuss it with a better plan.

Let's take a look at how traditional bi write SQL does this kind of analysis, first of all, make it clear that the OLTP library has only the "member birthday" of each member, "Trading Time" for each transaction, and does not need any additional information for SQL, usually a point-in-time snapshot of the member. This kind of member dimension information back to this point of time, for example, users want to analyze the day of 2014-01-01, "number of members", "Number of transactions", "Transaction amount" Three fact measures, and according to the date of the day of 2014-01-01 "member age" group statistics, Then this SQL script is necessary to enter a date parameter and designated as 2014-01-01, and then according to the date parameter and "member birthday", "Trading time" to do the member snapshot of the day to get all the members of the day "member age" and "last deal date" and other dimensions of information, And then left related to the transaction table to do the aggregation analysis, the idea is roughly the same, the specific implementation varies from person to person.

Now that we're back in SSAS, can we use the idea above to make a "member Snapshot Dimension"? The user randomly assigns a date and commands SSAS process update to generate a member snapshot of this date, which includes all such fast-changing dimensions as "Member age", "last Deal date", and even the attributes of SCD such as "membership level" can be put in , the dimension table key or account_id, so that it can guarantee and "member facts", "trading facts" at the same time, my implementation is exactly the same, because my customers choose the BI tool is Excel, so everything is going to happen, just need to use VSTO to do a plug-in function, Specify the date that the snapshot should be rolled back to the user and submit

After the server receives the command, only the simple process update this "member Snapshot Dimension", and this dimension table corresponding to the data source is a function, the reason is useless view, because view does not support the parameter

ALTER FUNCTION [dbo]. [f$dimperiodaccount] (    @UDPvarchar)RETURNSTABLE  as RETURN (    .................   )

See I made two "member snapshot Dimension", it is very useful in making membership change analysis, for example, to analyze the number of ordinary members in 2013-01-01 this day in 2014-01-01, how many upgraded to Gold card, how many upgraded to diamond cards, and each group Target's trading facts.

This is the current tradeoff between performance and user experience, I can find the most suitable solution, take out to give you a discussion of the brick

An approach to SSAS dealing with rapid change dimension (Quickly changing Dimension)