The universe structure

A universe is a semantic layer that maps a specific data area or several areas in the data warehouse or in any database such as SQL Server, DB2, Oracle, and many others. The universe uses a connection in order to connect to the database, and once it's set, users can navigate to the universe, build queries, and connect through the universe connection to the database, retrieving the most updated data according to the data update date in the database.

The universe represents the data in a lucid manner, so business users don't require database programming knowledge or an understanding of how the database is structured.

What the user does need to know is how the universe is structured: its main business purposes, what kind of queries can be built using it, and what kind of objects can be used to build his reports and where can he find them in the universe.

Traditionally, a universe maps a specific data subject such as Sales, Marketing, Human Resources, Logistics, Billing, and so on.

Each database can be mapped into a universe that we can also define as the business representation of the data in the tables.

It is possible to build a multisubject universe, but such a universe will usually create over-complexity and will lose the "specific subject" orientation as well as its audience. However, it is possible to combine queries from different universes, as we will learn in Chapter 8, Merging Data.

How to do it...

The basic universe structure is a set of objects that maps specific columns in the database. Each object is actually a mapped field in a database table.

The objects are grouped into logical folders and each folder corresponds to a specific data subject in the universe, such as time, sales figures, and products.

Every universe is based on the following four main types of objects:

• Dimension objects: These are used to present the basic type of data: customer name, city, employee number, and sales date. Dimension objects are also used to slice measure figures. Dimension objects can be numeric, character, or a date type. They are represented as a blue square.
• Attribute objects: These are used to present the same type of data as dimension objects but they are located as subobjects under dimension objects. Their primary function is to structure the universe, but they usually have a complementary meaning, for example, the code name of the product or the name of the dimension object. In other cases, we can say that when attribute objects are used for presenting data, they are not so meaningful without the dimension objects that are allocated above it; for example, the ZIP code as an attribute object won't tell us much unless we use the customer address as well. Attribute objects are represented as a blue rhombus with a green asterisk underneath.
• Measure objects: These are used for calculations. They are aggregative objects that represent facts: the number of products sold, revenue, income, tax, VAT, budget, and so on. Usually, a measure object will use an aggregation function such as sum, count average, maximum, and so on. Measure objects are represented as an orange line.
• Filter objects: These are predefined query conditions that are used for filtering the query results.

The following screenshot shows the structure of a universe:

Tip

By hovering over the object names, we can see the object description and type of data. This will help us get a better understanding of the object's purpose and reveal its type.

How it works...

The universe is simply a business map of the database. It can be described as the dictionary of a technical and complex structure that the business user or report builder can't navigate directly without technical knowledge.

When objects are created in the universe, they are named in widely used business lingo, which users already understand or will find easy to adapt to.

The universe reflects the structure of the tables and their relationships via joins and columns. The universe is created by an IT specialist, a Business Intelligence developer, or a person with the relevant development skills.

A universe is built for users because it enables them to create complex reports by themselves while keeping the data secure and the database protected from any changes and damage (the queries can be set to read-only mode).

There's more...

A universe is created as a result of a business request coming from one of the organization's departments or the business users' community, or as a part of the BI development plans.

The primary concerns that will define the scope of the universe will be:

• What kind of data is required?
• Which reports are required to be built?
• What business purposes will the universe serve?

The universe can evolve, be changed, and be adjusted over time so that it will keep up and correspond to the business user's requests.

See also

• For further information on how to use several queries and use Excel files as data providers, refer to Chapter 8, Merging Data