SSAS：日期维度成员的动态过滤(SSAS: dynamic filtering of date dimension member)

我有数据库和SSAS。

在DB中：

1）事实表factSales ：

+-----------------+----------+ | ColumnName | Datatype | +-----------------+----------+ | JoinDate | date | | TransactionDate | date | | Amt | money | | CustomerId | int | | ....... | | ....... | +-----------------+----------+

在SSAS中：

度量组factSales （具有度量：TotalAmt，Sales Count，Customer Distinct Count）维度JoinDate （基于dimDate ）维度TransactionDate （基于dimDate ）

我需要添加一个功能来实现：

例如，用户从2015-01-01到2015-02-01选择TransactionDate 。我需要补充一下：

新计算成员BaseCustomersAmt ，仅显示JoinDate小于[ 2015-01-01 ]即1年的客户在[ 2015-01-01 - 2015-02-01 ]期间内的销售额新计算成员NewCustomersAmt ，仅显示JoinDate大于或等于[ 2015-01-01 ]即1年的客户在[ 2015-01-01 - 2015-02-01 ]期间内的销售额

所以，我的想法是我需要将总销售额（ TotalAmt ）分成两组 -

第一组是从选定期间加入1年多以前的人，其余的第二组 - 在选定期间1年前的日期或之后加入。

当然，用户可以创建两个单独的报告并使用不同的加入日期范围，但要求是在一个报告中执行并自动执行。

有可能实现吗？请帮忙。

I have database and SSAS with me.

In DB:

1) fact table factSales:

+-----------------+----------+ | ColumnName | Datatype | +-----------------+----------+ | JoinDate | date | | TransactionDate | date | | Amt | money | | CustomerId | int | | ....... | | ....... | +-----------------+----------+

In SSAS:

Measure group factSales (with measures: TotalAmt, Sales Count, Customer Distinct Count) dimension JoinDate (based on dimDate) dimension TransactionDate (based on dimDate)

I need to add a functionality to achieve:

For example, a user chooses TransactionDate from 2015-01-01 to 2015-02-01. I need to add:

A new calculate member BaseCustomersAmt, which shows only sales made within [2015-01-01 -- 2015-02-01] period by customers where JoinDate is less than [2015-01-01] i.e. 1 year A new calculate member NewCustomersAmt, which shows only sales made within [2015-01-01 -- 2015-02-01] period by customers where JoinDate is greater than or equal to [2015-01-01] i.e. 1 year

So, the idea is that I need to split the total sales (TotalAmt) by two groups -

first group is those who joined more than 1 year ago from chosen period, second group the rest - who joined on or after a date which is 1 year ago from a chosen period.

Of course, the user can create two separate reports and use different ranges of Join Date, but the requirement is to do it in one report and do it automatically.

Is it possible to achieve? Please help.

最满意答案

当你说

例如，用户选择2015-01-01至2015-02-01的交易日期。

我将假设它在日期级别并将计算每个日期。因为我不知道你是否有一个具有年 - 日关系的时间层次结构，所以我只需要使用LAG(365)来回归1年。否则你可以使用PARALLELPERIOD

CREATE MEMBER [Measures].[BaseCustomersAmt] AS AGGREGATE( { NULL :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365) } ,[Measures].[Sales] ),VISIBLE=1; CREATE MEMBER [Measures].[NewCustomersAmt] AS AGGREGATE( { LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365) :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]) } ,[Measures].[Sales] ),VISIBLE=1;

编辑：

假设您有一个年 - 月 - 日期层次结构。

CREATE MEMBER [Measures].[NewCustomersAmt] AS AGGREGATE( { ParallelPeriod( [JoinDate].[YearMonthDate].[Year] ,1 ,LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate]) ) :LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate]) } ,[Measures].[Sales] ),VISIBLE=1;

对于动态滞后......我猜它会变得更棘手......但你可以尝试在你的baseDayDimension上进行LAG。不知道是否会奏效。

.LAG( iif([baseDayDimension].[Day].CURRENTMEMBER IS[baseDayDimension].[Day].[All] ,365 ,[baseDayDimension].[Day].CURRENTMEMBER.MEMBER_VALUE ) )

When you say

For example, user chooses Transaction Date from 2015-01-01 to 2015-02-01.

I'm going to assume it's at the Date Level and will calculate for Each date. Since i don't know if you have a Time Hierarchy with year-date relationship i'll just use LAG(365) to go 1 year back. Otherwise you can use PARALLELPERIOD

CREATE MEMBER [Measures].[BaseCustomersAmt] AS AGGREGATE( { NULL :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365) } ,[Measures].[Sales] ),VISIBLE=1; CREATE MEMBER [Measures].[NewCustomersAmt] AS AGGREGATE( { LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365) :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]) } ,[Measures].[Sales] ),VISIBLE=1;

EDIT:

Let's assume you have a Year-Month-Date Hierarchy.

CREATE MEMBER [Measures].[NewCustomersAmt] AS AGGREGATE( { ParallelPeriod( [JoinDate].[YearMonthDate].[Year] ,1 ,LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate]) ) :LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate]) } ,[Measures].[Sales] ),VISIBLE=1;

For a Dynamic Lag... i guess it gets more tricky... But you can try to LAG on your baseDayDimension. Don't know if will work.

.LAG( iif([baseDayDimension].[Day].CURRENTMEMBER IS[baseDayDimension].[Day].[All] ,365 ,[baseDayDimension].[Day].CURRENTMEMBER.MEMBER_VALUE ) )

更多推荐