You will find people defining BI, Business Intelligence in so many ways, and believe it or not all those different definitions do make sense for a specific need. In a nutshell, Business Intelligence is a collection of several elements that turn your data into useful information in a timely fashion which then helps make your decision based on data and facts and not just on your ‘Gut Feeling‘.
With ever-increasing data volume, companies can not afford to stick to traditional and manual ways of collecting data and then tabulating them. BI should help ease the burden by automating the process and providing a seamless way to analyze historical data with predictive modeling for the future.
However, there is no such thing as a ‘Standard BI Solution’. Every BI solution has its own unique attribute that depends on so many different things. The major factors that determine the size and the attributes of a BI solution are Business Requirements, Data Volume, Number of Users using the solution, Analysis and Reporting Requirements, Latency Requirements, Monitoring, and Support.
In general, all BI solutions are designed and developed to take data generated by various entities and transform them into an appropriate format for better analysis and reporting. Let us discuss some of the key elements of a BI solution.
Data Sources
Every business generates a lot of data. These data can be from standard application databases such as SQL Server, Oracle, DB2, Access, etc., Excel, proprietary data stores, or external sources like cloud-based services.
Make sure to audit all of those available data sources. The type of data stored, volume of data being generated, data types, values, duplicates, nulls, or any inconsistencies are important to help in choosing the right integration technologies.
ETL/ELT
The fancy acronym means Extract, Transform, and Load in different orders. The ETL process sits at the heart of any BI solution as it pumps data from point A to Point B within the solution. Key factors before designing your ETL system include the number of data sources, the volume of data to be transferred and how often, any transformation needed, data retention policies, etc.
Warehouse
This is where all the data from your business operations meet. It can be called a central data repository and all the reporting and analysis depend on it. Depending on the need and business requirements, you can have business data pushed into smaller chunks (Data Marts) first and then to one central repository (Warehouse), or all the data is pushed to Warehouse first and then Data Marts (Data Models) created off of Warehouse for functional reporting.
Regardless of the approach used in building Warehouse, it should be designed with reporting and analysis in mind. Data sources can be a normalized database but Warehouse should consider report performance and simplicity by flattening out the structures. There are two choices while designing a Warehouse.
- Star Schema (Recommended)
Simpler to work, with fewer tables to connect to in SQL joins but decreases storage efficiency by containing redundant values. The focus is on report performance and simplicity. - Snowflake
Normalized tables and thus increased storage efficiency with no data redundancy but a lot of joins in queries.
With modern cloud data warehouses being the new kid on the block, things have changed in how traditional warehouses were built. All kinds of data, structured, semi-structured, or unstructured data generated by disparate sources are dumped into a massively scalable data lake in their raw format and then further transformed and enriched for consumption via standard structured warehouse design or using direct queries.
Data Models
Analytical data models are built on top of the Warehouse. They offer tremendous capabilities that are not provided by a Warehouse including built-in KPIs, calculated measures, and data aggregation for faster analytical processing. Pre-aggregation of data within the data models provides vastly superior performance for analysis. There are two data models that can be built to serve your needs.
- Tabular Data Models – These models are simple to build and easy to understand as they consist of tables and relationships.
- Multidimensional Data Models – Considered advanced and matured and have advanced features like multiple facts and dimensions, KPIs, Data Mining, etc.
Reports and Analysis
The end result of any BI solution is to help in strategic decision-making through reporting and analysis. Reports can be delivered to the end users in different ways.
- Paginated Reports
A pixel-perfect report for end users that fits on a page(s) and can be printed. These are built by report writers and can be a subscription or run on demand. - Self-service Reports
With data models built in the backend, users will have the ability to create their own reports with no intervention from report writers or IT. This is essentially dragging and dropping and building your own flavor of a report from a pre-built dataset. These kinds of reports can be pretty dynamic and interactive so users can see data in many different ways.
On top of just visualizing reports, data need to be interpreted and analyzed to understand more about the state of any business.
- Interactive Analysis
The most common way to slice and dice the data using any given BI tool including Excel, SSAS, or Power BI. - Dashboards and Scorecards
To stream critical data points live and give the users the ability to drill down further to see the details. Important KPIs can be published as well. - Data Mining
With all historical data, using proper statistical algorithms and machine learning businesses can predict a few things in the future.
AI/ML
Artificial Intelligence (AI) and Machine Learning (ML) have exploded lately to provide further insights into business data and uncover critical insights. During data transformations, AI/ML models can be invoked to fulfill the need of a business.
