Archive for the ‘Semantic Layer’ Category

Supercharge Your Universe with Time-Series Analysis – Part 3

January 18th, 2010 10 comments

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.

Receipe for Success

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:

  1. Name of the Range
  2. Start Date
  3. 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:

Results from Date Range

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
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:

Date Range IS BETWEEN Fact Table

Now that I have this dynamic date range defined within my universe I can use it two ways:

  1. 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.
  2. 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)


«Good BI»

Supercharge Your Universe with Time-Series Analysis – Part 2

January 6th, 2010 4 comments

Using time is one of the most important ways to analyze information.  Each day, week, month and year is unique and can best be understood within it’s specific context.  In my last post, we showed how you can use the Semantic Layer to create time-based filters.  Today we are going to discuss Time Specific Measures.

Creating Time Specific Measures

Time Specific Measures are important because they allow us additional flexibility within WebIntelligence.  Instead of creating two queries, one for last years sales and one for this years sales, we can create one query and then use the definition within the measure objects to filter the records we need.  The following examples are for SQL Server:

YTD Sales

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<=
 getdate() then dbo.Current_Facts.store_sales else 0 end)

Previous YTD

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)

Now I can run a report and place these two measures on the report and the measures values for Current YTD and Previous YTD are already filtered accordingly.  What’s great about these measures is that they can be exceedingly complex within the universe, but they are easy for users to implement.  They don’t need to worry about accidentally filtering out too many records because the filtering within the case statement is non-exclusive.

Try this one on for size, Year to Date Sales v. Previous Year to Date Sales as a percent.

YTD Sales vs Prev YTD Sales %

case when sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)=0 then 0
else (sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
  datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<= getdate()
then dbo.Current_Facts.store_sales else 0 end)/sum(case when
 datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate())-1 and
then dbo.Current_Facts.store_sales else 0 end))-1 end

Now that’s some complicated SQL, and yet, because of the power of the semantic layer, all the end user consumer has to do is to drag the object onto the report.  The database will do the rest.

You can do the same on a monthly level.  Here is a list of time-based measures that I use all the time.

Time-based Measures

As you can imagine, it’s a great way to provide options to your users that they would never have dreamed possible before.  In my final post, part 3, I will provide a copy of the universe for you to download.

«Good BI»

Supercharge Your Universe with Time-Series Analysis – Part 1

December 29th, 2009 1 comment

To some degree or another, as we close out the year, we will all reflect on 2009 and what has been accomplished.  There are always things we might have done better, but looking at things over the time helps to put them in perspective– both in life and in business.    This is the first in a three part series.

Time Puts Everything in Perspective

When it comes to running reports and looking at your data are you leveraging time like you should?  Most organizations understand their performance month to month or year over year, but how much flexibility do you give your analysts to look at the information within the time segments they choose?  Some common time-series are:  YTD (Year to Date), MTD (Month to Date), WTD (Week to Date), but there is so much more.  As we prepare to close out 2009 and welcome in 2010, I challenge you to see if you are really using the power of time in your BI solution.

Insight Through Time

It’s important to always analyze your figures in comparison to other equivalent times, e.g. YTD with PYTD (Previous YTD).  The same with MTD and WTD, but how do you do it?  Within the Universe you can create filters.  These filters can be applied to any query.  Here is the SQL required.

YTD (Year to Date)

datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate()) and
dbo.Current_Facts.sales_date<= getdate()

PYTD (Previous Year to Date)

datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate())-1 and

Because you can run multiple queries using BusinessObjects you can simply create a query with the YTD filter and then “duplicate” the query and replace the YTD filter with the PYTD filter.  The dimensions of the two queries will be merged automatically.  Here is the query panel:

And here are the results:

… but sometimes you don’t want to have to make two queries.  Why not just get all results for the last two years and split things up within the measure?  Well, the power of the semantic layer allows you to do that too.  I’ll explain more about that in my next post, but before I do, let me share some additional common filters you should consider using:

Last 7 Days

dbo.Current_Facts.sales_date between convert (SMALLDATETIME,
{fn CURDATE()})-7 and convert (SMALLDATETIME, {fn CURDATE()})

Last n Days

dbo.Current_Facts.sales_date between getdate()-
@Prompt('Days Ago','N',,mono,free) and getdate()

Last n Months

datediff(m, dbo.Current_Facts.sales_date,getdate()) <=
@Prompt('Months Ago','N',,mono,free) and dbo.Current_Facts.sales_date<=getdate()

