Archive

Posts Tagged ‘WebIntelligence’

Merging SAP and non-SAP data: Overcoming Hierarchies

May 29th, 2012 12 comments

Even though SAP provides connectivity to both SAP BW and relational data, combining these data sets into a single report can be tricky.  There are lots of ways to do it:

  1. A Single, Federated Query combining an SAP DSO and Relational data at the universe tier.
  2. A Single, Federated Query combining an SAP Infocube Relational Schema + Relational data at the universe tier.
  3. Two non-Federated Queries, one BW Query and one Universe query, merging the data at the report (WebI) tier.

The first method has long been supported but you loose all the value-added elements of the Infocube.  Most customer I work with want to connect to the BEx Queries that run against the Infocubes.

The second method is supported, but not recommended.  The semantic layer via the federation engine is able to interrogate the Infoprovider and expose it as a relational star schema.  Here are a list of some of the elements you lose:

  • BW Hierarchies
  • Restricted and Calculated Key Figures
  • BEx Queries
  • BW Variables
  • Currency & Unit Conversion
  • Exceptions, Conditions

>> I always use the third approach, but there’s one big problem.  Hierarchies have remained a huge challenge.  Until Now.

SAP Hierarchies

Many customers have SAP Hierarchies that they have developed that they want to combine with non-SAP data.  The problem is that when you merge the data at the report level, the roll-ups no longer work.  Today I’ve discovered that there is a “secret formula” that solves this issue and I think you’ll like what you see.

Step by Step

Let me walk you through an example step-by-step.

The first thing I have to do is create an Infocube with a hierarchy and create a BEx Query on top of the InfoCube.  In my case I have a characteristic called District, which contains a hierarchy called, Regional.  In my example the hierarchy is a simple, two level hierarchy.

Infocube 2-level Hierarchy

I then created a BEx Query for that leverages this hierarchy.

BEx Query which references a hierarchy

Next I created an OLAP connection within the CMC to the associated BEx Query.

Create OLAP Connection in the CMC

Once the connection to the SAP data is complete, I created a data set in SQL Server which matches all the base level members.  Here is a view of the data:

View of SQL Server data which matches my SAP data

I then created and published a universe that connects to the SQL Server data and returns the correct results.

Universe Connecting to Data in SQL Server

Now it’s time to create a WebIntelligence Report.  First I’m going to connect to the BEx Query and pull in Regional (Hierarchy) as well as the measures Actual Amount and Budgeted Amount.

Next, I accessed the Data Access > Data Providers > New data provider and selected Universe.  This allowed me to connect to the SQL Server universe and added the elements from SQL Server and run the second query.

Query Panel Connecting To SQL Server (non-SAP) data

Now I had all the data I need from the two queries.  One query is pulling data from BW with a hierarchy and the other query is pulling data from SQL Server.  Here is what the data providers looked like once both queries have been added to the report:

The Available Objects from Query 1 & Query 2

Now I simply needed to merge the two data elements Regional together.  All you have to do is highlight to two values and choose Data Access > Data Objects > Merge.

From here select the Regional objects.

Merge Regional Objects

Once that was done, the key figures from the SAP and Relational data can be combined together is the same table and this is what the results look like when put into a table.  The first 3 columns come from SAP and the last two columns come from SQL Server:

Data Is Merged But Calculations Do Not Roll Up

You see that the elements all match and appear together at the lowest level, but the parent values are not rolling up.  This is because WebI is also looking for the values Metropolitan and Rural in the dataset.  If I was not doing simple addition, I would have no choice but to add these into the original data in SQL Server.  In my case I am doing a simple roll-up SUM(), therefore I can fix this with a new formula!

Magic Formula

Here is the magic formula:

=If( [].[].IsLeaf =1 )
Then [<relational_measure_name>]
Else Sum( [<relational_measure_name>] ; Descendants([<olap_webi_query_name>].[<olap_hierarchy_name>];1;Self_After) )

I say “magic” because when I first saw this formula, I was blown away.  Now that WebIntelligence contains a number of native OLAP functions, these types of OLAP-centric calculations are possible.

Leverage This New Formula

In my case I had two key figures which were coming from SQL Server, so these values would need to be replaced with formulas that included this new OLAP roll-up math.

I created a formula for the Citc Credits first:

New Citc OLAP Formula

I did the same for Turbine Totals and ended up with the following Available Objects.  You can see my two new Variables: Citc Credit Totals and Turbines Total.

Available Objects with New Variables for OLAP Rollups

Once I replaced the SQL Server key figures with the new OLAP formulas, I was able to see the magic in action.

WebI Report With Correct Hierarchical Rollups

 Conclusion

It’s not perfect but it works… and it works very well.  Ideally it would be best if we could merge the data at the semantic layer so that for each report the user didn’t have to have to create a series of formulas to solve this problem… however until a better semantic layer solution exists that supports hierarchies like this, I’m going to be a big fan of this new workaround.

