ECC Universe based on SAP Query
The BO 4.0 tools come with a number of really cool
connection options that allow you to connect to multiple sources to get data.
This blog documents my journey in using these tools and discovering which
scenarios work well with and also implementing best practices – so your
comments are welcome.
ECC connectivity can be established in BO by the following
steps:
- Create an InfoSet in ECC and publish in a group
(with a SAP query) - Create a project in the IDT using the SAP JCo
driver
In keeping with SAP tradition I have decided to use the
SFLIGHT demo tables. In some systems these tables may be empty and you will
have to populate them:
To populate the SFLIGHT tables, you can use the report SAPBC_DATA_GENERATOR.
First you must create a user group that will store the all
your InfoSet. In SQ03:
After creating the user group, now create the InfoSet in
SQ02:
Notice, I have selected the direct read option, and will
include all the fields in the table:
The InfoSet maintenance screen is presented to you, click on
generate and the InfoSet is created. Now the InfoSet has been maintained and
needs to be added to the group we created earlier. From SQ02, click on
Role/User Group Assignment:
Ensure the group is selected and click on save. The InfoSet
has been successfully created.
You have now created the infoset. This InfoSet is kind of a
data layer. You need to now create the SAP query that calls the InfoSet and
generates the data. To do this you go to transaction SQ01:
The group you were working in from SQ03 should be picked up
automatically:
If not, you change the group by SHIFT+F7 or rather, Edit
>> Other Group. When you click on create, your InfoSet will be picked up
immediately:
Select it and provide a description for the query:
And save. You still must identify the list of fields to be
used by the sap query, click on basic list and select all the fields we want to
work with:
Check it and save. If you test the query you have the
following results:
Now that the InfoSet is available with a query that utilises
it, we require a universe so we can create our report. To do this, start the
Information Design Tool, and create a new Project. Let’s call it
prjSFLIGHT_DEMO. In order to get a universe from SAP ECC data – we need to add
a SAP ECC connection. With this connection we then create the data foundation
which contains the InfoSet structure we are going to work with. In this example
our InfoSet result is just one table hence in our Business Layer we will only
limit our data to LH flights for next year (2013).
First let’s create the Relational connection that uses the
JCo connection for ECC, click on new and select the Relational DB connection:
Give the connection a nice descriptive name:
Scroll down to the SAP connectors and expand ECC 6 connector
and select the JCo connector:
Enter your system details as in your SAP login pad
If you perform a test then you will yield the following
results:
After creating the connection to the query, navigate to the “Show values” tab
and expand your Global tree and you will see your query available:
Our data foundation will be based on this query and the
connection created in the IDT to access it. This represents the actual infoset/sap query
structures as from ECC:
Insert the entire sap query as the basis:
After inserting the query – save.
Now that we have the data foundation – we need a semantic
layer to apply some filters, this is when we create a business layer based on
the above mentioned foundation layer
Now you have a connection, a data foundation and a business
layer. In the business layer you can then create the query. The query designed
in the IDT is similar to the BO 3.x tools:
Click on the button: 
Now create a query that filters that limits data to LH
flights for next year (2013):
You have now created the necessary objects. You must then
publish his as a connection. For this test we will publish it locally.
To publish a universe, right click on the business Layer
Publish >> To a local folder:
Click on Finish and it will publish it to your project
directory. Now you have a universe to work with. The next step will be to
actually use this universe, lets do that next time…