The Semantic Layer allows for powerful analysis of any relational database based on time. The ability for administrators to create complex time-based measures and filters, means that business users can access the data they need to answer any adhoc questions. We covered these topics in more detail in the previous two posts.
In the past, organizations took weeks to answer such questions or instead these questions went unanswered because the reports took too long to build. That is no longer true.
Easiest of All
I use Quicken as part of tracking personal finance. Last Year, Last Quarter, Last Month are common date ranges that I like to use over and over, unfortunately BusinessObjects doesn’t provide this out of the box… but with a little creativity, you can build it yourself. It’s quite simple really and when you see how simple it is, you will be shocked.
The Recipe for Simplicity
The first thing we need to create is a database view with calculated dates which will change automatically from one day to the next. The table will contain three columns:
- Name of the Range
- Start Date
- End Date
With these three columns we can build any date we wish. Here are what the results look like when you view the table:
Note that each day, based on the associated view, these dates will change.
Here an excerpt of the logic in SQL Server to create the view:
SELECT 'Last 7 Days' AS Date_Range, CONVERT(smalldatetime,{fn curDATE()})-6
AS Begin_Date, CONVERT(smalldatetime, { fn curDATE() }) AS End_Date
FROM dbo.syscolumns
UNION
SELECT 'Today' AS Date_Range, CONVERT(smalldatetime,{fn curDATE()})
AS Begin_Date, CONVERT(smalldatetime, { fn curDATE() }) AS End_Date
FROM dbo.syscolumns
UNION ... etc.
Download the Full SQL Statement HERE.
Isn’t that AMAZING!?! Now all you need to do is create a BETWEEN-join from the date field on your fact table and the start and end dates within the Date Range Table. Here is the logic:
dbo.Current_Facts.sales_date between dbo.Date_Ranges.Begin_Date and dbo.Date_Ranges.End_Date
It should look something like this:
Now that I have this dynamic date range defined within my universe I can use it two ways:
- I can use it simplify the traditional prompts that typically include a ‘start date’ and ‘end date’ field. BusinessObjects provides optional parameters, so the report could allow the user choose which type of prompt they wish to use.
- Alternatively, I can use these fields for scheduled reports. Users can specify “Yesterday” in the prompt so that each day when the report runs, the prompt is automatically updated.
Additional Downloads
These series was inspired by some incredible work that was done by Richard Reynolds while he was working at SAP BusinessObjects. He has an amazing way of taking basic principles around technology applying them is ways that are so simple. The idea of the Begin/End Date connected to the fact table was his genius. Having the chance to work with Richard was a true highlight to my career at SAP BusinessObjects and I wish him the best in all his future ventures.
If you would like to download the entire Universe, which contains fabulous examples of what I’ve been showing during this three-part series you can download it here.
Download the Foodmart Universe (Foodmart.unv)
Download the Foodmart Database (SQL Server 2005 BAK File or MDF/LDF Files)
Enjoy,
«Good BI»
David, you mention that the different date range could be selected via a WebI prompt by stating:
I can use it simplify the traditional prompts that typically include a ‘start date’ and ‘end date’ field. BusinessObjects provides optional parameters, so the report could allow the user choose which type of prompt they wish to use.
I am new to BOE and WebI and am evaluating the product for use in our company.
Can you please elaborate how “optional parameters” work and how one could provide a pre-determined prompt (or prompts?) in a WebI document that allows the user to specify which date range is desired?
Thanks!
Hi David.
Your posting on dynamic data range states:
I can use it [to] simplify the traditional prompts that typically include a ‘start date’ and ‘end date’ field. BusinessObjects provides optional parameters, so the report could allow the user choose which type of prompt they wish to use.
I am new to BOE and WebI and am evaluating the product for use in our company.
Can you please elaborate how “optional parameters” work and how one could provide a pre-determined prompt (or prompts?) in a WebI document that allows the user to specify which date range is desired?
Thanks!
Hi David, I enjoyed your Time Series blog and I am evaluating it now. I am having a problem with getting the Food mart universe however.
Can you repost it?
Thanks!
I just tested it again with no problems. I was able to download it and open it with the BI 4.0 Universe Design Tool. What seems to be the problem?
Well, when I click on the hyperlink it just goes to another page that is indecipherable.
The database link works fine though.
It doesn’t seem to be a .zip file like the database.
It would appear your browser is attempting to open the .unv file. I will email the file to your gmail account.
Awesome!
Thanks David!
Hi David,
It is really a good article. But just one question, since we are using so many Time specific measures, for example, YTD, MTD, PYMTD, PYYTD, etc. When all these measures are used in the same query in WebI, the Select statement generated has multiple Case statements for each selection. So for large fact tables, wiil there be performance issues, since for each Case statement, it is spanning a different set of rows.?
Hi David, your article was very helpful. The universe helped me understand a lot of things. The WTD, MTD, YTD etc is calculated based on current date, but what if I have a corporate calendar and the Week starts from Wed-Tues? How do I write a code to get these metrics when the user enters the week number? Any help would be greatly appreciated. Thanks!
Hi..
I want to view last N year,quarter,month.
the following code gives me only month
datediff(m, dbo.Current_Facts.sales_date,getdate()) <= @Prompt('Months Ago','N',,mono,free) and dbo.Current_Facts.sales_date<=getdate()
i have same code for year and Quarter,in separate prompt.
But i want to view that,
if i enter n(4) number thenit should display last n(4) years,
if i enter last n(3) Quarter then it should display last n(Quarter)
like wise for month
by using single prompt.
Using CASE WHEN….THEN we can implement this ,but i am not able to do that.