In my AS2005 cube, I have a time dimension with the following attributes: year, quarter and month. Also, there is currently no hierarchy by design.
Is there a way to make a custom attribute/time period that reflects the last 12 months (R12M) and work in the same manner such as year, quarter and month?
The time dimension would then have: years, quarter, month and R12M.
As a result, a user can drop a measure such as Sales Amt and then add the R12M attribute from the time dimension and would then receive the total sales amount for the last 12 months.
I hope that made sense. I can and have written a calculated member for R12M Sales, but this R12M view is very common and repeated in our world. As an example, we have R12M Sales, R12M Volume, R12M # of Customers, etc.
Right now I have a calculated member for each one of those metrics, but it would be nice to move the repeatedness of R12M into the time dimension.
Is this possible? If so, how?
Thanks!
If I am understanding your question correctly all you would need to do is to create a calculated member and then place it on one of your attribute hierarchies. Here is an example calculated member that resides on the [Time].[Month] hierarchy and sums up any measure for the last 12 months that have sales data. You can remove the filter if you do not load month members unless there is data.
HTH,
- Steve
//BEGIN Last 12 Sales Months
CREATE MEMBER CURRENTCUBE.[Time].[Month].[All].[Last 12 Sales Months]
AS
Aggregate(
Tail(
Filter([Time].[Month].Members,
([Measures].[Sales Amt]) <> 0),
12)
),
FORMAT_STRING = "Standard",
VISIBLE = 1 ;
//END Last 12 Sales Months
|||Steve,
Thanks for your help. Unfortunately, I'm still fairly new to Analysis Services and the whole cube thing that my understanding is very limited. With that said, assuming your approach will work, how do I add a Calculated Member to a time dimension?
I'm essentially doing all of this in BI Development Studio.
Regards,
Johnny|||Okay, so I think I know how to add the calculated member to the time dimension. I'll let you know how it goes.|||I don't think your suggestion is what I'm looking for, or I don't understand it.
I'd like to add an attirbute to the time dimension that represents the last 12 months, which would then be applied to any measure, whether it's sales or volume.
So a user could drag this "Last 12 Months" attribute from the time dimensoin and then whatever measure is dropped would result in the value for the last 12 months.
I believe your suggestion isn't as flexible and is only possible for sales?
Does that make sense?|||
Have you tried creating the member and adding to your "Month" attribute hierarchy? The reference to [Measures].[Sales Amt] is not needed and was used to demonstrate how you could filter out empty months if they existed in your cube. Here is a revised version that would give you a member on the "Time.Month" hierarchy which could be place on rows or columns for any measure to give you the twelve month total.
CREATE MEMBER CURRENTCUBE.[Time].[Month].[All].[Last 12 Sales Months]
AS
Aggregate(
Tail(
[Time].[Month].Members,
12)
),
FORMAT_STRING = "Standard",
VISIBLE = 1 ;
|||I have not implemented a time hierarchy, so Month, Quarter, Year and now [R12 Months] are all attributes from my time dimension.
Hopefully, I'm using the correct terminology. The current members for the Month attribute are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and "R12 Months".
If I add both the Year and Month attributes as row fields with Sales as a Data Item, I'm not getting what I want.
For example, for:
Year = 2006, Month = 1, Sales = $10M. "R12 Months" also equals $10M.
In this scenario, I'd want "R12 Months" during 01/2006 to be equal to the total sales for the 12 month period of 01/2005 to 12/2005.
If it was June 2006, I'd want the total sales for the period of June 2005 to May 2006.
Is this possible?
|||I think I understand what the issue is now.What you need to do is to create a new attribute called “Period Calculations” and then create your rolling 12 month member using the “Period Calculations” dimension. Here are the steps you will need to follow:
1.) Edit your data source view and add a “Named Calculation” to your time dimension called “Period Calculations”. Enter the following string value for the calculation:
‘Current Period’
2.) Edit your Time dimension and drag the “Period Calculations” column you added to the time entity in the data source view to your “Attributes”.
3.) Click on the new “Period Calculations” attribute and then hit “F4” to bring up the properties window. Set the “IsAggregatable” property to “False”.
4.) Process your time dimension and cube.
5.) Add the following to your cube calculations script:
CREATE MEMBER CURRENTCUBE.[Time].[Period Calculations].[Rolling 12]
AS
Aggregate(
[Time].[Month].CurrentMember.Lag(12):[Time].[Month].CurrentMember *
{([Time].[Period Calculations].DefaultMember,
[Time].[Year].[All],
[Time].[Quarter].[All])}),
VISIBLE = 1 ;
This will allow you to display:
2005 10 Rolling 12 (Sum of any measure for 10-2004 through 10-2005)
2005 11 Rolling 12 (Sum of any measure for 11-2004 through 11-2005)
…
2006 1 Rolling 12 (Sum of any measure for 1-2005 through 1-2006)
HTH,
- Steve
|||Steve,
Your help has been invaluable! However, it's not returning the exact figures I'm expecting. Unfortunately, I can't figure out what it's really doing. The "Current Period" works just fine, but the "Rolling 12" doesn't.
Unfortunately, since I don't know what it's really doing, I'm not sure what kind of information I can provide that would help you understand what's going on.
Adding just the Year and Month attributes, I get "$115,488,690" for the Year 2005 total. Adding in "Period Calculations", "Rolling 12" never has the value of $115,488,960. In my ideal world, I would expect to see that figure of $115,488,960 in January 2006.
Our Rolling 12 is for the prevoius 12 months -- not including the current month. So for January 2006, the Rolling 12 period for us is January 2005 thru December 2005. If it was September 2006, the Rolling 12 period would be September 2005 thru August 2006.
I assumed with your misunderstanding of our Rolling 12 period, I would see the figure of $115,488,960 under Rolling 12 for December 2005. For what it's worth, the figure I see is higher: $125,075,196.
Any idea what I can do or provide that would help you understand what's going on?
Again, I REALLY appreciate your time and efforts!!!
|||Well at least I think we are getting closer. First lets look at adjusting the MDX to calculate the 12 months prior to the current month. To accomplish this all we have to do is adjust the "range" specifid in the Aggregate dimension. I am using the "Lag" function which will use the current month to move back through the previous periods, so all that is needed is to set the range to Lag(13):Lag(1) as shown here:
CREATE MEMBER CURRENTCUBE.[Time].[Period Calculations].[Rolling 12]
AS
Aggregate(
[Time].[Month].CurrentMember.Lag(13):[Time].[Month].CurrentMember.Lag(1) *
{([Time].[Period Calculations].DefaultMember,
[Time].[Year].[All],
[Time].[Quarter].[All])}),
VISIBLE = 1 ;
Now there is another topic that we have not discussed that may also be influencing your results. In an earlier post you stated that your Month attribute had the following members: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 and 12. I made what may be a bad assumption that your "Key" values for the Month attribute were unique. What I mean by this is that the source data used to populate your month attributes looks like the following:
Key Name
200401 1
200402 2
...
200501 1
200502 2
If this is not the case then the data for each month will sum for all your years and you will not get the results you are looking for. Just another item to check.
HTH,
- Steve
|||
Hi Steve,
I tried the new definition and notice that for January of any year, it will not having the "Rolling 12" available, only "Current Period".
Here's how the underlying Date table looks like that the Time dimension points to:
Date_key | Date | Day | Year | Quarter | Month
2 | 1996-01-01 00:00:00.000 | 1 | 1 | 1 | 1996
3 | 1996-01-02 00:00:00.000 | 2 | 1 | 1| 1996
...
3783 | 2006-01-29 00:00:00.000 | 29 | 1 | 1 | 2006
....
and so on until 12/31/2020.
I'm in my dsv, I used a Named Query to pull data from this Data table where year <= year(getdate()).
So with our structure in our date table, anyway to get the results that I am looking for?
Thanks,
Johnny
|||
Johnny,
There is an important concept that you need to be aware of with regard to "attribute" hierarchies in 2005. It is important that your attribute keys are unique when you are trying to sum up the months for a specific year. The description of your time dimension leads me to believe that you do not have unique keys and the calculation you want will not work without them. There is a great section in one of the ProjectReal write ups that talks about this issue. Check out the following URL:
http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx#EGAA
and do a search on:
"Best Practice (required): You must always ensure attribute key uniqueness"
HTH,
- Steve
|||Steve,
Please see my previous post regarding the new behavior of "Rolling 12". I edited the post without realizing you had already replied.
Unfortunately, I don't understand MDX or Analysis Services that well at all.
I'm somewhat confused with my attribute keys not being unique. You're not referring to the date_key right?
This issue that you bring up, would it affect all calculations dependent on the Time dimesnion, or just in the case of Rolling 12?
I used to have a calculated member (still do, but it's not visible) for each Rolling 12 metric: Roling 12 Sales, Rolling 12 Volume, Rolling 12 # of Customers, etc. These all worked and calculated correctly.
I can provide you with the expression if that would help.
Regards,
Johnny
|||Johnny,
There are many reasons why the unique key issue could cause difficulties. Given the limitations of this method of communication it is hard for me to know exactly what the issue is in your particular case. Feel free to contact me via my direct email and we can work further on this off line if you still need assistance.
- Steve
|||To anyone that may be reading this, I seriously cannot express how much appreciation I have for Steve helping me in this thread and offline. Again, thank you Steve!!!!
No comments:
Post a Comment