Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX query using two date dimensions

Proposed MDX query using two date dimensions

  • Wednesday, February 20, 2013 4:52 AM
     
     
    Sign In to Vote
    0

    There are two date dimensons start date and end date date. End Date by deafult is 31 Dec 2060.

    I wish to know the number of employees on any specified date.

    The sql query for the same is:

    Select count(employees) from table1 where joining date < "User selected date" and transfer_leaving date > "User selected date"

    How do i write an MDX query for this SQL query?

    I am new to wiritng MDX queries. Kindly help.

    Thanks.





    • Edited by ANURD Friday, February 22, 2013 5:44 AM
    •  
     

All Replies

  • Monday, February 25, 2013 9:48 AM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    Hi ANURD,

    Please refer to the following Adventure Works sample query:

    with member Measures.CountOfMembers as
    COUNT(
     existing [Customer].[Customer].[Customer]
    )

    SELECT Measures.CountOfMembers on 0,

    [Customer].[Gender].members on 1

    from [Adventure Works]

    WHERE [Date].[Calendar].[Date].&[20010701]: [Date].[Calendar].[Date].&[20031231]

    Regards,

    Elvis Long
    TechNet Community Support

    • Proposed As Answer by TheSmilingDBA Monday, February 25, 2013 2:58 PM
    •  
     
  • Tuesday, February 26, 2013 6:26 AM
     
     
    Sign In to Vote
    0

    Hi Elvis,

    The above query does not consider a start date and end date as the customer table does not contain any similar date columns. 

    Below is the employee table query to count the number of employees in adventure works table during the year 2001:

    SELECT TOP 1000 [EmployeeKey]
          ,[FirstName]
          ,[HireDate]
          ,[StartDate]
          ,[EndDate]
      FROM [AdventureWorksDW2008].[dbo].[DimEmployee]
      where StartDate < '2001-12-31' and EndDate >= '2001-01-01' or EndDate is null

    The above query means any employee who has joined during the year 2001 (upto 31 dec) is counted in the list of 2001 employee.

    and any employee who has left the company during the year 2001 (any time after 01 Jan 2001 or later) or is still with the company (end date is null) is also counted in the list. I have not used distinct here in which case it will further reduce the employee count. I get a value of 294 employees during the year 2001 for the above query. How do i write an MDX query to get the same data?

    Am i missing any aspect in analysing the difference in MDX and SQL query count for employees?

    Thanks for your reply.

     
  • Tuesday, February 26, 2013 12:13 PM
     
     
    Sign In to Vote
    0

    this questions seems similar to

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3b97d416-d614-42ab-90cd-c6a3f8d00b06


     
  • Tuesday, February 26, 2013 12:26 PM
     
     
    Sign In to Vote
    0

    Yes  Krisztian. it is similar but there is no solution available over there.

     
  • Tuesday, February 26, 2013 12:35 PM
     
     
    Sign In to Vote
    0

    What would be the usage behind this? Do you need to create a report, or you want user to be able to browse it in excel using  pivot table? There are a couple of solutions depending on what do you need at the end.

    One solution is to create two dimension (role play) and then filter by them. Dim one would have all dates from begining to userselectdate-1 (including) and second dimension will filter from transferleavingdate to the end. So two calendar dimensions and combining those would give you what you want.

    A different solution would be using Many to Many relationship where you would need to create a M2M measure group with all combinations of joining and leaving date + dates between those. Then you would connect dimension "user date" with original fact table using M2M relationship over newly created measure group.

    MC

     
  • Tuesday, February 26, 2013 12:38 PM
     
     
    Sign In to Vote
    0
    sure there is a solution there, just it is not marked as such.
     
  • Tuesday, February 26, 2013 1:44 PM
     
     
    Sign In to Vote
    0

    Hi Marko,
    I need to use this in the report.
    I have a fact table having following columns:
    empid
    Start Date
    End Date

    The same empid will be repeated in the table as after the end date there will be again a start date for the same empid if the employee is transfered or promoted.

    Then i have a standared Date table which contain all the dates.

    My goal is to find out number of employees during any particular month/Qtr/Year.

    Example:
    empid 1000
    Start Date 20-Jan-2003
    End Date 20-Feb-2003
    Then this employee is counted in the list for months Jan and Feb 2003
    If Q1 2003 is selected then this employee will be counted only once in the Q1 2003 list.
     
    If he got transferred on 21 Feb 2003 then there will be another row added to the fact table.

    empid 1000
    Start Date 21-Feb-2003
    End Date NULL

    Then this employee is counted in the list for month of Mar 2003. As list is updated on 1st of every month.
    If Q1 2003 is selected then this employee will be counted only once in the Q1 2003 list.


    Thus ,the following SQL Query gives me the right result but i need help in preparing an MDX query for the same as i am new to MDX. 

    Select count(distinct empid) from fact table where Start Date <= "User selected date" and End Date  >= "User selected date"

    Thanks.

     

     

     
  • Tuesday, February 26, 2013 3:01 PM
     
     
    Sign In to Vote
    0

    I think this is more design issue then MDX. While I believe you can do something like that in pure MDX, its probably best to redesign. Simplest solution would be to have distinct count of employees, and have one row for each month.

    So instead of having one row fo empID 1000, it would be 2 rows (one for Jan and one for Feb) and a single date column.

    If this is not feasible, then i can suggest using M2M, where you would have a fact with columns: EmpID, DateFrom, Date, DateTo and it would be a view that expands all dates between from and to. Then you connect empid, DateFrom and DateTo dimension to that new fact. After that connect calendar dim to Date column, and at the last connect calendar dim to original fact using M2M relationship.

    After that you can browse and analyse numbers in cube (trends and such).


    MC

     
  • Wednesday, February 27, 2013 6:35 AM
     
     
    Sign In to Vote
    0

    Thanks for the reply.

    If i were to follow solution 1 then,  for empid 1000 if tranferred in Feb Month to another dept, then it would be 2 rows for Feb month. Can that situation be handled through a MDX query?

    If count for dept A is considered for Feb month then it will be counted once.

    If count for dept B is considered for Feb month then it will be counted once.

    If count for all depts is counted for Feb month then also the employee should be counted only once.

     

    I did not get your solution 2 approach, can you please elaborate on that. 

     
  • Wednesday, February 27, 2013 7:18 AM
     
     
    Sign In to Vote
    0

    If you implement distinct count, it will work as you described. 1 on A, 1 on B, 1 on total.

    For solution 2, check the many to many implementations, there is an example of that in AW solution. In my experience, the best way to understand M2M relationships in OLAP is to implement one :)

    MC