All about Microsoft BI – Options, Objectives and licensing.
At Acuvate we have been having discussions with many customers about the Microsoft BI system. There are two main aspects which comes in this conversation.
1. I thought SQL Server is a Database, Is the ETL Component – SSIS a separate product which I need to buy? What exactly is the MS BI Landscape and Tools?
2. There are many reporting tools SQL Server Reporting Services, Power BI, Power Pivot, Data Zen etc. When do I use what and how is it licensed.
The way this question is best answered is through a System Architecture View. Let us now try and understand the system view.
Microsoft BI Ecosystem
The diagram mentioned above constitutes the entire Microsoft BI eco system. Some of these tools are part of the SQL Server standard and some of these come with the enterprise edition of SQL Server and some are separate components altogether.
The eco systems can be divided into four separate layers.
1. ETL Layer
2. Warehouse Layer
3. OLAP Layer
4. Reporting Layer
Let me explain these layers in a little more detail.
1. ETL Layer
This is the layer which extracts the data from the LOB systems in the organization like the ERP, CRM, SAP, Oracle Fusion, Dynamics, and Salesforce etc. and transforms the data by applying business rules if any and loading the data into the data warehouse and the SQL Component which covers this is called SQL Server Integration Services.
This is the layer where we store data in a dimensional mode, which means data is split into dimensions and fact tables (Star or Snowflake schema based on business rules). The SQL Component which covers this is called SQL Relational Database Engine.
This is the analytics layer which pre-aggregates the data and stores it for faster fetching of data. The SQL component which covers this are called SQL Server Analysis Services and comes in two modes a Tabular Model which is an In Memory module with data loaded in Memory for faster access and the other is a multidimensional OLAP (MOLAP) model where the data is stored in disk.
The Reporting layer which consists of components like Power BI, Data Zen, SQL Server Reporting Services, PowerPivot\View on Excel can directly read from the source systems.
When do I get what and how will I get charged for is a question which comes to our mind. There are two main components to be bought.
- SQL Server
SQL Server has a core based licensing and let us say we buy 16 Cores of SQL License. I can install this on a single box with all the SQL components which are mentioned above.i. SQL Database Relational Engine
ii. SQL Server Integration Services
iii. SQL Server Analysis Services.
iv. SQL Server Reporting Services
v. Datazen for SQL 2014 and below (It is integrated into SQL Server Reporting Services from 2016 onwards )There is no additional licensing for each component, when you buy SQL Server License, you have the entire stack at your disposal.Another alternative to it could be to deploy couple of components on one server with 8 crores. Let us say the relational database engine, Integration Services are on Server A, SQL Server Analysis Services and Reporting Services on another server, let us call it Server B which has 8 cores which is typically called the scale out architecture in case of intensive ETL and Reporting requirements.
It is also possible that ETL workload is so huge when in Terabytes that 16 Cores is utilized by one server with the relational database engine and you will need to additionally buy more SQL Server Core Licenses for other components such as SSIS, SSAS, SSRS.
- Power BI
Power BI is a cloud based service which is more used for Self Service BI with powerful features such as Natural Language Query which can get data from various different sources like Oracle, SAP BW, SAP Hana, Facebook etc.. It has got its own Desktop component which is free. The data refresh can be scheduled using the Enterprise Gateway component or the personal gateway. The Licensing is user based and you can buy this as an Individual component or bundled with 0365 upward of E4 License.Now the question arises if I have Power BI which can connect to any source why do I need SQL Server? The answer is that when you connect to the core systems all the data might not reside in one system and also when you query the data for multiple months etc… It might impact the performance of the core system. This could also lead into data retrieval not being fast enough if the underlying database is modeled as a transaction database instead of a dimensional model which is meant for reporting.
- Also if you need features where a person from a specific region should be able to access data pertaining to their region only then you will need to have a DWH with SSAS involved.
Hopefully this blog addresses your confusion on what constitutes a Microsoft BI ECO system and when do you use what? Well some of our customers have really understood it and liked it, if you need a deeper understanding, consultation on your BI tools, Road map, strategy or implementation write to us at email@example.com
Also we have another blog which talks about How business users can maximize ROI from MS BI