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
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.
2. Warehouse Layer
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.
3. OLAP Layer
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.
4. Reporting Layer
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 connect with our experts.
