Three inter-related layers or processes in a robust BI analytics solution can include:
- a Symantic (meta-data) layer;
- OLAP cubes;
- Extract, Transform, Load (ETL) jobs.
Let’s take a closer look at each of these and how small changes within any one of them can affect your critical business reports at the top of your BI stack.
(1) Semantic layer – an abstracted ( meta-data ) layer that contains simplified business views mapped to underlying relational database models and data warehouses. This layer is a pre-requisite for some WSYWIG, drag and drop design reporting tools.
A semantic layer is a business representation of corporate data that helps non-technical end users access data using common business terms. The semantic layer is configured by a person who has knowledge of both the data store and the reporting needs of the business. The person creating the semantic layer chooses to expose appropriate fields in the data store as “business fields,” and to hide any fields that are not relevant. Each business field is given a friendly, meaningful name, and the business fields are organized in a way that will make sense to business users.
By using common business terms, rather than data language, to access, manipulate, and organize information, a semantic layer simplifies the complexity of business data. This is claimed to be core business intelligence (BI) technology that frees users from IT while ensuring correct results when creating and analysing their own reports & dashboards. In reality, however, IT staff are typically needed to create and maintain the semantic layer ( business view definitions ) which maps tables to classes and columns to objects.
(2) OLAP cubes – the arrangement of data into Cubes overcomes a limitation of relational databases, which are not well suited for near instantaneous analysis and display of large amounts of data. OLAP data is typically stored in a star schema or snowflake schema within a relational data warehouse or in a special-purpose data management system.
An OLAP cube is a term that typically refers to multi-dimensional array of data. OLAP is an acronym for online analytical processing, which is a computer-based technique of analyzing data to look for insights. A cube can be considered a multi-dimensional generalization of a two- or three-dimensional spreadsheet. Each cell of the cube holds a number that represents some measure of the business, such as sales, profits, expenses, budget and forecast. A Slice represents two-dimensional view of an OLAP Cube that arranges data in a grid, similar to a spreadsheet; a Slice functions much like a report or a query in an RDBMS in that it returns data based on a request for what to see
Although many report-writing tools exist for relational databases, these are slow when the whole database must be summarized, and present great difficulties when users wish to re-orient reports or analyses according to different, multidimensional perspectives, aka, Slices. The use of Cubes facilitate this kind of fast end-user interaction with data. Some reporting tools like Crystal Reports include the ability to define simple two-dimentional OLAP cubes for generating graphs for example.
(3) ETL Software – import clean data from virtually any source into permanent, structured relational database tables.
ALSO CALLED: Data Extraction Software, Database Extraction Software
DEFINITION: In managing databases, ETL refers to three separate functions combined into a single programming tool: Extract, Transform, Load
- First, the extract function reads data from a specified source database and extracts a desired subset of data.
- Next, the transform function works with the acquired data – using rules or lookup tables, or creating combinations with other data – to convert it to the desired state.
- Finally, the load function is used to write the resulting data (either all of the subset or just the changes) to a target database, which may or may not previously exist.
ETL can be used to acquire a temporary subset of data from virtually any file for reports or other purposes, or a more permanent data set may be acquired for other purposes such as: the population of a data mart or data warehouse; conversion from one database type to another; and the migration of data from one database or platform to another.
A change to the underlying source database, or at any one of these BI layers can affect the hundreds or in some organizations, thousands of reports that are dependent on the underlying data model. Something as simple as renaming a field or variable name can essentially break the chain, and stop reports from working or producing accurate results the business depends on.
Now imagine a change impact analysis tool that can search your entire BI process flow to show you all affected references throughout your Business Intelligence stack.
Find it EZ – Software change made simple. ™ Download your free trial today.
You must log in to post a comment.