I am trying to create a calculated measure in my cube (MDX Exprsssion), but first I want to write the query in MDX so I can test it. I am having issues getting this to be "dynamic"
WITH
MEMBER Measures.[Disconnects] AS
(
(
[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],
[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]
),
[Measures].[Subscription Cancels]
)
MEMBER Measures.[Subscriptions] AS
(
(
[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],
[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]
),
[Measures].[Subscriptions New]
)
SELECT {
Measures.[Subscriptions]
,[Measures].[Disconnects]
} ON COLUMNS
FROM [MyCube]
this query above gives me the correct values for the day I have in there, but I want it to work over all days, and so the end users can slice by other dim's as well. Basically I am trying to get the number of disconnects that occured on the same day as that disconnect subscribed. I have a feeling my underlying cube/dw is too aggregated already or something else, but I can get this pretty close to what I want. Does anyone have any insight? I have been looking at this
http://sqljunkies.com/WebLog/mosha/archive/2007/06/01/count_in_flight_mdx.aspx
because it is similar, with the two different date dim's, but I just want to always get results where the two date dim's are the same day...
Thanks
If we just take the first measure (as both can use the same pattern) using link member should make the date range dynamic. I needed to pick one of the dates as the one that you will be analyzing by, so I assumed [Transaction Time] was probably the one.
eg.
MEMBER Measures.[Disconnects] AS
(
LinkMember([Transaction Time].[Calendar Date].CurrentMember,
[New Subscription Date].[Calendar Date]
),
[Measures].[Subscription Cancels]
)
This should work OK when you are looking at specific days for Transaction Time, but if you wanted to look at the sum of all transactions where the cancellation and subscription occurred on the same date for a month, week, quarter etc. , assuming that you had a [Calendar] hierarchy then you would need to do something like the following in order to sum over a set of days.
MEMBER Measures.[Disconnects] AS
SUM(
DESCENDANTS([Transaction Time].[Calendar].CurrentMember,
[Transaction Time].[Calendar].[Calendar Date])
,
(
LinkMember([Transaction Time].[Calendar Date].CurrentMember,
[New Subscription Date].[Calendar Date]
),
[Measures].[Subscription Cancels]
)
)
)
|||
wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?
When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run
I think the solution above is totally the right track though, maybe I just need to tweak something more...
|||
 dart_board wrote: |
| |
| wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy? When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run |
In regards to the hierarchy, I used Calendar as an example as it is something that most people can understand. When I set up a date hierarchy, personally, I usually set up one (or more) hierarchies and then I usually hide the "raw" attributes that are present in the hierarchy. You don't have to do this, you can leave the attributes separate, but the important thing is to get your attribute relationships set up correctly, which it sounds like might be an issue in your case as this would lead to apparent duplicating values.
It's important the each attribute either has a direct or indirect (not both) relationship to the key attribute. And indirect relationship is where one attribute is related to another to form a sort of chain back to the key attribute. One way of thinking of relationships is that an attribute has a relationship to it's parent. So a TimeID has a relationship to a Date and a Date has a relationship to a month and so on. Notice that Year is at the top level of granularity so it is not related to any other attributes.
eg.
TimeID
- Date
Date
- Month
Month
- Quarter
Quarter
- Year
Year
If you don't create explicit hierarchies in your date dimension, or you want to leave the "raw" attributes visible, you should be able to replace the descendants function with the existing keyword which should reduce the set of Date members down to just those that are related to what ever Time attribute is currently selected. (it figures this out using attribute relationships, so these need to be right). Notice that I have used a naming convention of <dimension>.<Attribute>.<level>.members, this is important as if you just used <dimension>.<attribute>.members, it would include the default "All" member and would return all dates which is not what you want.
MEMBER Measures.[Disconnects] AS
SUM(
EXISTING [Transaction Time].[Date].[Date].Members)
,
(
LinkMember([Transaction Time].[Calendar Date].CurrentMember,
[New Subscription Date].[Calendar Date]
),
[Measures].[Subscription Cancels]
)
)
)
Hope this helps
|||yeah that totally makes sense. I brought up a while ago that our hierarchies weren't set up correctly, but no one would beleive me
- The EXISTING seems to work well. My only issue now is that when I try to use BOTH caclulated measures together, one is summed up over all and one is correct, depending on which date I slice on (transaction or new subscription) Its like I can get either one or the other to work, but not both together.
I will keep digging into that, if you have any quick insight that would be awesome. You really know your stuff
