How do you use MDX to find the number of items between two dates, when the start date and end date are different role playing dimensions?
I had an interesting question from Ricardo Santos on another frog-blog post this week (MDX Calculated Member spanning multiple date dimensions). I answered Ricardo in the post, but it was a good question which I thought deserved a full blog post on its own. So thanks to Ricardo for prompting this.
The Scenario
If you have a ‘Project’ fact table, which has two dates (start date and end date), how do you find out how many projects are active on any given date. In this scenario we would have three role playing date dimensions; Date, Start Date & End Date.
We want the user to be able to select a date (from the Date dimension) and then use that to filter the projects using the other two date dimensions.
This also applies to many other scenarios; accomodation occupancy, staffing levels, contracts, really anything with a start and end date.
The MDX answer
If there was just a single date dimension (i.e. a typical sales fact) then you could just filter it
([Measures].[Internet Sales Amount], [Date].[Calendar].&[20080110])
But what we need to do is combine two different date dimensions. In SQL, to find the number of active projects on 1st April 2013 we’d just use
SELECT Count(*)
FROM Projects
WHERE StartDate >= 20130401
AND EndDate <= 20130401
The equivalent in MDX is more complex as we have to filter a fact by two different dimensions. Starting with the [Start Date] dimension, we need to sum the project count for the projects with a start date on or before the required date:
AGGREGATE({NULL:[Start Date].[Calendar].[20130401]}, [Measures].[Project Count])
We also need to then find the project count for all projects with an end date on or after the required date
AGGREGATE({[End Date].[Calendar].[20130401]:NULL}, [Measures].[Project Count])
We can then use crossjoin (*) to combine them together.
AGGREGATE( {NULL:[Start Date].[Calendar].[20130301]}
* {[End Date].[Calendar].[20130301]:NULL}
, [Measures].[Project Count])
Note that you could also use FILTER to achieve the same goal.
This will find the project count for a specific date, but we now need to make the date dynamic. We want the calculation to use a date selected by the user from a 3rd [Date] dimension, applying this date to the other two dimensions (start date & end date).
Enter the LINKMEMBER function. This is a great tool in your MDX arsenal, allowing you to identify a member of one dimension from a selection in a different role playing dimension. e.g.
LINKMEMBER([Date].[Calendar].[20130414], [Start Date].[Calendar])
The example above will return the following member of the Start Date dimension.
[Start Date].[Calendar].[20130414]
When we integrate this into the previous MDX, we get:
AGGREGATE(
{NULL:LINKMEMBER([Date].[Calendar].CURRENTMEMBER
,[Start Date].[Calendar])}
* {LINKMEMBER([Date].[Calendar].CURRENTMEMBER
, [End Date].[Calendar]):NULL}
, [Measures].[Project Count])
This will take the CURRENTMEMBER of the [Date].[Calendar] hierarchy, and map it to the equivalent members in the [Start Date].[Calendar] and [End Date].[Calendar] hierarchies, before then using the resulting members to filter the Project Count measure.
And there you have it.
But, that's not strictly the right answer...
Data modelling
Whenever we have to write anything complicated in MDX, we need to ask ourselves the question of why?... Complicated MDX is all too often a symptom of poor data modelling. Usually caused when a data warehouse is designed with SQL in mind, not cubes/MDX. If you're planning to use cubes then you can reap huge benefits from remodelling your data accordingly. This could be in your ETL and warehouse, or you can just remodel your data in the cube data source view. Lets have a look at an example.
The DSV for the project fact we were looking at earlier would be something like this (simplified)
SELECT
, ProjectManagerKey
, CustomerKey
, StartDateKey
, EndDateKey
, 1 AS ProjectCount
FROM FactProject
So the project fact represents a project. A cube fact table should however represent an event, not an item (items should be dimensions, not facts). So we need to think of a project as two seperate events; a start and an end. Take a look at the query below
SELECT
, ProjectManagerKey
, CustomerKey
, StartDateKey AS DateKey
, 1 AS ProjectCount
FROM FactProject
UNION ALL
SELECT
, ProjectManagerKey
, CustomerKey
, EndDateKey AS DateKey
, -1 AS ProjectCount
FROM FactProject
Each project will therefore exist twice, once with a +1 on the project start date, and once with a -1 on the project end date. And we only have a single date dimension to worry about.
So how do we use this in MDX?
You can find out the number of active projects on any given date using
AGGREGATE({NULL:[Date].[Calendar].CURRENTMEMBER}
, [Measures].[Project Count])
i.e. the sum of all projects started/finished prior to the selected date.
To find out the change in the number of projects in any given time period
([Date].[Calendar].CURRENTMEMBER
,[Measures].[Project Count])
In the SQL world this is an inefficient approach, but it's perfectly suited to the way cubes work.
So by spending a little time thinking about the cube's data model, we can dramatically simplify our MDX, and improve our performance.
Frog-blog out
interesting approach,what if there were say 3 project states e.g. started, pending, end, would you recommend UNIONing all 3 states?
I did a talk on exactly this at the last SQLBits – you can view the recording at http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes.
The approach I usually use (obviously depending on the reporting requirements) is to turn the state changes into transactions in and out of a state. So you have a project state dimension, containing Start, Pending and End. When a project starts, +1 in the Start state. When is moves into pending you create a -1 in the Start state and a +1 in the pending state, etc. The cube can then provide very deailed analysis on the flow, bottlenecks, volume and change in each state over time. See the video for a more detailed explanation.
Hi Alex,
Get Latest Record Measure Based on Given Date using Calculated Member
My requirement is i need to pick up the latest Fact EmployeeActiveCount based upon the Given Date
Employee Table β EmployeeKey
Itβs an Detail Table β Supports type2 Data, for every new event it capture new record and the Last Record is the Latest record, same will enter in Fact Table
Date Table – DateKey — Year, Month,Querter,Day
Fact Table β EmployeeKey, DateKey, EmployeeActiveCount, EffectiveDate, ExpairyDate
For Each Employee active Record Count shows as 1 in the Fact Table
In SQL we can get the Latest Records Writing Condition like below
Select Sum(EmployeeActiveCount) From Fact Table
Where GivenDate between EffectiveDate and ExpairyDate
Based on the given dates, it will pick the latest record accordingly.
If we give date like 2014-01-01, it will check the latest records between EffectiveDate and ExpairyDate
And gives the Active Employee Counts between those Dates. By this When User select any date in Cube Or SSRS , Will get Active Employee Counts.
Please suggest how to achieve this Requirement Using Calculated Member.
Hi Durgasi
There are two approaches to this in cubes:
1) Use Semi Additive measures. Create a snapshot of the active employees on a daily/monthly (depending on the required level of detail), and aggregate the ‘EmployeeActiveCount’ using a LastNonEmpty aggregation in the cube.
2) Create employee starting/leaving events, using a query such as the following for your measure group
“SELECT EmployeeKey, EffectiveDate AS DateKey, 1 AS EmployeeActiveCount FROM FactTable
UNION ALL SELECT EmployeeKey, ExpairyDate AS DateKey, -1 AS EmployeeActiveCount FROM FactTable WHERE ExpairyDate IS NOT NULL”
This will create two records in the cube’s measure group, +1 when they start and -1 when they leave. Then the MDX is simply a sum of EmployeeActiveCount from the start of time to the selected date.
For more details on this approach, have a look at the following video: http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes
Regards
Alex
Hi Alex,
In addition to employee count, I have a requirement to get the count for new joiners, leavers for selected month.
Also would like do MoM comparisons. Would approach #2 be able to give these results?
Thanks,
Sachin
Yes absolutely. If you have a measure group with +1 for joiners and -1 for leavers, and a join/leave dimension, then you’ll have everything you need.
Thanks Alex, got it working as per approach #2. I already had a join/leave dimension π
I had earlier setup the fact for periodic snapshot (monthly), approach #1.
I do not see “reply” option after you comment, so have replied here to your below comment.
Thanks again for your help!
Thanks, I have managed to go through the recording and it is very informative. I particularly like the idea of thinking as events.
Based on state, lets assume you have scenarios where a client can have say 4 states, Active, Under Review, Suspended, Withdrawn. And the client statuses are not always sequential e.g.
Day 1: Active
Day 3: Withdrawn
Day 7: Active
Day 9: Suspended
Day 13: Under Review
Day 23: Active
How can we deal with this say when we need to get the status on say Day 8? I want to assume the approach above works as well? i.e. “backing out” the transactions?
I am new to MDX and trying to “unlearn” my t-SQL thinking.
Hi James
Yes this approach will cope with that, as entering any state will create a +1 against that state. You can move in and out of any statuses in any order and it will work.
Because we use a Sum to Date, all of the +1/-1 will cancel each other out until the latest record prior to the day being reported.
Alex
You are the man! I went through your video and it makes a lot of sense and I have managed to apply that. One question though, using your example for loan application, assuming on
Day 1 Status = Application
Day 1 Status = Review
Using the model of “Backing out” the transaction, that means a SUM:
Day 1 – Applications – 0
Day 1 – Review – 1
I am tempted to do a DATEADD to negate the Status to Day 2, but then that will not give the true picture.
How would you go about this?
Again thanks for all the help so far. Been a lifesaver!
This is correct behaviour – if the status moves from application to review on the same day, the loan can only be counted once or your totals will be incorrect. So it must be reported either as Application or Review, not both.
This approach will show the end of day status against each date. So if an application changes numerous times in a day, you can still track that, but only the final state of each day will be shown in the aggregation. You can enhance this by adding a time dimension, which would then allow you to break down a day into hours/minutes/etc.
Or if you want to track the number of loans that entered a status for each day, just filter out all the -1 records (add a dimension for this). Then you’ll see both statuses together on the same day.
Hi Alex,
Thanks so much for sharing your knowledge and experience.
I would like to ask you for a suggestions regarding my current challenge.
Background: We have a highly normalized enterprise Data Warehouse. The sales fact table uses row versioning, similar as Kimball proposed (Compliance-Enabled) with a ValidFrom, ValidTo timestamps and a Current flag to easily identify the latest record. The only different with the Kimball version is we generate a new surrogate key for new versions of a fact (Kimball proposed the same surrogate key in combination with the validTo timestamp).
The granularity of the table is the line order number. We have different status of every single article in the order, letΒ΄s say βopenβ, βcancelledβ and βshippedβ. We also store the net price, quantity, customer, etc.
My challenge is to build a cube with a measure group with measures like the sum of the net price per article of the open orders for a given date or the sum of the quantities for a given article in a given date in the cancelled orders.
I tried to use your ideas in order to design a data mart thinking in MDX and not in SQL but at the end Iβm not sure if is better to transfer this VelidFrom and ValidTo columns to the fact table in the data mart and fill the analysis requirements using MDX instead.
Have you faced a similar situation?
Any comments or guidelines would be extremely appreciated.
Thanks in advance,
Paul
Hi Alex,
Thanks so much for sharing your knowledge and experience.
I would like to ask you for a suggestions regarding my current challenge.
Background: We have a highly normalized enterprise Data Warehouse. The sales fact table use row versioning, similar as Kimball proposed (Compliance-Enabled) with a ValidFrom, ValidTo timestamps and a Current flag to easily identify the latest record. The only different with the Kimball version is we generate a new surrogate key for new versions of a fact (Kimball proposed the same surrogate key in combination with the validTo timestamp).
The granularity of the table is the line order number. We have different status of every single article in the order, letΒ΄s say βopenβ, βcancelledβ and βshippedβ. We also store the net price, quantity, customer, etc.
My challenge is to build a cube with a measure group with measures like the sum of the net price per article of the open orders for a given date or the sum of the quantities for a given article in a given date in the cancelled orders.
I tried to use your ideas in order to design a data mart thinking in MDX and not in SQL but at the end Iβm not sure if is better to transfer this VelidFrom and ValidTo columns to the fact table in the data mart and fill the analysis requirements using MDX instead.
Have you faced a similar situation?
Any comments or guidelines would be extremely appreciated.
Thanks in advance,
Paul
Hi Paul
There are a lot of ‘it depends’ here I’m afraid.
My preferred data model here is a transactional backout fact table. It creates a large fact table, but the cube becomes incredibly flexible.
1) Store an order line in the fact.
2) When a replacement comes in, create a negative ‘backout’ transaction for that order, with the original order details (but negative values) and the date of the replacement record.
3) Insert the new replacement record.
So two versions of a record result in 3 records in the fact.
I did a talk on this at the last SQLBits – have a look at the video here
http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes
Hope this helps.
Alex
Was this not the same technique discussed by Mosha Pasumansky back in 2007?
http://sqlblog.com/blogs/mosha/archive/2007/06/01/counting-in-flight-events-in-mdx.aspx
Yes you’re right, and thanks for the link. Mosha goes into more detail of the MDX solution including adding Root() to fix a potential problem with the Date dimension, useful to know. What Mosha fails to point out though is the [in my opinion, better] alternative of changing the underlying data model. Not always an option, but it’s my preferred technique if it’s possible. Although as the founding guru of MDX, one would expect him to focus on the MDX solution π
Hi Alex, I’m trying to apply your example MDX to return a count between start and end dates. It’s working as expected and with good performance for the Calendar Year and Month in my hierarchy, however the query just hangs if I try to expand the hierarchy to the date level. My date dimension has attribute relationships for the date – month – year.
Am I missing anything here? I assume the 3rd role playing date dim, in my case ‘Date Between’ is just a floating dimension i.e it has no relationship to a measure group?
Thanks
AGGREGATE(
{NULL:LINKMEMBER([Date Between].[Calendar Year – Month – Date].CURRENTMEMBER
,[Date Start].[Calendar Year – Month – Date])}
* {LINKMEMBER([Date Between].[Calendar Year – Month – Date].CURRENTMEMBER
, [Date End].[Calendar Year – Month – Date]):NULL}
,[Measures].[Count])
Hi Brad
Paul’s right. The problem with any MDX solution is that it can perform poorly when calculating on a leaf level. The best option is to alter the data model to simplify the query.
Although you don’t always have to rely on the ETL layer, you can often recreate the backout transaction approach just using a view under the cube. Worth investigating if you need to do a lot of these queries.
Regards
Alex
Hi Brad,
I also implementing a similar solution. It also works slowly for me when I drill down. I suppose the reason is, the funcition is evaluated for each member. Then if you have 5 year, the function is evaluated 5 times at this level, but 365 times for every year.
We must think in a aggregation table per day or the “backout” solution proposed by Alex, which in my opinion is the best option if you have full control of the ETL process.
Kind Regards
Paul
Hi Alex
I have a different problem in that I am trying to get a measure that will look at YTD LY Sales for a period that looks at orders placed before a certain date, I tried below but it keeps bringing back the YTD value, any insight as to where I am going wrong?
WITH
MEMBER [TEST] AS
SUM({NULL:[Sales Order Date].[Purchasing Model Calendar].[Date].&[2013-06-22T00:00:00]}*
(EXISTS([Dates].[Purchasing Model Calendar].[Date].MEMBERS,[Dates].[Current Day Europe].&[Y]),[Time Calculations].[YTD LY]),[Measures].[Extended Price Local – S])
SELECT NON EMPTY
{[TEST]}
ON COLUMNS,
NON EMPTY
{[Customers].[Sales Continent].CHILDREN*
[Customers].[Sales Country Designation].CHILDREN} ON ROWS FROM
(SELECT({[Customers].[Sales Continent].&[Europe]}) ON COLUMNS
FROM [Buyer])
I’d change the SUM() set to use currentmember of the Sales Order Date dimension, and move the actual date selection to the WHERE clause.
If that doesn’t work then you may have a problem in the MDX scope script behind the [Time Calculations].[YTD LY] attribute.
Regards
Alex
Hi Alex, I am new to MDX and trying to get customer sales for given date range (for example 2001.07.24 β 2007-08-01) for specific sales type.New customer means they have no transaction for previous two months.Δ° wrote something but didnt work.My query is below,
SELECT
{[Measures].[TranCount],[Measures].[Amount]} on columns
,
EXCEPT(
NONEMPTY
(
[Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
([Measures].[Fact Islem Count],[Date].[Dt].&[2001-07-24T00:00:00] : [Date].[Dt].&[2008-08-01T00:00:00])
)
,
NONEMPTY
(
[Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
OpeningPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(2)) :
ClosingPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(1))
)
)
ON rows
FROM [cubeSales]
where ([Tran].[TranType].&[C])
Great information. Thanks for posting. This saved me hours.
I used the mdx query to search between start date and end date but it is giving incorrect result
I build test fact with
startdate end date charges
Thanks for the information.
I just started learning BI, I have scenario where
I have a weekly run for all patient accounts and to see if hospital billed it or not
and goal is to build a cube and see week over week how we are progressing.
So I have fact table with
Patinetaccountkey
IsBilled
Charges
StarDateKey
EndDateKey
I used the mdx query to search between start date and end date but it is giving incorrect result
I build test fact :
startdate end date charges
1/5/2014 1/11/2014 $10
1/12/2014 1/14/2014 $20
1/15/2014 1/18/014 $30
MDX in my cube Calculated Member(CALC_charges)
SUM(
{NULL:LINKMEMBER([dim Date].[WK].currentmember, [startdatekey].[WK])}
* {LINKMEMBER([dim Date].[WK].currentmember, [enddatekey].[WK]):NULL}
, [Measures].[charges]
)
Result:
Week Calc_Charges
———————————-
1 $10
2 $50
3 $10
4 $10
why I am seeing Week 3 and 4 with values even though there is no associated fact.
Please help
Thanks,
Tata
Hi Tata
I’ve just replicated your data and code, and it works correctly. Therefore it’s something in your implementation that is wrong.
What I’ve got is:
CREATE TABLE [dbo].[test](
[TestFactKey] [int] IDENTITY(1,1) NOT NULL,
[StartDateKey] [int] NOT NULL,
[EndDateKey] [int] NOT NULL,
[Charges] [int] NOT NULL
)
GO
INSERT INTO dbo.test
VALUES (20140105, 20140111, 10)
,(20140112, 20140114, 20)
,(20140115, 20140118, 30)
GO
Then a standard role playing date dimension (integer DateKey in the form YYYYMMDD), added three times (Date, End Date & Start Date). Date has no relationship to the test fact. The other two dates are linked to the test fact with a regular relationship.
Then the calculation is
CREATE MEMBER CURRENTCUBE.[Measures].[CALC_charges]
AS
AGGREGATE(
{NULL:LINKMEMBER([Date].[Week].CURRENTMEMBER
,[Start Date].[Week])}
* {LINKMEMBER([Date].[Week].CURRENTMEMBER
, [End Date].[Week]):NULL}
, [Measures].[Charges]);
Then, the following query
SELECT [Measures].[CALC_charges] ON 0,
{[Date].[Week].[1]:[Date].[Week].[3]} ON 1
FROM [Test Cube]
Gives the results:
1 10
2 30
3 50
As expected. Can you double check your implementation to make sure it follows the above structure?
Also, please also take note of the blog post which talks about remodelling your data. You may want to consider this in order to simplify your MDX…
Regards
Alex
Alex,
Thanks for your reply.
I created everything from scratch and it worked.
And regarding remodeling data:
As i said before I am trying to build a cube on history to track the changes week by week.
In my scenario
1. every patient will be included in each week run
2. A patient Billed status may or may not change from week to week
for example:
for date 8/2/14 run for patient 1 billed is 0 and charges $100
for date 8/2/14 for patient 2 billed is 1 and charges $200
for date 8/9/14 run for patient 1 billed is 1 and charges same $100
for date 8/9/14 run for patient 2 billed is 1 and charges same $200
for date 8/16/14 run for patient 1 billed is 1 and charges same $100
for date 8/16/14 run for patient 2 billed is 0 and charges same $200
for each week i need metrics total patients,total billed and total billed ratio and total charges ratio
may be i need to look by week,Month, Qtr and year
fro week 8/2/14:
Total patients : 2
Billed: 1
Billed Charges: $200
for month August:
Total patients: 2
Billed: 1
Billed Charges: $100
I am trying with start date and end date with complicated MDX,
do you have any suggestion.
I appreciate all your help.
Thanks,
Tata
Thank you very much for this article. I also watched the video. Thank you very much.
I remodeled my cube and it’s very easy to calculate active transactions or project.
Please, I have a question.
In you MDX statement, after remodeling, you specify a date hiearchy from which the user must analyze the KPI. Calendar hierachy below.
AGGREGATE({NULL:[DATE].[Calendar].CURRENTMEMBER} , [Measures].[Project COUNT])
This oblige us to create one KPI for Week, another one for Month, Quarter, Semester and Year. I think it’s very very complicate.
Is it possible to write, only one MDX statement that won’t depend on the hierachy that the user has selected ? I mean, if the user select the [Week] hierachy or the [Month] hierachy then the MDX statement always return the good result.
Thanks.
Sivleam.
Hi Sivleam
I wrote another blog on this some time ago. Have a look at https://purplefrogsystems.com/2010/08/mdx-calculated-member-spanning-multiple-date-dimensions/ and see if it helps.
Regards
Alex
Thank you …
Hi Alex ,
I need to filter two different dates by start date and end date.
SQL statement looks like
select a, b, c , d
from table1
where Rdate >= @startdate and Ddate <= @enddate
Can you let me know how I can do the above statement in MDX.
I tried to put it under where clause in my MDX but it didn't work..
It throws an error
"Greater Than function expects a member expression for the 1 argument"
Thanks in Advance !!
KARI
Hi Kari
Like always with MDX, there are many ways of approaching this. However we shouldn’t think of this in terms of how we’d write a SQL statement, it’s important to get into an MDX mindset.
One option would be to define the subcube using sub-selects in the FROM statement:
SELECT … FROM (SELECT {[RDate].[Date].[20141029]:NULL} ON 0 FROM (SELECT {NULL:[DDate].[Date].[20141029]} ON 0 FROM [MyCube]))
So each {} set is the set of all required dates in that particular dimension, null being the start or end of the dimension (i.e. the earliest or latest date, depending on whether it’s null: or :null).
Hope this makes sense!
Alex
Thank Alex!
As I mentioned I am using parameters here. So would you be able to tell me how I put a parameter values to the same instead of the direct dates.
I am still working on the same issue.. Seems like nothing is working I hope your solution works π \
Thanks a bunch!
Kari
Hi Kari
There are two separate things here, the MDX functionality and then the parameterisation.
First I’d recommend getting the MDX working how you want with a hard coded date. Only when that is working should you then introduce the parameters.
When you get that far, just use the strtomember MDX function (and string concatenation if required) to convert the parameter value into a dimension member.
Regards
Alex
Hi Alex,
thanks for sharing your knowledge! Great article and talk! I’ve learned a lot from them so far.
If may I ask..
I’m working on a project and its reporting requirements are similiar to the one you presented but with a little difference:
Let’s say I have some “contracts” that are submited for analysis and, at the end of the day, they get a result/status (A – “approved”, F – “failed”, P – “Pending”). And their status may change, or not, day after day once they can be re-submitted for a new analysis.
From your approach, I can calculate the “TOTAL” amount of contracts by status until a certain date.
But, what if I wanted to know the amount of contracts that were approved in a interval (i.e, from May 5th 2014 to Jun 9th 2014)? In other words, I am interested in knowing the amount of contracts whose last status were approved in a specified period of time.
It’s like your approach calculates the total amount (from the begining until a selected date) and what I need is an approach to calculate the amount in a selected date (not the total).
I’ve been struggling with this for some days and until now I couldn’t find any solution.
Any suggestion?
Thank you very much in advance.
Hi Kecia
Because the changes are transactional, finding the sum to date (total amount from the beginning to the selected date) does give you the status on the selected date (or date range).
Lets say you have a contract with the following details/transactions:
Contract A, 1/1/2014, Pending, +1 (+1 as it enters the Pending state on this date)
Contract A, 10/1/2014, Pending, -1 (-1 as we’re backing out the Pending state on this date, to replace it with another record)
Contract A, 10/1/2014, Approved, +1 (+1 as we’re creating the new replacement record, which defines the new status from this date)
Contract A, 20/1/2014, Approved, -1 (-1 as we’re backing out the Approved state on this date, to replace it with another record)
Contract A, 20/1/2014, Failed, +1 (+1 as we’re creating the new replacement record, which defines the new status from this date)
So the contract was Pending, then Approved, then a mistake was corrected and it was changed to Failed.
So the Sum to Date for the entire contract is +1 (+1 -1 +1). So we have one contract.
If we filter this to just Approved, then we’ll just see the following records:
Contract A, 10/1/2014, Approved, +1
Contract A, 20/1/2014, Approved, -1
So the total contracts approved is 0, unless we then add a date filter to show only 15th January. In which case, the sum to date on 15th Jan would be +1, as it filters out the 2nd record.
Therefore we know there was 1 contract which was in the approved state on 15th Jan.
Does that make any more sense?
Thanks
Alex
Now I’ve got your point!
Thank you very much, Alex!!
Hi,
I’m new to mdx and I need something similar to what is in your approach.
I time dimension, created from generated Time table.
I have employers and deparments. Employers have StartDate and EndDate (null value is possible), DepartmentID.
Employers can go from one department to other, and go back.
I need to count how much different employers was in department on any given date.
I was trying to use your example, but I can’t get what I need.
Thanks in advance.
In this situation you’re looking to create a measure, employee count. As an MDX beginner, I would suggest that the best option for you would be to use data modelling instead of complex MDX, so create a new fact table to store this.
This could be a virtual fact table, i.e. generated using a view.
You’d create it using a query something like:
SELECT EmployeeKey, DepartmentKey, StartDate, ‘Joined’ AS EmployeeStatus, 1 AS EmployeeCount FROM DimEmployee
UNION ALL
SELECT EmployeeKey, DepartmentKey, EndDate, ‘Left’ AS EmployeeStatus, -1 AS EmployeeCount FROM DimEmployee WHERE EndDate IS NOT NULL
Then use this to create a new measure group in your cube.
Create a new dimension (EmployeeStatus) containing two values (Joined & Left).
You can then simply SUM() the EmployeeCount measure to find out the net increase or decrease in employee numbers.
Or filter it to Joined or Left to find out how many joined or left a department.
Or use SUM(null:[Time].CurrentMember) to find the total number of employees currently in a department.
etc.
I explain this approach to data modelling in more detail IN A SQLBits video:
http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes
Kind regards
Alex
Thanks! You saved my day.
It’s working fine.
Have a nice day,
Sandra
Hi, again,
Now we need to count average of work experience (of those employees at given date).
Count of employee in department on a particular day is working fine.
How this may be achieved?
Hi Alex,
I am new to MDx and tried various MDx solutions to achieve the below requirement but could not find suitable query as expected. below is my table structure
policyno startdate enddate
56356 20141210 20150215
89496 20150110 20150429
I need to calculate backlog of this policy numbers based on year, month wise etc.Below is the expected output
Month wise
2014- dec – 1 (56356)
2015- jan – 2(56356, 89496)
2015- feb- 1
2015- mar- 1
yearwise
2014- 1 (56356)
Similarly i need to calculate backlog based on date, week,month year. Could you please suggest do we need to create four separate measures or single measure is enough to handle this requirement. Could you please help to write calculated member of this.
Thanks in Advance
Regards
Murugan
Hi Alex,
I am new to MDx and tried various MDx solutions to achieve the below requirement but could not find suitable query as expected. below is my table structure
policyno startdate enddate
56356 20141210 20150215
89496 20150110 20150429
I need to caculate backlog of this policy numbers based on year, month wise etc.Below is the expected output
Monthwise
2014- dec – 1 (56356)
2015- jan – 2(56356, 89496)
2015- feb- 1
2015- mar- 1
yearwise
2014- 1 (56356)
Similarly I need to calculate backlog based on date, week,month year. Could you please suggest do we need to create four separate measures or single measure is enough to handle this requirement and suitable cacluated member for this.
Thanks in Advance
Murugan
Hi Alex,
I tried few of the above example but did not work for me.
I have a scenario, I need to find pending claims in a cube fact.
FACT DIMENSIONS ( date key relation with fact)
claim_id Received_Date Completed_Date
1 2011-01-01 3000-12-31
2 2011-01-01 2013-01-01
3 2014-01-01 3000-12-31
4 2015-01-01 2015-02-01
5 2011-01-01 2014-01-01
I need to find the count of pending claims.
any claim which has a Received_Date less than current date ( third date dimension not linked to fact)
and completed_date greater than current date( third date dimension not linked to fact)
is pending
for example if we are looking for pending in the above example for the DATE 2012-01-01 (third dimension date) then we have 3 pending count.
I have three date dimensions received_date (relation with fact using date_key)
completed_date (relation with fact using date_key)
Date (no relation with fact)
Date:
Date.Calendar
Year
Quarter
Month
Date
MDX — gives me erroe
WITH MEMBER PENDING
AS
AGGREGATE(
{NULL:LINKMEMBER([[Date].[Calendar].[Month].CURRENTMEMBER
,[Received Date].[Calendar].[Month])}
* {LINKMEMBER([End_Date].[Calendar].[Month].CURRENTMEMBER
, [Completed Date].[Calendar].[Month]):NULL}
, [Measures].[Count])
SELECT PENDING ON 0,
{[Date].[Calendar].[Year].&[3]&[2014]:[Date].[Calendar].[Month].&[6]&[2014]} ON 1
FROM [Legal_Svcs]
How to find the pending count using MDX ?
Any help will be greatly appreciated.
Hi Hama
Couple of things here:
1) It’s good practice to fully qualify calculated measures. I find it just helps the clarity of the MDX.
WITH MEMBER [Measures].[Pending]
2) In the SELECT part of the query, you’re trying to create a set containing different attributes; [Date].[Calendar].[Year] to [Date].[Calendar].[Month]
A set must contain members from the same attribute/hierarchy. So change it to
{[Date].[Calendar].[Month].&[3]&[2014]:[Date].[Calendar].[Month].&[6]&[2014]} ON 1
If that still doesn’t work, let me know the error message and I’ll take another look.
Regards
Alex
Alex,really its a helpful knowledge. I have a condition for distinct measure value . in a fact I have org_I’d. The primary measure is distinct org_I’d. While I am using the same approach its giving me only start date total distinct count value. Can you please suggest me during this scenario what we should do.
Thanks,
Sita
Hi Sitakanta
There’s no reason that I can think of why this approach shouldn’t work for a distinct count measure. Could you post more details including the exact MDX you’re using?
Thanks
Alex
Thanks Alex for your reply,
First of all I want to say itβs really a magnificent approach to avoid a complex MDX code.
My point is its working perfect for the below code.
AGGREGATE(
{NULL:LINKMEMBER([DATE].[YQMWD].CURRENTMEMBER
,[ OrgStartDate].[YQMWD])}
* {LINKMEMBER([DATE].[YQMWD].CURRENTMEMBER
, [OrgEndDate].[YQMWD]):NULL}
, [Measures].[DistinctOrganization])
But my question is how we can make a better data modelling so that my MDX will look like something below
CREATE MEMBER CURRENTCUBE.[Measures].[Active Organization on Specific Day] as
AGGREGATE({NULL:[DATE].[YQMWD].CURRENTMEMBER}
, [Measures].[Distinct OrgID])
[Measures].[Distinct OrgID] is the distinct count of Org_dwk from the fact table, and the approach for fact table is union all of Start Date Part and End date Part.
This code returns me all those distinct key relate to my date dimension but not in a range of start date and end date, as the measure is distinct count rather than Sum of rows, so there is no +1 and -1 logic apply here.
So how we can design a better data modelling for which distinct value measure can be found on a specific date range condition without using linked member.
Let me know if I am doing somewhere wrong
Thanks,
Sita
Hi Alex,
I’ve been using several role playing dimensions for my cube. One for “Sales Turnover”, “Sales date”, “Inventory turnover”, etc. There is a fact table that has Valid From and Valid To information and I am trying to have one other dimension “Time Period” to help users play around with one dim rather than 2. Found a solution on https://purplefrogsystems.com/2013/04/mdx-between-start-date-and-end-date/ (this site) and implemented it.
I am having a difficulty here : The SQL Profiler queries show me that the base query uses “Time Period” dimension but the overlaying querying uses “Inventory turnover” dimension and eventually results in NULL.
I realized that its the other role playing dimensions that are causing issues so:
1. I wrote a scope to make the calculated measure null by default
2. Then a scope for “Time Period” dim to calculate based on the valid from and valid to ranges as mentioned in the website above.
This doesnt help as well. I also played around with “IgnoreUnrelatedDimension” property but this doesnt change the query.
To debug even a little more, I removed the other role playing dimensions one by one and the MDX changed to take the one thats left. So I got rid of all role playing dims except “Time Period”, “Valid From”, “Valid To” (Which are all used in the calculation). Now the query was fine.
I would not be able to chuck the other dims(I’d have to bring them back). Please could you suggest me a solution to limit the calculated measure to the role playing dimension thats used in it(or only the ones used in dimension usage) ?
Hi Alex,
I am trying to calculate the count of the records for less than the year or month
EX: year 2015 i want to calculate count of records before 2015 ,
this the sql statement for it
select count(1) FROM [Court_Case_Fact] where Date_Dim_Key=20150101
This MDX work fine for year ,but when i drill down the month get wrong number
AGGREGATE(
{null:[Date Dim].[year month h].lag(1)}
* {linkmember([Date Dim].[year month h].LastChild.lag(1),[Date Dim 1].[year month h]):NULL}
, [Measures].[Court Case Fact Count]
)
.[year month h]: this name of the hierarchy include (year,month)
Please advice its very argent issue π
Thank you very much π
How can we get details on this newly created measure ?
Sorry, I don’t understand the question. What details do you need? Of which measure?
Hi Alex,
I’ve small Issue, My Query is like below.
select strtomember(@planstaging,constrained) on columns from(select strmember(@planyear,constrained) from [cube])
But two new filed(EffFromDate,EffTodate) is added in someother dimension, i.e [Account].
My Question is to @planYear is from planning dimension.
I need to Implement Below SQL query in MDX.
select * from [Table] join [Acount] on [Table].id =[Account].id
where (@planyear between datepart(yyyy,EffFromDate) and datepart(yyyy,EffTodate)).
Could you please help in MDX for B/w operator.
I need it in subquery, I need to write “@planyear ” level.
My Data is like
AccID EffFromDate EffTodate
123 1992-01-01 2005-12-31
123 2006-01-01 2008-12-31
123 2008-01-01 4000-12-31
Want to get 2007 records. So only “123 2006-01-01 2016-12-31” has to get for summation in Report and If I select year 2008,only 2008 record has fetch for summationin report.
please help me regardsing the same.
Thanks,
Ameer
Hi Ameer
My first comment here is that your cube is not modeled correctly. You shouldn’t just dump data in the cube as if it were a database, then try and hack MDX together to make something work. You’ll end up in a world of pain, with difficult to write and poor performing queries.
You can’t just translate SQL into MDX, it’s a totally different way of thinking about data modelling and query writing. [Please read the above post!]
So yes it is possible to write the query that you want, but I wouldn’t recommend it. I can’t give you the syntax without having access to the cube to understand its structure, but as a starting point look at the sample MDX in the above post, specifically the one that contains the LINKMEMBER function. You should be able to modify that to do what you need.
Regards
Alex
Hi Alex,
Thanks for your kind reply,
Yes, Here the problem is , data modeling was not defiened properly. However My problem has resolved .
I’ve another Query, While Utilizaing the Cube databse in SSRS,
My MDX Query already exist , but while adding new attributes from the same dimension. not able to reflect the all the fields at dataset level,even after ‘click ‘on ‘refresh fields’. But Query get Executing under the ‘Query Designer’ .
I just wanted, get the fields in at dataset level to make filter at dataset filter.
Please help on regarding this.
Thanks,
Ameer
Hi Ameer
The last thing you ever want to do with MDX is the equivalent of a “SELECT *”, this will kill your query performance, and is simply not what cubes are designed to do.
If you want to get all fields then use a SQL query instead.
Regards
Alex
Hi Alex,
I mentioned my entire Dataset in MDX past..that MDX is working fine…But here I just want to add two more attributes from same dimension in existing MDX quey.So I did for one report It is working fine. that attributes gets reflected in that(past) report. In same way while I’m trying to do, It was not reflecting at filters tab.
Could you help me regarding on this.
Thanks,
Ameer
Hi Alex,
I was created on select query given below which working fine.
select ({NULL:LINKMEMBER([Account].[From Year].CURRENTMEMBER,
[Account].[From Year]) }*
{LINKMEMBER([Account].[To Year].CurrentMEMBER ,
[Account].[To Year]):NULL},[Plan].[Year].&[2010]) on rows,
[Measures].[Number of Outputs] on columns
from [Cube_GF]
But here, I want to use PlanYear as filter for both FromYear and ToYear. I want utilize it in powerpivot to avoid duplicate and filter for only between FromYear and ToYear. Do I want know create measre inorder to do that then how.
Measure [Measures].[Number of Outputs] can change and as well planyear can change
Please give a best advice and help me on it.
Regards,
Ameer
Hi Ameer
LINKMEMBER allows you to map a member selection from one dimension onto the equivalent member of a different dimension. e.g. mapping the users selection from [Plan Year] to [From Year].
In the query above, you’re mapping from [From Year] to [From Year], so LINKMEMBER isn’t doing anything at all.
If you change it to LINKMEMBER([Plan].[Year].CURRENTMEMBER, [Account].[From Year]) then it should do what you want.
Regards
Alex
Hi ALex,
Thanks for your reply,
As said changed Query, Working fine. But,Thinking to do this,Measure purpose is to utilize in powerpivot to make the filter.
Suppose I’ve plan year and operation as filter, when I selected “2015 “(Plan Year) and “Division of External Relation”(Operation). The below attributes(Account ID ,AccountName, FromYear, ToYear and Countof MSRP), which are filtered out like based plan year and operation. But, My records like below for sample
AccountID AccountName FromYear ToYear COuntof MSRP
1234 abc 1992 2007 124 –>This record has to remove in powerpivot
1234 cde 2007 4000 124
Regards,
Ameer
Hi Ameer
If you’re using PowerPivot, then you should be using DAX not MDX?
PowerPivot is similar to the Tabular cube structure and syntax, nothing like multidimensional cubes.
Hi Alex,
I have scenario to implement asofdate based calculation to find out aging amount.
when
βDue Dateβ is less than βAsOfDateβ and βDue Dateβ is greater than (AsOfDate β 30)
Then
ScheduleAmount-transactionamount
Could you please help in resolving this.
Hi Radhika
Create an ‘As At Date’ date dimension, it should not have any relationship with the measure groups.
Then use LINKMEMBER to map the selected As At Date into the corresponding member in the Due Date dimension.
Do this twice, once using.lag(30) to get the due date 30 days ago.
Create a range of due dates between these two members, using ‘:’
Then SUM the measures over this set.
Something like:
SUM(
{LINKMEMBER([As At Date].[Date].CURRENTMEMBER.LAG(30),[Due Date].[Date])
:LINKMEMBER([As At Date].[Date].CURRENTMEMBER,[Due Date].[Date])}
, [Measures].[ScheduleAmount])
–
SUM(
{LINKMEMBER([As At Date].[Date].CURRENTMEMBER.LAG(30),[Due Date].[Date])
:LINKMEMBER([As At Date].[Date].CURRENTMEMBER,[Due Date].[Date])}
, [Measures].[TransactionAmount])
Regards
Alex
I dealt with a similar scenario last year, and I modeled my fact table view in SQL with a running date which was generated within a given time interval start-end. I got more data to pull during into the cube, but great querying performance on my attached dashboards and reports.
Hello All I have to same this using Mondrian MDX but I am getting error every time pelase provide me some clue to resolve this issue.
Mondrain MDX for records between date range where start and end date being separate columns
I want to search date between two columns of same dimension. Columns name is start date and end date. I tried every thing following query give this error : Mondrian Error:Internal error: Cannot deduce type of call to function ‘:’
MDX query :
WITH SET [~ROWS] AS Hierarchize({{[Location_Cluster.default].[All Location_Cluster.defaults]}, {[Location_Cluster.default].[Location_Cluster].Members}}) member [Measures].[QTY Percent] as ‘[Measures].[Total_Quantity]/([Measures].[Total_Quantity], [Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ member [Measures].[Revenue Percent] as ‘[Measures].[Total_Revenue]/([Measures].[Total_Revenue],[Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ member [Measures].[Margin Percent] as ‘[Measures].[Total_Margin]/([Measures].[Total_Margin], [Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ SELECT NON EMPTY {[Measures].[Sku_Count],[Measures].[Total_Quantity],[Measures].[QTY Percent],[Measures].[Total_Revenue],[Measures].[Revenue Percent],[Measures].[Total_Margin],[Measures].[Margin Percent]} ON COLUMNS, NON EMPTY [~ROWS] ON ROWS FROM [APCS_SALES_CUBE] Where ({[Date].[Start_Date].&[2017-01-01]: NULL} ,{NULL : [Date].[End_Date].&[2017-03-01]},[Cluster.Cluster_Id].[2],[Taxonomy.default].[Taxonomy_ID].[3],[Company.Company_Name].[Compnay_Name].[1])
What a simple solution to something I greatly struggled with. Thank you!!
Hi Alex,
I wonder if you can help, I have a similar problem where I want a user to select a date from the date dimension, but then have a dynamic set display the last 13 months based on their selection.
I have tried various options with tail or lag, etc, but the second I select a month level, my set is limited to just that month.
I am wondering if I need to introduce a role playing dim and use the linkmember to achieve this
I think your best/easiest option would be to have two date dimensions. Your primary date, linked directly to your measures, and then a secondary ‘Date Range’ or ‘Date Selection’ dimension containing just months.
Create a bridge table linking each ‘Date Range’ month with the relevant 13 months worth of dates in the main ‘Date’ dimension.
Then set up a many to many relationship between ‘Date Range’ and your measure group.
Then when the user selects a month in ‘Date Range’, the measure group will be filtered to the relevant 13 months, and you can add the normal ‘Date’ dimension onto the rows/columns to see it broken down by month.
Regards
Alex
This solution is really very good although I do experience slow performance with this MDX over a not particularly large dataset:
AGGREGATE({NULL:[DATE].[Calendar].CURRENTMEMBER}
, [Measures].[Project COUNT])
Is there anything I can do to improve this performance? I thought maybe include the total per date in the SQL fact table. Any thoughts would be appreciated.
This should perform quickly, if you’ve set up your aggregations correctly.
If you don’t have any aggregations then every query will be hitting your entire storage and it will be slow.
So set up aggregations, or store the running total in SQL and use SSAS’s Semi Additive measure functionality. Semi Additive measures will always give you better performance, but note that you do need an Enterprise licence to use this functionality.
Alex
Thanks for the quick reply Alex. I have used the wizard to design some aggregations but it has had little effect. It may be that my cube query that utilises the above measure is complex (there are a number of dimensions and filters). I will take a look at Semi-Additive measures. Thanks.
Hi Alex,
This solution works perfectly to find the value for any particular date. In my case also it works but the problem is that i am getting value 0 for the other dates where actually the value is null(in my explore DSV). I am using this query
min({NULL:[TIME].[CALENDER MONTH].currentmember}*{NULL:[TIME].[CALENDER WEEK].currentmember},[Measures].[STOCK]) ,,,, result in my excel pivot is like this.
StockID year quarter month date STOCK
111 2015 Q1 jan-mar 5
Q2 apr-jun 0
on 1st april it should be null but i am getting it as 0 and thereby it is showing the value 0 for all the following years. My point is if i select year 2016 then all the stockID which dont have any stocks should dissappear. Hope to get some sort of help from you. Thanks in advance
Hi Alex – love your SQL bits video! I was super excited to implement it until I ran into the following problem:
When an employee work record ends, I need that to count as “one day of work” if I slice by that day.
Likewise if an employee starts and ends on the same day, I need a count of one.
The “events” method sums these two events to zero. I tried adjusting the enddate by adding one day to it but that doesn’t work either should the employee begin with a new row on the day before the adjusted end date. When slicing by the day before the adjusted end date, the count will sum to zero.
Does anyone have ideas for this?
Or should I resort to two role playing date dimensions…
Thanks everybody for sharing!
What about if I want to count distinct customers (customerkey) that have active projects at any given date? For example a customer start 2 projects in the same month – how do I only count him once?
NICE
This is the exact two scenarios I needed help on and unfortunately your query is not working for me :-(. I dont get any error but whenever I try it hangs.
I am using a BI tool call Pyramid Analytics query builder. I tried and converted your query as below, please help me know if I am missing anything:
AGGREGATE( {NULL:[START DATE].[Calendar].[20130301]}
* {[END DATE].[Calendar].[20130301]:NULL}
, [Measures].[Project COUNT])
as
AGGREGATE({null:[Claim Opened Date].[Calendar Date].&[20200604]}*{[Claim Closed Date].[Calendar Date].&[20200605]:null},[Measures].[Claim Count])
Thanks….Amitesh
Hi Amitesh
Unfortunately Pyramid Analytics uses its own form of MDX which is very similar to SSAS but not exactly, and there are circumstances where the functionality differs.
My Pyramid server isn’t working so I don’t have any way of looking into this for you I’m afraid. You’d need to engage with your Pyramid technical partner to get further advice on this one.
Thanks
Alex
I should add that our advice is that if you’re using Pyramid you should use an Analysis Services cube underneath instead of the internal Pyramid IMDB. You can then add these calculations natively in MDX/DAX within the cube, without having to migrate them to Pyramid calculation logic. This is usually faster and requires less server resources.
Alex
Thank you Alex. I am connected Pyramid to SSAS OLAP cube and not using IMDB connection. Also if I use independently or separately i.e. Open Date as one calculation and Closed Date as another calculation then it works perfectly fine however if I try to club the result sets then I face the issue I mentioned. I have provided the details to Pyramid Support team and hoping for response.
Are you using this calculation in a Pyramid Formulate, or as a calculated measure in SSAS?
If in Pyramid, I’d try putting it directly into SSAS. If this works then it’s a Pyramid issue, if if doesn’t then it’s a cube issue.
Hi Alex,
In my fact table I have order date and shipping date and , I want to see how many items shipped at the order date. may you help me please
Hi Sara
You should really approach this in the data model, calculating the number of days between the two dates and linking this to a dimension. Then in the cube you can easily filter by different values to find those that shipped on or after the order date, and how by how many days were they delayed.
Regards
Alex
Hello Alex,
I need help with similar use case.
There are 3 dates , StartDate, CurrentSelectedDate, EndDate.
So, I need to find few things:
1) Days left : EndDate – CurrentSelectedDate = 2022-01-05 – 2022-01-02 = 3 days
2) Total days : StartDate – EndDate
3) Cost till the selected date : Sum of Cost from [StartDate] till the [CurrentSelectedDate].
StartDate, CurrentSelectedDate, EndDate. All these are in different date hierarchy, So, need help with the same.
Hi Subham
This is a very different scenario to that in this post. If you simply need to calculate the difference in days between two dates, then you can use the vba!Datediff() function in your measure calculation.
Alex