Hi all,
I'm new to SQL AS and MDX in general but I'm trying to build the following and maybe you guys can help me.
I have a snowflake database with a table containing subscription. Each subscription has a date/time at which it starts and a date/time at which it is terminated:
subscription:
And there is a dimension-table called datetime:
AS will generate the count-measures for start-date and end-date (e.g. the number of subscriptions started at any given moment: 1 started at 10-jul-2005 17:00). But I also want to know how many subscription are active at any given moment (e.g. 0 at 1-jan-2005 12:00, but 2 at 1-jan-2006 12:00 and 4 at 1-aug-2005 12:00). In SQL I would produce something like 'select count(*) from subscription where measuredate between start_date and end_date' but how do you create something like that in AS using MDX?
TIA,
Ferdinand
Not sure how well this Adventure Works query corresponds to your scenario, but it computes the number of products that are "active" during the selected Calendar Date member:
>>
With
Member [Measures].[ProductCount] as
Count(Filter(existing [Product].[Product].[Product],
[Product].[Start Date].MemberValue <=
ClosingPeriod([Date].[Calendar].[Date]).MemberValue
AND ([Product].[End Date].CurrentMember is
[Product].[End Date].[Active]
OR [Product].[End Date].MemberValue >=
OpeningPeriod([Date].[Calendar].[Date]).MemberValue)))
select
[Date].[Calendar Year].Members *
{[Measures].[ProductCount]} on 0,
[Product].[Category].Members on 1
from [Adventure Works]
All Periods CY 2001 CY 2002 CY 2003 CY 2004
ProductCount ProductCount ProductCount ProductCount ProductCount
All Products 606 283 411 534 406
Accessories 35 3 13 32 22
Bikes 125 30 65 95 60
Clothing 48 7 28 41 20
Components 189 34 96 157 95
>>
|||Sorry for my 'not so very fast'-reply, but I had some other bussines to attend to. The above example is not entirly what I need. I need to create three measures with the model I posted:
1. I need to count all activations (e.g. all subscriptions that are activated (=start_date) at an given moment
2. All terminations (=end_date) at an given moment
3. All active subscriptions (= given date between start_date and end_date) at an given moment.
1 and 2 are relativly simple. These are accomplshed by using the generated [Count of Subscription] measure and the also generated [Start DateTime] and [End DateTime] dimensions. But the third measure is not so simple (for me that is). Has anyone got a clue on this?
The pivot I whish to create will look something like this:
Well, in your report there is only one date for number of subscriptions, there isn't start_date and end_date. So I assume it means number of active subscriptions at that date. The standard way of doing it is maintaining in fact table measure "Number of subscriptions", and then you will put 1 for it when there is an activation and -1 when there is a termination. By implementing running sum calculation on the lowest level of Date dimension - you will get what you need. This problem is the same as classic inventory management problem - you can read more about it here: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm
|||
Thanks for your tip Mosha.
I've created a view where there is a 1 for each activation and a -1 for each termination and indeed when you sum them up at a specified day the number of active subscriptions is acurate. I use this MDX script to create the sum at the specified day:
CREATE MEMBER CurrentCube.[Measures].[Number of active subscriptions] as
sum
(
[Date Time].[Day].Level.members(0):ClosingPeriod([Date Time].[Day].Level),
[Measures].[SubSum]
),
visible=true;
However, this script does not seem to work at other levels then day (my time dimension contains two hierarchies: year->quarter->month->day->hour and year->week->day->hour). The results at month level for example is the result of the sum of all records the fall within that month and not the sum of all records up to the first day of the month (which should be the case). Same goes for week, quarter and year level. The lowest level (hour) works fine, that is, it contains the same value as for the whole day.
Can you tell me what I'm doing wrong?
|||
I don't think at the month level you want to sum up to the first day of the month. I think you want to get the sum up to the current day. While it looks like this problem is for LastNonEmptyChild semiadditive calculation, it actually can be solved more efficiently and still give correct results with LastChild. And if you don't want to work with LastChild semiadditive aggregation, you can simulate it with the series of lines like
SCOPE (Measures.[Number of active subscriptions);
[Date Time].[YQMDH].[Month] = [Date Time].[YQMDH].LastChild;
[Date Time].[YQMDH].[Quarter] = [Date Time].[YQMDH].LastChild.LastChild;
[Date Time].[YQMDH].[Year] = [Date Time].[YQMDH].LastChild.LastChild.LastChild;
// etc
END SCOPE;
|||Thanks, that last part did the trick!
No comments:
Post a Comment