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.
Please check the below links
1) Leasestartdate < today
2) LeaseExpDate > today
Then combine these 2 sets with a range operator.
hope this helps
- Proposed as answer by Tinto James Tuesday, March 06, 2012 1:19 AM
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 //
- Edited by Brent Greenwood Thursday, March 01, 2012 2:59 PM
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?