Last n Months Ago Ending When

datediff(m, dbo.Current_Facts.sales_date,@Prompt('Ending Date','D',,mono,free))
<= @Prompt('Months Ago','N',,mono,free) and
dbo.Current_Facts.sales_date<=@Prompt('Ending Date','D',,mono,free)

I will post a copy of my everything-you-never-thought-you-could-do-in-a-universe universe at the end of this series, but here is a list of some of the filters that exist in my universe.

Time-based filters Graphic

Common Time-base Filters

That’s a long list, eh?  I almost wish all these filters were already in the universe by default, but they aren’t, so don’t be afraid to add as many as you need.  Remember, when you add a filter to a query it affects the entire query.  Tomorrow we’ll talk about the technique of creating time specific measures…

«Good BI»

WebIntelligence Integration with SAP

September 14th, 2009 No comments

After attempting to move my blog off on Friday, I decided it was going to be more involved and I had a bit more testing to do before I was willing to make the final switch… but that’s another story.

So what do I have for you this week?  Well, I ran across a great demo by Ingo Hilgefort.  Ingo is a great resource.  He was one of the original folks that helped Crystal Decisions develop the SAP Integration Kit way back in the day and you can read more about him on his blog.  He’s what you would call the resident expert.  Last week a saw a demo that Ingo put together and I really liked it and wanted to share it here.

Watch the Demonstration

Excellent Overview of SAP BusinessObjects and WebIntelligence Integration:

You’ll see that he demonstrates some of the latest fold/unfold capabilities that come with BusinessObjects XI 3.1 SP2. Do recognize that is is NOT a replacement for the upcoming Pioneer release. I recently spoke with a customer who didn’t like the fact that the WebIntelligence document had to be in a specific layout/format in order to leverage fold/unfold. REMEMBER! WebIntelligence is an adhoc reporting solution that allows you to easily create formatted reports. It’s not targeted for power analysts. Pioneer is coming… in the meantime you’ll still need to use BEx Web Analyzer for that that power analyst group.

SAP BusinessObjects Roadmap for WebIntelligence

NOTE: for the latest roadmap updates on the SAP BusinessObjects Product Integration go to

Download the PPT

Here is a download of his powerpoint presentation in PDF format:

Note the slides that explain how the items for the BEx Query panel are translated to objects within the Universe.  It’s important to understand what objects will be exposed to the adhoc user as the build the report and which objects, e.g. characteristic restrictions, won’t be.

Make sure as well you get your hands on the SAP BusinessObjects Best Practices Guide as well.

«Good BI»

Sets Integrated into the Semantic Layer

August 18th, 2009 6 comments

On August 5, 2009, the SAP BusinessObjects Innovation Center announced a prototype for moving Sets Technology directly into the Semantic Layer.

Potential Future of Sets

Since the EOL announcement of BusinessObjects Performance Manager and two of the three analytic engines, Process Analysis and Predictive Analysis, there has been an ongoing question on the future of Set Analysis.  I have long been an advocate of the incredible power and importants of sets to help customers get a better understand of how data is changing under the covers.  If you have 100 more customers, that’s great… but how many did you lose and how many did you gain?  You may have lost 50 and actually added 150.  What about my “Gold” Customers?  How many new Gold Customers do I have?  Where did they go?  Did they become Silver members?

We have many customers who have successfully implemented Set Analysis; however the solution had not had significant R&D investment for some time and was tied to the legacy metrics engine of Performance Manager.  How could this technology be integrated into the new XI 3.X platform?

Sets and the Semantic Layer – I’m in Heaven

The Innovation Center has delivered a prototype which show Sets integrated directly into the Semantic Layer.  This prototype is a plug-in to the universe that allows you to create these “sets” , static or dynamic – all in the query panel.  Semantic layer sets lets customers leverage the complexity of  sets natively within that environment.  This is a huge step forward.

Check out the 15 minute video on the SDN website

You can see that this implementation can support all the classic calendar sets which will support of joiners, leavers, stayers, which are at the core of Set Analysis.  In this labs implementation there is support for both Visual Data and Visual Sets; however I hope this is expanded in the future to support Freehand SQL and an imported list.

I think this is a fantastic new innovation and needs to be added to the core product ASAP.  If you think so too, let your voice be heard:

I’ll keep you up-to-date with additional innovations around sets and may provide some additional posts on this topic if there’s interest.  Let me know.

«Good BI»