Self-service BI may sound simple. As stated by experts it is an approach to data analytics that facilitates users to access and utilize business information without the IT department’s involvement. The user requires BI tool, which enables the analytics process and helps derive business reports independently. In the previous article, we discussed about the pre-requisites to design a self-service BI system.
In this article, I would like to further discuss how we can design a Self Service BI System using MS BI.
Below is a step-by-step approach to adopt Self-Service BI System:-

• Gather the data from several platforms
• Create a Data Warehouse and aggregate tables
• Provide users access to Data Warehouse and aggregate tables and connect them with MS excel
• Connect Data warehouse to report builder
• Create a cube on the top of data warehouse comprising the calculated columns, KPI and then connect with MS excel for further slicing and dicing

Gather the data from several platforms: In an organisation data originates from different applications in various formats and is further segregated at different levels. To initiate the data warehousing process we need to gather the data at one location, next the data can be loaded into data warehouse.
Why to gather data first? Why not directly load in data warehouse? Exactly, gathering/staging area is not a necessity, if we can handle it on regular basis. But can we? Here are a few reasons why we can’t skip a staging area:

• We cannot connect to source system for longer duration.
• There are several basic checks to be followed before loading the data into data warehouse and it is better to load the data in staging first and validate it.
• We can join the two or more tables to generate relevant data.
• From the same staging place, we can load data at several places.

Create a Data Warehouse and Aggregate tables: After loading the data to data warehouse, tables are created to make it more organised and significant.
Why we need a data warehouse?

• In Data warehouse we store the historical data which cannot be maintained in ERP system.
• In data warehouse we can arrange the data according to focus areas namely, customer, products and sales etc.
• The source data is often inconsistent having different formats. The integrated data source must be made consistent to provide a unified view of the data to the users.

Why we need an aggregate table?
For large transactions it is better to create aggregate tables that give present data for analysis directly from Data Warehouse. The query will run faster on the tables and it will be easier to create cube.
Provide user access to Data Warehouse, Aggregate tables and connect them with MS excel: The first step which provides self-service BI capabilities for business users. Once the data warehouse is ready, create a role based access for business users depending on their data requirements to create ad-hoc reports. Linking data warehouse to excel provides flexibility to users to extract data and utilise MS excel tools for reporting.
Connect Data warehouse to report builder: Another option available to business users to create and save the reports as per their requirements. Mostly Business users face problems in using database/tables directly for reports. In report builder, we can create a business layer for name of every table, known as data model and deploy them on web. This hides the complexity of technical term and business user can easily create report using data model. Also Report Builder is a, stand-alone application that can be used by developers and business users alike to create rich reports in data visualizations. It provides an MS Office-like interface making it easy to connect to a variety of data sources and display data from those sources in tables, charts, lists, gauges, or maps.
Create a cube on the top of data warehouse comprising the calculated columns, KPI and then connect with MS excel for further slicing and dicing: The next phase to provide a business user more flexibility with quick turnaround time. A developer needs to design a SSAS Cube, where we can use data warehouse and create relationships between different dimensions and facts, or create hierarchies to drill down data using tools like MS excel, Power BI tool or SSRS.
The developer can implement complex formulas for business user, which can be directly used to generate reports without any complexity.  The business users can churn out KPI, MIS reports easily in short time span without difficulty.  The  developer creates a connection in the MS excel file to SSAS Cube using Analysis services connection and then business user can convert  data into pivot, chart and gauges etc.
It is not just about building a self service BI, the business users have different requirements and analytical queries. In addition, regular training is required to make the users gain ease with the system. The technical teams need to monitor the BI tools and applications to ensure the users are analysing data in a consistent manner and the system remains updated.
Image Courtesy: www.metasite.netSelf-service BI may sound simple. As stated by experts it is an approach to data analytics that facilitates users to access and utilize business information without the IT department’s involvement. The user requires BI tool, which enables the analytics process and helps derive business reports independently. In the previous article, we discussed about the pre-requisites to design a self-service BI system.
In this article, I would like to further discuss how we can design a Self Service BI System using MS BI.
Below is a step-by-step approach to adopt Self-Service BI System:-

• Gather the data from several platforms
• Create a Data Warehouse and aggregate tables
• Provide users access to Data Warehouse and aggregate tables and connect them with MS excel
• Connect Data warehouse to report builder
• Create a cube on the top of data warehouse comprising the calculated columns, KPI and then connect with MS excel for further slicing and dicing

Gather the data from several platforms: In an organisation data originates from different applications in various formats and is further segregated at different levels. To initiate the data warehousing process we need to gather the data at one location, next the data can be loaded into data warehouse.
Why to gather data first? Why not directly load in data warehouse? Exactly, gathering/staging area is not a necessity, if we can handle it on regular basis. But can we? Here are a few reasons why we can’t skip a staging area:

• We cannot connect to source system for longer duration.
• There are several basic checks to be followed before loading the data into data warehouse and it is better to load the data in staging first and validate it.
• We can join the two or more tables to generate relevant data.
• From the same staging place, we can load data at several places.

Create a Data Warehouse and Aggregate tables: After loading the data to data warehouse, tables are created to make it more organised and significant.
Why we need a data warehouse?

• In Data warehouse we store the historical data which cannot be maintained in ERP system.
• In data warehouse we can arrange the data according to focus areas namely, customer, products and sales etc.
• The source data is often inconsistent having different formats. The integrated data source must be made consistent to provide a unified view of the data to the users.

Why we need an aggregate table?
For large transactions it is better to create aggregate tables that give present data for analysis directly from Data Warehouse. The query will run faster on the tables and it will be easier to create cube.
Provide user access to Data Warehouse, Aggregate tables and connect them with MS excel: The first step which provides self-service BI capabilities for business users. Once the data warehouse is ready, create a role based access for business users depending on their data requirements to create ad-hoc reports. Linking data warehouse to excel provides flexibility to users to extract data and utilise MS excel tools for reporting.
Connect Data warehouse to report builder: Another option available to business users to create and save the reports as per their requirements. Mostly Business users face problems in using database/tables directly for reports. In report builder, we can create a business layer for name of every table, known as data model and deploy them on web. This hides the complexity of technical term and business user can easily create report using data model. Also Report Builder is a, stand-alone application that can be used by developers and business users alike to create rich reports in data visualizations. It provides an MS Office-like interface making it easy to connect to a variety of data sources and display data from those sources in tables, charts, lists, gauges, or maps.
Create a cube on the top of data warehouse comprising the calculated columns, KPI and then connect with MS excel for further slicing and dicing: The next phase to provide a business user more flexibility with quick turnaround time. A developer needs to design a SSAS Cube, where we can use data warehouse and create relationships between different dimensions and facts, or create hierarchies to drill down data using tools like MS excel, Power BI tool or SSRS.
The developer can implement complex formulas for business user, which can be directly used to generate reports without any complexity.  The business users can churn out KPI, MIS reports easily in short time span without difficulty.  The  developer creates a connection in the MS excel file to SSAS Cube using Analysis services connection and then business user can convert  data into pivot, chart and gauges etc.
It is not just about building a self service BI, the business users have different requirements and analytical queries. In addition, regular training is required to make the users gain ease with the system. The technical teams need to monitor the BI tools and applications to ensure the users are analysing data in a consistent manner and the system remains updated.
Image Courtesy: www.metasite.net[:]