First, let us understand why Self-Service BI is so important for decision making within anorganization and how the Analytical Databases are making this possible

The increased need for every company to have more and faster data insights has led to new BI solutions which are allowing the people from an enterprise to analyze their business data and have all the information at a ‘mouse click’ distance.

With the latest Self-Service BI technologies, business users can easily develop or modify their own reports without tying up any developer resources. This way, the information is turned into intelligence much faster because the users can dig much deeper into the company data and they have more flexibility than with the static reports. This is much faster and the reports are more scalable than in a Traditional reporting system, where it is the developer job to maintain the static reports.

‍

The most common used Self-Service BI tools are:

·       PowerBI

·       Tableau

·       Qlik Sense

·       Sisense

·       Zoho

·       Microsoft Excel

‍

Unlike static reporting services, like SSRS, with these tools you have an (almost ) infinite possibility to customize your reports, using all the available data at its best power.

An example of a fully customizable PowerBI Report:

Now that we understood how the reporting evolved and how Self-Service BI is gaining more and more ground, the obvious question is how are they so fast? How are they getting all that data in a matter of seconds?

Even though the Self-Service BI Tools can use various kinds of data sources (like an OLTP System, an Excel Flat File, XML, Json etc.), if you want to have the best performance in terms of querying a large volume of data, the best way is to build an Analytical Database.

Analytical Databases are specialized databases which are optimized for best analytics performance. They manage and store big data like business, customer, and market data for Business Intelligence Analysis. They are also scalable and faster while querying high volume of data (like hundreds of millions of rows across agreat timespan).

Main goals for an Analytical Database:

    · Speeding up analytical queries through a specialized storage engine

    · Facilitates Self-service BI through the Semantic Layer

Main types of Analytical Databases:

Columnar database – The data is organized column-wise instead of row-wise. The performance is improved because the database engine needs to read fewer data need for processing any query.

‍

Data Warehouse Appliance – They are combining the hardware wand the BI Tools in an integrated platform which is easy to install and operate, to handle analytical workloads smoothly.

‍

In-Memory Database – The data is loaded from the source into the system memory in a non-relational and compressed format.

‍

Massively parallel processing (MPP ) Database – The data is spread across a cluster of servers which are sharing the workload to enable faster processing.

‍

OLAP (Online Analytical Processing)  Database – The data is stored in multidimensional “cubes”. These databases are allowing users to analyze information from multiple systems and provides them the capability to extract and view the business data from different points of view.

As an analyst often needs to group, join, and aggregate the data, his job can become a lot easier as OLAP data can be pre-aggregated and pre-calculated, making analysis faster.

‍

To better understand the OLAP Systems, in terms of functional aspects, we can identify a list of differences between it and an OLTP System:

‍

‍

OLTP vs OLAP in terms of Database Design:

‍

In the end, a small example of an end to end BI Architecture:

‍

Challenges:

    · Taking data from different systems, cleaning it, transforming it and conforming it (merge data from different systems/formats into one table )

    · Near real time data. The batch of ETLs executed through simultaneously running SQL Server Agent jobs.

    · Preserving history - Persistent Staging Area to keep historical data from OLTP Systems before modelling it into DW, because Business Rules are changing.

‍Conclusion:

Combining Self-Service BI Tools with the latest technologies in terms of Analytical Databases, you will obtain a powerful and flexible system that will provide you with immediate access to insights which can be used by key decision makers with confidence. By learning to use Self-Service BI Tools, you will not be dependent on developer teams and project delivery timelines every time you need to obtain a report, allowing you to focus on extracting the insights you need when you need it.

‍