«Good BI»

 

 

Fixing Permissions: Am I Allowed To Do That?

May 18th, 2012 16 comments

One of my least favorite topics is licensing.

It seems like every year as corporate strategies change and as customers push for simplified licensing, SAP comes out with a new way to sell it’s products.  If you’ve been around a while you’ll recognize some of these:

  • BusinessObjects Enterprise
  • BusinessObjects Enterprise with Interactive Viewing
  • BusinessObjects Enteprise Premium
  • BusinessObjects Edge
  • BusinessObjects Edge Premium
  • Premium Package, Visualization & Reporting
  • Premium Package, Query Reporting & Analysis
  • BI Package
  • BI Suite

What I’m not going to do is explain how SAP licenses it’s products.  That’s what the sales guy is for.

What I do want to explain is how the functionality within the licensing model breaks down and how it impacts you as the BusinessObjects administrator.

Licensed User Type

With BusinessObjects, licensing was pretty straight-forward.  You could either buy:

  • CPUs – unlimited active users running on a limited number of cpus (cores typically counted as 1/2).
  • Named users – one license per named system user on unlimited hardware.
  • Concurrent users – a delightful combination of unlimited users set up on unlimited hardware, but only a given number of those users could log in simultaneously.  (This license went away for a while and now it’s back for specific situations.  Typically customers can figure on 10:1 rate of potential users to concurrent users).

When SAP acquired BusinessObjects all that changed.  SAP introduced the idea of licensing both the ‘platform’ as well as the user count.

SAP customers who use BusinessObjects must purchase licenses for the platform (cpu or named users) as well as a named user license for every person who accesses the BusinessObjects environment.  The only problem is that there is typically a big difference between the capabilities a power user/administrator needs and a casual user who only accesses the system occasionally.

In order to provide try and keep licensing simple, while at the same time providing a differentiation between power users and casual users, SAP created the concept of a licensed user type.

There are two:

  1. Expert users – also know as Business Expert or Business Analyst Users
  2. Standard users – also known as Business Information, BI Viewer or Viewer Users.

What Is My User Allowed To Do

I’ve created a table which breaks down the each product and explains what the user is able to do within that product.  Note that an expert user can do everything a standard user can, plus the additional items listed in the Expert User Rights column (which is the equivalent of ‘full access”).

SAP BusinessObjects Platform

Product Standard User Rights Expert User Rights
BI Platform Access platform services and view environment Modify BI Platform environment, security definition and make modifications. Access and administer (profiles, configuration) platform services.
Live Office View integration with Microsoft Office for viewing reports, dashboards Design and edit queries
BI Workspaces Customize, view personalized reports, dashboards
BI Widgets Customize, view personalized BI widgets Design, author, and create BI widgets
BI Launchpad Personalize your user profile
SAP BusinessObjects Integration  (Oracle, PeopleSoft, JD Edwards) View data from business applications in a report, analysis or dashboard (indirect access)

SAP BusinessObjects Tools

Product Standard User Rights Expert User Rights
Crystal Reports View (refresh and schedule) reports Schedule, author, edit, and update reports
WebIntelligence View (refresh, schedule, export, zoom, sort, search, filter, drill, apply basic formatting) a predefined report Schedule, author, edit, and update analyses and queries
Dashboard View (refresh and interact) dashboard models Schedule, author, edit, and update models
Analysis for OLAP Customize, view personalized BI widgets Design, author, and create BI widgets
Explorer View (search, view and navigate) data sets  Schedule, author, edit, and update Infospaces
BI Mobile View (refresh and interact) documents Author, edit and update analyses and queries
Note that this information can change at any time,
so always check with your SAP sales team to make
sure you understand the capabilities of each license type.

Controlling Access

As administrators, it’s very important to make sure standard users are not using functionality of the expert users.  The good news, bad news is that in most cases this type of user differentiation is a paper license and not controlled by the license key.  This is good because it means that as SAP’s licensing model changes, you don’t have to run out and get new license key or download a new service pack.  The bad news is, you need to setup BusinessObjects security so that standard users don’t accidently extend the reach of what they are licensed to do.

Setting restrictions based on the tables above is pretty straight-forward for most of the application permissions.  In most cases access should be set to “View” and that takes care of it.  The one except is WebIntelligence.

Controlling WebIntelligence

In order to be a standard user, you should explicitly deny specific permissions within the WebIntelligence application.  To access these permissions from the CMC home page, choose Applications.  From the list of available Applications, scroll down the list to Web Intelligence.

Select Web Intelligence and select Manage > Properties from the menu (or user the right-click menu).  Next choose User Security from the navigation pane.

