December 5, 2013 at 10:22 am
I have built my first cube and I believe it is setup properly but I am having hard time wrapping my mind around how to report some data.
My cube is based on admissions to a university and I would like to build a report that compares applications to date for a specific term and compare it to another term based on the same date last year. Example, Dec 5, 2013|2014Fall and Dec, 5, 2012|2013Fall. The users should be able to pick the 2 terms and at least 1 date.
I have tried to build an MDX script to test this with hard-coded values but I am having problems getting the dates to work from essentially the beginning of time to the specified date and summarizing the data by term.
Ultimately, some end users will be getting this data in a canned report through SSRS and some will be using Excel to slice and dice the data. However, each instance requires this flexibility.
Am I going down the wrong path with the MDX script since I will be using Excel and SSRS? If yes, then how is the reporting requirement performed in Excel and SSRS?
Thanks
December 6, 2013 at 3:26 am
You could generate a calculation in your cube using ParallelPeriod. Then you can use your Date dimension to slice and dice the data as you need. A simple example would be
SUM(
PARALLELPERIOD([Date].[<Hierarchy>].[Year], 1, [Date].[<Hierarchy>].CURRENTMEMBER)
, [Measures].[<Your Measure>])
)
for previous year comparison at any level of your date hierarchy.
For a more comprehensive date based MDX solution, check out Marco Russo's DateTool dimension here and adapt to your needs.
Hope that helps 🙂
December 6, 2013 at 9:30 am
Great help. I have played around with the code somewhat and came up with this...
WITH MEMBER [MEASURES].[COUNTAPPS] AS
(SUM(
PARALLELPERIOD([Dim Date 1].[CALENDAR].[Calendar Year Number],1 , [Dim Date 1].[CALENDAR].CurrentMember)
, [Measures].[EVENT]) )
SELECT {
([Dim Start Term].[APPL START TERM].[2013AY]),
([Dim Start Term].[APPL START TERM].[2012AY])}*
{ [MEASURES].[COUNTAPPS]} ON 0
,{[Dim Application Category].[APPLICATION CATEGORY DESC].CHILDREN} ON 1
FROM FACT_APPLICATIONS
WHERE ([Dim Date 1].[CALENDAR].[Calendar Year Number].[2013])
Unfortunately, it does need some tweaking. Again, I am pretty new to MDX but I think what this is showing me is all of applications for the year versus the previous year. That's great and I will use this for a separate measure however, what I really need it to do is to compare each start term from the 'beginning of time' or at least 1960 which is how far back my date dimension goes to a choosen date. The reason I need to go back so far (probably not back to the 60's but at least 3 years) is that some applicant defer their application to 1 or 2 years out and those deferrals still get captured in the deferred term. In easy terms, I would to "all time" to date(preferably yyyyMMdd)
In saying that, what would I need to do to modify my script to reflect the requirements?
December 6, 2013 at 12:41 pm
I think I solved it!
WITH
MEMBER[Measures].[APPLICATIONS2013AY] AS SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2013-06-21 00:00:00.000]},[Measures].[EVENT])
MEMBER [Measures].[APPLICATIONS2012AY] AS SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2012-06-21 00:00:00.000]},[Measures].[EVENT])
SELECT
{([Dim Start Term].[APPL START TERM].[2013AY],[Measures].[APPLICATIONS2013AY]),
([Dim Start Term].[APPL START TERM].[2012AY],[Measures].[APPLICATIONS2012AY])}ON COLUMNS,
{[Dim Application Category].[PK APPLICATION CATEGORY].CHILDREN}*
{[Dim Application Category].[APPLICATION CATEGORY DESC].CHILDREN} ON ROWS
FROM FACT_APPLICATIONS
I have verified the numbers against the TSQL script and the numbers at least check out. If someone could critique the logic that would be great!
Eventually, I would like users to be able to be able to select the dates and terms for comparison via SSRS and or Excel. Any tips moving forward would be helpful.
Thanks.
December 9, 2013 at 2:06 am
Hi George,
I'm glad you figured it out - apologies, I had gone home for the weekend and I don't even like to think about this sort of thing at the weekend 😀
Your code will do it but I have to say I'm not a fan of those hard-coded dates. Perhaps consider adding an attribute to your date dimension (either in the SQL table or as a Calculated Member in the data source view) for something like TermStart as a boolean. You could then construct your MDX to be far more dynamic.
As you're new to MDX, I can think of no better guide to not only the language but the concept of MDX than the guide by Frank Banin on this very site: http://www.sqlservercentral.com/articles/MDX/91228/
Good luck 🙂
December 9, 2013 at 9:35 am
Brilliant! Thanks.
The hard-coded dates are there just for testing. I am hoping to use the dates and start terms as parameters in SSRS reports.
However, I am intrigued by your suggestion. Are you suggesting that in my date dimension that I apply a start term to a date as well? If so, I can't really do that only because applicants can defer their application to the following year which would then leave me with the possibility of having 2 start terms associated to a date. If not, I am open to any enhances that will make my data more dynamic and easier to manage.
Thanks.
December 10, 2013 at 3:50 am
Ah ok, I didn't think of that. Perhaps if you created a measure group from your candidate data you could include a StartTermDateKey (or however you would name it) in the source which could then be used in calculations if you linked it to the Date dimension in the cube in a role playing context (possibly hidden if you're only using it for calculation).
Role Playing Dimensions:
http://technet.microsoft.com/en-us/library/ms174487%28v=sql.90%29.aspx
December 11, 2013 at 9:03 am
I checked out you suggestion but I don't think it will work because even though the start term itself has a start and end date, the time period that applicants apply for it do not.
My problem now is how to create a calculated member such as
SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2013-06-21 00:00:00.000]},[Measures].[EVENT])
into
SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[@full date]},[Measures].[EVENT])
I can get the hard-coded version above to work after I deploy my cube and browse the data but I only get #value for the 2nd code option. The reason I am doing this is to prepare a report in Excel 2010 so that users can easily pick a date.
December 12, 2013 at 2:50 am
You'll only be able to use parameters if the calculation is part of a query (say, one that's being run from SSRS).
Depending on exactly what your parameter is passing the code would look something like this:
SUM({NULL:STRTOMEMBER("[Dim Date 1].[CALENDAR].[full date].&["+CSTR(@full date)+"]"},[Measures].[EVENT])
The above is assuming your parameter would be passing a date in the format of "2013-06-21 00:00:00.000".
I hope that helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply