Archive

Posts Tagged ‘Cool’

New 90-day free trial to SAP Visual Intelligence

September 11th, 2012 3 comments

It’s New!

It’s Cool!

It’s Wow!

It’s like two aspirin for that DeskI hangover!

… and it’s available FREE for 90 days!

11/12 UPDATE:  This was a limited time offer.  The free trial is now 30 days.

SAP Visual Intelligence is now available for download.  It provides the ability to connect to data sources such as MS Excel, Relational databases (SQL Server, Oracle, Sybase IQ, Teradata), Universe (unx) and HANA.

To enjoy your 90 day free trial go here:
http://www.sap.com/tryvisualintelligence

… and don’t forget to leave feedback for our product team so we can continue to make this new innovation product better!
https://cw.sdn.sap.com/cw/community/ideas/businessanalytics/sap_visual_intelligence

Enjoy!

 

 

«Good BI»

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»

 

 

FREE SAP BusinessObjects Tutorials and Training

February 20th, 2012 5 comments

New to the SAP BI Product Suite?  Looking for free training?  You’ve come to the right place.  SAP provides a large number of free videos and tutorials for all aspects of the BusinessObjects Business Intelligence Suite.

Here is a link for you to commit to memory…

http://www.sap.com/LearnBI

Here you will find several getting started tutorials for end-users.  It’s a great way to get familiar with some of the basic capabilities of the BI Suite, so if you’re helping new users learn about BusinessObjects, this is a great site to leverage.

For products that are not completely new, there are What’s New overview tutorials and individuals tutorials for new features.

There is also some comparison tutorials including:

Subscribing to New Content

If you are one of those folks who always wants to have a look at the latest and greatest learning content, I recommend you subscribe to the eLearning channel.

RSS Feed for eLearning:
http://www.sdn.sap.com/irj/servlet/prt/portal/prtmode/rss/prtroot/feedserver?rid=/webcontent/uuid/c0875d43-1c07-2b10-36a6-917f5d779a2c

Or if you prefer, you can follow the BI Suite eLearning on twitter:  @SAP_learn_BI

Enjoy.

«Good BI»

SAP Runs Crystal Reports

December 15th, 2011 8 comments

Say it loud, Say it proud

With the latest development of SAP Business Suite Enhancement Pak 5, SAP users can now enjoy the rich, flexible formatting functionality right from within the application.

ALVs Get An Upgrade

For many years, users have been limited in their ability to provide formatted reporting from an SAP ALV (ABAP List Viewer).  Users could do basic sorting, grouping and filter, but that was about it.  Now the output from an ALV can be pushed directly into a Crystal Report using the new Crystal Reports ALV Adapter.

This solution is currently available for ALV Grid (SAP GUI ALV) and for Web Dynpro ABAP ALV.  ALV List and ALV Classic are not supported.  In my case, I will be running an SAP GUI ALV.

Using the Crystal Reports ALV Adapter

Before installing the adapter you need to be running Microsoft .NET Framework 2.0 or above.  In addition, because I will be running ALVs run directly within the SAP GUI, I must have also installed SAP GUI 7.10 or above.

Downloading the Adapter

One of the hardest things about installing the adapter is finding in the Service Marketplace. Here is a screenshot of the location

Personally, I recommend searching for it.  Here is how I found it:

From the search screen, search for CR ADD-ON

Software Search Screen

Search for CR ADD-ON

You should see CR ADD-ON FOR BS APPS 1.0 come back in the search results.  This is what you want.

Search Results

Once you select CR ADD-ON FOR BS APPS 1.0, you will be able to download the ZIP archive file.

Service Marketplace Software Download

After extracting the application you will see the xSAPCRVAdpt.exe, which is what we will be installing.

Installing the Adapter

Begin the installation by double-clicking on the install file, xSAPCRVAdpt.exe.

Here we will see the Crystal Reports ALV Adapter ready to be installed.

After choosing Next, the installation will begin:

After a few minutes you should see the following message, which indicates the installation was successful.

Configuring the Adapter

The final step after installing the adapter is telling Business Suite to allow Crystal Reports to be used with ALVs.

We need to use tcode, SALV_GUI_CUST

Here we want to make sure we Allow Crystal Reports to be an option.

Next we need to use tcode, SALV_WD_CUST to go into the Web Dynpro Settings

Here we want to make sure we Allow Crystal Reports to be an option.

Testing the Adapter

You can use any ALV Grid (SAP GUI ALV) or Web Dynpro ABAP ALV for testing.  In my case, I’m going to use the t-code KSB1, Display Actual Cost Line Items for Cost Centers.

Note that at the end of the input screen, I select the layout for this report.  This is important because with Enhancement Pak 5, you can save Layouts that leverage Crystal Reports.

Here are the default results in an ALV Grid:

Pretty boring, eh?

Choose the Change Layout Icon

Under the View tab you can change the Preferred View to Crystal Reports.  This will cause the data to be sent to a Crystal Report using the SAP_GenericTemplate.rpt.

Now you can see the output in a Crystal Report.

Modifying the Crystal Report

We first need to get a copy of the report, so we will chose the option “Export Report”.

Export to Crystal Report

By selecting this option, I will be presented with a dialog box which will allow me to Save the Report to my hard drive.  At first, I found this button confusing because I expected it to work like it does in standard Crystal Reports and ask me if I wanted to export the report in PDF.  It left me wondering how I would go about exporting the results to PDF if I wanted to.  Hmm.

NOTE:  Once the report is exported, you can make changes to it using
Crystal Reports 2011, since at the time of this writing Crystal Reports
for Enterprise does not support direct data connectivity.  Also this template
was created in the pre-Crystal Reports 9 format; therefore you could modify
these reports with older versions of Crystal Reports.

When you open the report to modify it, you will see that the data is being pushed into the report via an ADO.NET (XML) database connector.  This means that this report cannot be refreshed from within Crystal Reports during the report modification process.

Rather than showing you the details about how to modify a Crystal Report, I will simply assume that after exporting the report, you have been able to make a number of changes to suit your needs and are ready to load those changes into the SAP List Viewer.

Select your update report from the dialog box:

After after being imported successfully:

… the new report layout will appear on the screen

Saving the Layout for Future Use

Now that we’ve got the new report loaded, we would like choose this view or layout.  SAP accommodates this.  All you need to do is save the layout under a new name by following the prompts in the Save Layout dialog:

In my case I named my new report layout /ZCRKSB1

After the layout has been saved, users can reference it from the Setting section of the original SAP List Viewer prompt screen.  Under the last section, Settings the user can change the default layout by selecting the layout of their choice.

Summary

For many years now, we’ve been talking to customers and partners about the value of embedded analytics.  Now we are finally beginning to see they rolled out in earnest.  Not only is Crystal Reports now embedded directly into SAP Business Suite, but SAP Dashboards (aka Xcelsius) are being provided out of the box for HR, Finance and other key areas.

The only drawback of embedded analytics today is the lack of built-in intelligence about how to navigate the data.  In the SAP List Viewer today, if you click on a column, the List Viewer is intelligent enough to drill to the associated supporting document.  But even with this limitation, there is still real value in better reporting from SAP Business Suite.

«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»