A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process.
Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, “sales” can be a particular subject.
Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.
Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept. For example, a transaction system may hold the most recent address of a customer, where a data warehouse can hold all addresses associated with a customer.
Non-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.
Different data warehousing systems have different structures. Some may have an ODS (operational data store), while some may have multiple data marts. Some may have a small number of data sources, while some may have dozens of data sources. In view of this, it is far more reasonable to present the different layers of data warehouse architecture rather than discussing the specifics of any one system.
In general, all data warehouse systems have the following layers:
The picture below shows the relationships among the different components of the data warehouse architecture
Data Source Layer
This represents the different data sources that feed data into the data warehouse. The data source can be of any format — plain text file, relational database, other types of database, Excel file, … can all act as a data source.
Many different types of data can be a data source:
- Operations — such as sales data, HR data, product data, inventory data, marketing data, systems data
- Web server logs with user browsing data.
- Internal market research data.
- Third-party data, such as census data, demographics data, or survey data.
All these data sources together form the Data Source Layer.
Data Extraction Layer
Data gets pulled from the data source into the data warehouse system. There is likely some minimal data cleansing, but there is unlikely any major data transformation.
This is where data sits prior to being scrubbed and transformed into a data warehouse / data mart. Having one common area makes it easier for subsequent data processing / integration.
This is where data gains its “intelligence”, as logic is applied to transform the data from a transactional nature to an analytical nature. This layer is also where data cleansing happens.
Data Storage Layer
This is where the transformed and cleansed data sit. Based on scope and functionality, 3 types of entities can be found here: data warehouse, data mart, and operational data store (ODS). In any given system, you may have just one of the three, two of the three, or all three types.
Data Logic Layer
This is where business rules are stored. Business rules stored here do not affect the underlying data transformation rules, but does affect what the report looks like.
Data Presentation Layer
This refers to the information that reaches the users. This can be in a form of a tabular / graphical report in a browser, an emailed report that gets automatically generated and sent every day, or an alert that warns users of exceptions. Example: Business Objects, Cognos, SSRS etc.
OLAP Tool Functionalities
OLAP tools are geared towards slicing and dicing of the data. As such, they require a strong metadata layer, as well as front-end flexibility.
3 types of OLAP tools are there:
MOLAP (Multidimensional OLAP) and ROLAP (Relational OLAP) and HOLAP (Hybrid OLAP)
- MOLAP: In this type of OLAP, a cube is aggregated from the relational data source (data warehouse). When user generates a report request, the MOLAP tool can generate the create quickly because all data is already pre-aggregated within the cube.
- ROLAP: In this type of OLAP, instead of pre-aggregating everything into a cube, the ROLAP engine essentially acts as a smart SQL generator. The ROLAP tool typically comes with a ‘Designer’ piece, where the data warehouse administrator can specify the relationship between the relational tables, as well as how dimensions, attributes, and hierarchies map to the underlying database tables.
- HOLAP: HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can “drill through” from the cube into the underlying relational data.
- Ability to leverage parallelism supplied by RDBMS and hardware: This would greatly increase the tool’s performance, and help loading the data into the cubes as quickly as possible.
- Performance: In addition to leveraging parallelism, the tool itself should be quick both in terms of loading the data into the cube and reading the data from the cube.
- Customization efforts: More and more, OLAP tools are used as an advanced reporting tool. This is because in many cases, especially for ROLAP implementations, OLAP tools often can be used as a reporting tool. In such cases, the ease of front-end customization becomes an important factor in the tool selection process.
- Security Features: Because OLAP tools are geared towards a number of users, making sure people see only what they are supposed to see is important. By and large, all established OLAP tools have a security layer that can interact with the common corporate login protocols. There are, however, cases where large corporations have developed their own user authentication mechanism and have a “single sign-on” policy. For these cases, having a seamless integration between the tool and the in-house authentication can require some work. I would recommend that you have the tool vendor team come in and make sure that the two are compatible.
- Metadata support: Because OLAP tools aggregates the data into the cube and sometimes serves as the front-end tool, it is essential that it works with the metadata strategy/tool you have selec
Popular Reporting Tools
What is Business Objects?
BUSINESSOBJECTS is an integrated query, reporting and analysis solution that allows you to access the data in your corporate databases directly from your desktop and present and to analyze this information.
SAP Business Objects BI (also known as BO or BOBJ) is a suite of front-end applications that allow business users to view, sort and analyze business intelligence data. The suite includes the following key applications.
Data Sources that Business Objects can connect:
· Relational databases (RDBMS), such as ORACLE, Microsoft SQL Server, Informix, IBM DB2, etc.
· Multidimensional (OLAP) databases, such as SAP, Microsoft OLAP Services, Hyperion Essbase, and ORACLE express, etc.
· Text files and spreadsheets
· Packaged applications such as SAP
· Virtually any data source using Microsoft Visual Basic for Applications (VBA) procedures.
Different versions are in Business Objects:
Business Objects 5.1
Business Objects 6.1
Business Objects 6.5
Business Objects XI
Business Objects Xi/R1
Business Objects XI/R2
Business Objects XI/R3 or 3.1 and BO 4.0 apart from these
Crystal Reports and
Business Objects Xcelcious are now under SAP family.
Architecture of Business Objects Xi/R3 or 3.1
Business Objects 3.1 has multi tire i.e. 5 tire architecture.
The client tier is the only part of the Business Objects Enterprise system that administrators and end users interact with directly. This tier is made up of the applications that enable people to administer, publish, and view reports and other objects
The application tier hosts the server-side components that process requests from the client tier as well as the components that communicate these requests to the appropriate server in the intelligence tier
The intelligence tier manages the Business Objects Enterprise system. It maintains all of the security information, sends requests to the appropriate servers, manages audit information, and stores report instances
The processing tier accesses the data and generates the reports. It is the only tier that interacts directly with the databases that contain the report data
The data tier is made up of the databases that contain the data used in the reports. Business Objects Enterprise supports a wide range of corporate databases
Business Objects integration with SAP
Main features of Business Objects Xi/R3 or 3.1 Universe.
· Desktop Intelligence
· Web Intelligence
· Central Management Console
· Report Conversion Tool
· Import Wizard
· Web Intellegence Rich Client
BO repository is a set of database tables which holds the metadata information and ITS A SEMANTIC LAYER WHICH STORES THE BO USERS N THEIR PRIVILEGES. It contains 3 domains.
· Security Domain
· Universe Domain
· Document Domain
The security domain has the user security info and other domains address.
Information about universes created and exported
Contains information about scripts or stored procedures, text files
A universe is a semantic layer that maps the data structure found in databases (tables, columns, etc.) in business terms.
A universe can represent any specific business area, process, or department. For example, a universe can relate to a department in a company such as marketing or accounting.
Universe can be created through universe designer:
SAP Business Objects Designer:
Universe Designer is a graphical design tool used to create the rich “semantic layer” (metadata) that makes SAP Business Objects products intuitive for non-technical users. It’s a graphical abstraction of the dimensional models that exist in your data warehouse. It auto-generates the Structured Query Language that interrogates the database tables so that business users do not need to understand this language or even know that it exists.
When the SAP Business Objects SAP Integration Kit is installed you can also create a universe based on your SAP Business Information Warehouse data using Universe Designer. This XML based universe will use the MDX language. You can combine the SAP data with your relational data (e.g. Teradata) by synchronization within Web Intelligence.
Note: If the universe has designed on top of the SAP Bex query you will not find the table structure in the structure pane.
Use SAP BW Query as data source:
SAP BW Queries are recommended data source as:
- Navigational Attributes
- Calculated Key Figures / Formulas
- Restricted Key Figures
- Custom Structures
Creation of the Universe on top of the BEX query (step by step):
· Open the Universe designer to create the universe
- Give the SAP system name
- Provide necessary credentials like User Name and Password
- Authentication should be SAP
· Create new connection between SAP and Business objects (Step by Step)
- Click New in the Universe Designer page.
- Specify the Universe name
- Give the description of the Universe
- Click New button to create the connection between SAP and business objects
W What is Connection?
- A connection is a link from the universe to the target database.
- The link is achieved using middleware (for example ODBC).
There are three different types of connections
Personal: Can only be used on the client.
- Shared : Can be used by more than one user to send queries to the target database from a shared
- Secured connection: This connection is used when you wish to distribute the completed universe to the user population via the repository.
Note: Here since we are distributing the universe in to repository will go for secured connection.
- Select SAP Client and click Next
- Fill all the details of the SAP system and Click Next
- Selectthe required query from SAP BI cubes.
- Click Next
- Click Finish
- Test the connection has been created
- Click Test
Note: If the server is responding then the given credentials of the SAP BI system is working else need to connect the other system
Then you can see the structure of the newly created universe
- Click File and save the Universe
After Save the Universe need to export it into repository.
- Click File and then Export
- Export universe into respective folder
Note: You can see how BW Metadata transfers in to OLAP Universe as well as naming conventions between two applications.
- Each Navigational attribute in the query results in a Class with Dimension and Detail objects
- Each Display attribute for the Characteristic maps to a Detail object of the corresponding Dimension objects in the universe
- The OLAP Universe includes all available Display attributes for the Characteristic and not only those Display attributes that are included in the SAP BW query
- All key figures in the Infocube or defined in the BW Query are included in the universe under a single object class called “Key Figures”
- Each key figure is represented with a measure object in a class Key Figures
- Most key figures are defined in BW with either a currency or a unit characteristic. For each key figure, Designer creates:
- A measure object with numeric format corresponding to the key figure without the unit
- A dimension object with character format that contains the unit or currency. For example, ‘USD’, ‘€’, ‘km’.
- A dimension object with character format that contains the key figure and the unit (formatted value) based on user preferences configured on the SAP server. For example, ‘200 USD’, ‘345 €’, ’25 km’.
- For Calculated Key Figures and Restricted Key Figures original calculations and restrictions are applied to the query , but are not exposed in the universe
Hierarchies from BW to OLAP universe
- Designer creates one hierarchical structure in the universe; Each level of a hierarchy will result in a dimension object.
- For characteristic Sales Person a hierarchy has been activated in SAP BW and therefore the OLAP Universe has generated the number of levels required to leverage the complete hierarchy in the universe