How does one decide whether to create one complex universe or multiple/simpler universes?

[cgwaters]

I'm still somewhat new to SAP universes and to the Information Design Tool.

My data sources are SQL Server databases and SAP R/3 tables.

To create my reports I'm using Web Intelligence. The data source for reports created in Web Intelligence can come from multiple universes.

In designing a data foundation and business layer, how does one decide whether to create one universe (say, a multisource data foundation, with connections to SQL Server and to SAP, including tables from both connections) or to create multiple universes? What are the advantages of one approach over the other?

[Former Member · Accepted solution]

Chris,

In addition to what Mani said, you can create any number of business layers over one data foundation, so if you can target and get the multi-source enabled data foundation which combines SQL and SAP then You can decide on whether to project one business layer out of data foundation or multiple depending on business needs.

Thanks

Mallik

[cgwaters · Accepted solution]

Thank you for the information, Mani and Mallik. I'm using one complex, multisource universe but am having difficulty joining tables between these multiple sources so as to obtain the desired results in Web Intelligence. Hence my question as to the advantages/disadvantages of using one complex universe compared to using multiple simpler universes.

[Former Member · Accepted solution]

Hi Chris,

Can you elaborate what is the challenge you have in Joining tables between the two sources at Data foundation layer?

Are you similar approach as listed in below document to build multi source universe?

http://scn.sap.com/docs/DOC-38685

There is always merge dimension option to save you to get the outer join operation at report query level, but its better to use the power of new IDT and club the different sources at universe layer itself.

Thanks

Mallik

[cgwaters · Accepted solution]

Thanks, Mallik. Since you asked...

I'm developing a WebI report that uses, as its data source, project management metric data from SQL Server and corresponding project-related time reporting data from SAP.

In my SQL Server data source, the project management data has the following tables: Departments, Teams, Projects, and People. Departments, Teams, and People are assigned to one or more projects. Of course, each table has many other columns that need to be included on the report.

In my SAP data source, each record in the time reporting table contains a Person ID, a Project ID, a work date, and an hour quantity -- representing the number of hours that each person reports to each project, by date.

So my data hierarchy is:

Projects (stored in a SQL Server table)

Departments (stored in a SQL Server table)

Teams (stored in a SQL Server table)

People (stored in a SQL Server table)

Time Reporting (stored in an SAP table)

The WebI report has a series of hierarchical table objects that match the database tables; i.e., projects, departments, teams, and people. The user interface for the report, however, is a bit of a challenge:

1. The user selects a row in the Departments table object; this updates the Teams table object so as to display the team(s) assigned to the selected department.
2. The user selects a row in the Teams table object; this updates the Projects table object so as to display the project(s) assigned to the selected team; in addition, columns from the Departments and Teams tables are included in the Projects table object.
   [Note that this part of the user interface object selection process differs from the order of the data hierarchy as described above.]
3. The user selects a row in the Projects table object; this updates the People table object to display the person/people assigned to the selected project; in addition, for each person, the detailed time reporting records (from SAP) are to be displayed.

I have all of the above present and properly joined in one multisource universe. Initially within WebI, I tried to combine all of this data into one query; however, having so many joins (most of which are many-to-many) caused incorrect results--especially the numeric columns that are used for aggregation. [Actually, I can get the content for some of the table objects to work, but not all of them.] Using a separate query for each table object, merged dimensions to bring the queries' results together, and linked elements, it appears as though I can obtain the desired results -- at least, with my efforts so far.

Whew! With the above scenario, is this a practical use of merged dimensions and do you still recommend against using them?

[In hindsight, the title of my thread should perhaps read "How does one decide whether to create one complex query (in one universe) or multiple/simpler queries (whether in one or more universes)?"]

[Former Member · Accepted solution]

Hi Chris,

I think its better to handle at universe layer itself since it offers more control, and also as it gets a little complex with merged dimensions and I think you should ensure you merge all the dimension objects of the data hierarchy mentioned above from both queries.

Thanks

Mallik

[cgwaters · Accepted solution]

Thanks, Mallik. I'm not sure I understand.

I have all of the joins in one multi-source data foundation and one corresponding business layer; however, my report has mutliple queries / report tables. I'm using linked elements and merged dimensions to keep the report tables in sync with one another. Are you suggesting that I try to figure out a way to keep the report tables in sync without using merged dimensions?

Also, you earlier suggested creating numerous business layers over one multi-source enabled data foundation. Obviously, this would result in the report being based on multiple universes and multiple queries. Wouldn't that scenario typically require the use of merged dimensions?

[former_member182521]

Chris,

All your requirements are purely based on the requirements from the Business.

Consider a situation where you have all your business transactions in ERP (SAP) and you have a separate software for your Human capital management (People soft).

Now if you have a requirement to create semantic layer for reporting on Employee related information , You will create a universe on top of Peoplesoft tables and do your reporting.

Similarly if you want to create a universe for reporting on Business transactions you will create report on top of universe on top of SAP data sources.

Again if you want to create a reporting structure only for Sales related information your Data foundation will have tables related only with Sales. Similarly for Marketing, Finance, Purchase etc.

finally If I need a consolidated view on both the data sources (SAP, Peoplesoft) I will go for a Multisources universe.

Hope this makes you to understand.

The following blog could help you to understand How content(Universe, reports, Users) are managed in BusinessObjects.

http://blogs.hexaware.com/business-objects-boogle/business-objects-content-management-planning/

Regards,

Mani

[cgwaters]

Thank you for the example and for the blog reference, Mani.