Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Business Scenario For a with Dynamic Dates.

Proposed Business Scenario For a with Dynamic Dates.

  • Monday, February 27, 2012 9:32 PM
     
     
    Sign In to Vote
    0

    Hi All,

    I am working on a DWH sales project. I have a fact table and mainly three dimensions. The business is based on renting office space.

    We buy property and lease them. One building can have mutiple suites and each suit has one tenant with Leasestartdate(lease starts) and  LeaseExpDate(Lease Ends).  My Questions is Can we calculate the count of the active leases base on the date. If a user want to run a report on Jan 1 2012  to get the counts as per that date? Please help me. 

    Regards

    Tinku

     

All Replies

  • Tuesday, February 28, 2012 2:51 AM
     
     Proposed
    Sign In to Vote
    0

    Tinku,

    Please check the below links

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/74af6305-5a70-4afa-a4fb-421c615c4406

    http://www.sqlservercentral.com/Forums/Topic15721-17-2.aspx

    http://sqlblog.com/blogs/mosha/archive/2007/06/01/counting-in-flight-events-in-mdx.aspx

    What all are suggesting is you need to take two sets,

    1) Leasestartdate < today

    2)  LeaseExpDate > today

    Then combine these 2 sets with a range operator.

    hope this helps

    Tinto

    • Proposed As Answer by Tinto James Tuesday, March 06, 2012 1:19 AM
    •  
     
  • Tuesday, February 28, 2012 2:58 PM
     
      Has Code
    Sign In to Vote
    0

    Tinkureddy -

    From your description, every fact row is related to a date dimension for the start date and end date of the lease.  So if done in SQL, the report query against the star schema could simply have a count of leases(rows in the fact table) and a WHERE clause with something like:

    WHERE '1/1/2012' between LeaseStartDate and LeaseEndDate

    where 1/1/2012 is your user-defined date and can be passed in by a parameter.

    Hope that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



     
  • Thursday, March 29, 2012 9:22 PM
     
     
    Sign In to Vote
    0

    Hi Tinto & Brent,

    Thanks a lot for the help. Sorry as well for my late reply on this. I was not able to work on this for quite some time and the issue still persists.

    Now,  I wanted a user to select a date either on the excel or cube browser to know how many leases are active on the particular filter date? 

    Example : If I have two Leases

     Lease1 : Starts on Jan 1st 2009 , Ends on Jan 31st 2012.

    Lease 2 : Starts on Jan 1st 2010, Ends on Dec 31 2012. 

    If a user filters on Jan 15th 2012, there should be two active leases. If he change the filter to Mar 1st 2012 there should be only one lease.

    I am sure this is straight forward using ssrs reports but how can we do this on the cube? 

    Thanks

    Tinku