Add a new Principal (if one is not already there) or highlight an existing principal user or group.  We want to Assign Security and restrict WebI access.  The default access right should be View.  We will then want to modify this by clicking the Advanced tab and selecting Add/Remove Rights.  From the left navigation pane, select Application > Web Intelligence.

Manage WebIntelligence Application Security - Scroll down to see ALL the permissions

We are now going to explicitly deny the following rights:

  • Desktop Interface – * all permissions *
  • Documents – enable creation
  • Documents – enable publish and manage content as web service
  • Interfaces – enable Rich Internet Application
  • Interfaces – enable web query panel
  • Query script – enable editing (SQL , MDX…)
  • Query script – enable viewing (SQL , MDX…)
  • Reporting – create and edit breaks
  • Reporting – create and edit conditional formatting rules
  • Reporting – create and edit input controls
  • Reporting – create and edit predefined calculations
  • Reporting – create formulas and variables
  • Reporting – insert and remove reports, tables, charts and cells

If you look under the permissions for WebIntelligence, you will now see the only permission you can modify on a per report basis is Edit Query, which no longer matters because here we’ve explicitly denied Query script – enable editing (SQL, MDX…) within the WebI application.

Once the changes are complete, you should be able to access existing WebIntelligence documents and see that a number of features are now disabled (grayed out).

Standard User Access WebIntelligence With Restricted Permissions

If you have additional questions about permissions and how they might have changed from older versions of BusinessObjects, you can access the information here:
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/f0543a8d-9155-2f10-c5a7-8d15688a5faa&overridelayout=true

I hope this blog has been useful.  Please do NOT leave any comments below about licensing.  I won’t be able answer these.

What I did want to answer is any questions about the difference between the different user types and show you how to modify the security within BusinessObjects to make sure you are in compliance with your license agreement.  Hopefully it’s mission accomplished.

«Good BI»

Histograms in WebIntelligence

September 27th, 2011 1 comment

Every once and a while I run across a reporting technique that makes me say:  Wow – that was really clever.

In going through the new charting in BusinessObjects v4.0, I noticed that there was no histogram available.  After doing a Google search I found a great article by Alastair Gulland.

Alastair uses  a combination of straight-forward formulas and out-of-the-box thinking.  A brilliant combination.  He also introduces a way of making the histogram dynamic by using a Input Control.  Very nice.  It’s definitely worth checking out.

You will find the full blog post here:
http://www.gulland.com/wp/?p=662

Click on the image to see the finished report with Histogram and Input Control

«Good BI»

WebI Date Conversion

March 18th, 2011 4 comments

Have you ever been in a situation where you wanted to display a date on a report but the date was in date/time format?

I run into this all the time when dealing with detailed data from transactions with date time stamps.

The best way I’ve found to do it is by converting the value from DateTime to Text and back to Date.

Convert Text to Date

Believe it or not, it’s not too bad after you figure it out.  It did take me a few tries so I thought I’d share my solution with you.

=ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy")

Let’s break it down so we can understand the individual components.

In this case, the field I am formatting is a date/time prompt and I will reference it by the name “As Of Date”.

I will need to perform 3 steps in able to convert it:

  1. Use the Pos function and find the space between the date & time, e.g. 1/1/2011 15:03:03 returns the position value of 9.
  2. Next use the Left function and grab everything to the left of the space – 1.  e.g. 1/1/2011 15:03:03 returns the text “1/1/2011”.
  3. ToDate now takes the date text and converts it to a date.  e.g. 1/1/2011 becomes a date type field 2011-01-01

Display As Text

Optionally now that I have the value in a date-field format, I can now convert it back to a formatted date using the FormatDate function.

=FormatDate(ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy");"yyyy-MM")

What about you?

What’s your favorite WebI syntax for converting data types in WebI?

Do you have any conversions that are giving you trouble?

«Good BI»

Date Conversion Made Easy…

October 15th, 2010 3 comments

Have you ever been using SAP BusinessObjects WebIntelligence and wanted to turn a prompt from a date time into a date?

I do a lot of demos using Microsoft SQL Server and by default Microsoft SQL Server saves everything as a date & time.  So what if I don’t want the time?  Simply convert the datetime into a date within the semantic layer.

Ignoring the time 12:00:00

There are many solutions, including many which rely on some form of string parsing, but why make it more complicated than it has to be?  This is what I like to use:

CONVERT(DATETIME, CONVERT(INT, GETDATE()))

This is the perfect solution if all the data is stored as 1/1/2010 12:00:00, wherein the time element is 0.

Ignoring time and rounding down

If indeed there is a time and it’s important to round down, e.g. 1/1/2010 11:59pm should be rounded down to 1/1/2010, then in this case I use:

CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

This is great when accessing call center data and I need to group calls around a specific day, but the time is still extremely relevant.

Anyone else have any commonly used tips or tricks within the semantic layer?  Post it in the comments below!

«Good BI»