By: Arshad Ali | Comments | Related: > Analysis Services Development
Problem
In my last tip in this series, I talked about how Business Intelligence (BI) systems have become a priority for top executives in order to optimize resource utilization as well as to remain competitive. I also talked about BI users and how BI relates to Data Warehousing and Business Analytics. In this tip, I am going to take this discussion even further in order to understand how Business Intelligence differs from an organization's OLTP systems, what a typical BI system architecture looks as well as the different components of the Business Intelligence system architecture.
Solution
What is OLAP and how does it differ from OLTP?
The OLTP (OnLine Transaction Processing) systems are supposed to track and record transactions in real time and intended to automate the process of clerical data entry or data retrieval for functional areas. It allows to users to add, modify, delete and view data in the OLTP database using the front end user interface. You can learn more about OLTP system at Wikipedia here.
On the other hand, OLAP (OnLine Analytical Processing) systems are core to Business Intelligence systems and are supposed to allow the user to quickly analyze vast amounts of information that has been summarized into multidimensional views and hierarchies. OLAP tools are used to perform trend analysis on the summarized data; for example analysis on aggregated sales and financial information on a yearly basis. OLAP tools enable business users to easily and selectively extract and view data from different point of views or dimensions like time, geography, gender, product, etc. Some of queries which can be answered from OLAP systems, in timely manner, are:
- Which region generates highest level of revenue for the organization?
- Which store in that particular region is most responsible for the revenue?
- What product or product categories contributes most to the revenue?
- Which customer segment is using which product or which product is popular amongst which customer group?
You might argue that answers for these kinds of questions can also be ascertained from OLTP databases as well; then why is an OLAP system needed? Well, you can answer some of theses questions with an OLTP system to some extent, but its not recommended. Typically these types of queries are based on a large set of data. Further, it is going to take longer to get the result and these types of queries will negatively impact the performance of the overall OLTP system. Remember, OLTP systems are meant for transaction processing, which might impact a few records whereas OLAP systems are based on analytical processing, which might impact thousands to millions of records. Keep in mind the BI platform pre-calculates or summarizes the aggregates beforehand, i.e. summation of sales per year, so when you run a query to get total sales per year the query just provides the data from the aggregates and does not need to calculate each value. But it does not mean, you need OLAP systems for all types of reporting; you can still use OLTP systems for operational reporting.
A typical Business Intelligence System Architecture based on the Microsoft SQL Server and Business Intelligence Platform
When we talk about designing a Business Intelligence System, there are certain components which need to be planned, designed and developed as you can see below:
Data Integration or ETL (Extract, Transform and Load) Pipeline
An organization might have one or more different types of applications catering to the needs of the organization's functions. When we talk about designing and developing a Data Warehouse as part of the Business Intelligence System, we need to define the strategies for data acquisition from all the these source systems (or based on the customer requirements) and integrate it into a data warehouse (I will talk in more detail about different design methodologies for data warehouses in my next tip), this is required for doing analysis on complete organizational data and for doing cross functional analysis.
Your Business Intelligence System can leverage SSIS (SQL Server Integration Services), a component of the SQL Server platform, for data integration into the data warehouse. SSIS provides the ability to have a consistent and centralized view of data from different source systems and helps to ensure data confidence through integration, cleansing, profiling and management. SSIS features a fast and flexible ETL (Extraction, Transformation and Loading) framework and has in memory transformation capabilities for extremely fast data integration scenarios. SSIS has several built-in components to connect to standard heterogeneous data sources (RDBMS, FTP, Web Services, XML, CSV, EXCEL, etc.), along with a rich set of transformation components for data integration. SSIS also includes DQS (Data Quality Services) for data cleansing, matching and profiling. You can learn more about Data Quality Services here. MDS is another component in SQL Server which provides Master Data Management platform for managing organizational master data centrally, click here for more details.
SSIS packages can be developed using the Business Intelligence Development Studio (BIDS) in SQL Server 2008 R2 and earlier versions (SSIS is available from SQL Server 2005) or SQL Server Data Tool (SSDT) in SQL Server 2012 and can be configured during execution based on the meta data stored in meta data repository. There are several ways of SSIS Package Deployment and SQL Server 2012 introduced even more robust Package Deployment Model, click here for more information.
Analysis - Once you are done with creating a Data Warehouse and the data integration components to load data into the Data Warehouse, next you need to create an OLAP multi-dimensional structure. Your Business Intelligence system can leverage SSAS (SQL Server Analysis Services), for making data available for analytics and reporting. SSAS, a leading OLAP tool, delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. SSAS pre-calculates, summarizes and stores the data in a highly compressed form which eventually makes the reporting and predictive analysis extremely fast and interactive exploration of aggregated data from different perspectives.
Data Mining models created in SSAS can help identify rules, patterns and trends in the data, so that you can determine why things happen and predict what will happen in the future. SSAS has already included several data mining algorithms as out of box capabilities. SSAS also lets you define KPIs (Key Performance Indicators) to your SSAS cube in order to evaluate business performance over time against the set target, as reflected in the cube data. To learn more about SSAS check out this tutorial.
Normally, the front-end reporting is provided by data through SSAS cubes (in some cases your reports can get data directly from the Data Warehouse, though based on my experience I would suggest a single point of data access through the cube unless there is a pressing need). These cubes aggregate data and through cache management features optimize the query results, so that predefined queries have quicker response time than summarizing these from the underlying data sources every time users query it.
Information Delivery - Once you are done with creating SSAS cubes
(multi-dimensional structure) and populated them with the data from Data Warehouse,
you can use different reporting tools to analyze the data from different perspectives or dimensions.
SQL Server Reporting Services (SSRS) allows creating formatted and interactive reports with or without parameters.
SSRS also has highly scalable distribution and scheduling capabilities for automatic report delivery.
You can create tabular reports as well as different varieties of chart reports, graph reports, map or geographical reports.
In addition, KPI based scorecards reports can also be created.
PowerPivot, Power View, Excel services and SSRS provide users with the ability to both define and execute ad-hoc reports from a standard data model. Through the SSAS cube, users have the ability to analyze reports in a flexible manner based on the exposed measures and dimensions. Users will also have access to the most normalized data and ability to drill down into data
for slicing and dicing.
PowerPivot, Power View, Excel services provide rapid data exploration, visualization, and presentation experience for users of all types - from business executives to information workers. It allows users to interrogate the data from various aspects by using charts, graphs, drill-down paths, etc.
Collaboration and hosting platform -You can use SharePoint 2010 or 2013 as
a collaboration, hosting and sharing platform; all the reports and dashboards
will be deployed or hosted by
a SharePoint portal. SharePoint is one of the leading products for enterprise content management and provides collaboration, social networks, enterprise search, business intelligence, etc. capabilities out of
the box.
Its not mandatory to have SharePoint as the reporting user interface, you can very well have SSRS reports deployed on report server. Though it is recommended to have SharePoint as
a hosting platform as you get several other out of
the box features like Performance Point services for creating nice looking dashboards, which provides out of box drill down, drill path and decomposition.
Further, Excel and PowerPivot services can be used for deploying Excel or PowerPivot to SharePoint in order to make it available to other people, turning Personal BI into Organizational BI.
For security, you can use SharePoint built in security features which allows role based security. SSAS also allows defining role based security as well as cell level security. These security roles or cell level security will govern data access and ensures the right person has access to right information at right time.
In my next tip, I am going to talk in more detail about Data Warehousing and its design methodologies, how data warehousing differs an from Operational Data Store (ODS), etc. so please stay tuned.
Next Steps
- Review SQL Server Integration Services Tutorial tips.
- Review SSIS new deployment model in SQL Server 2012 tip.
- Review Data Quality Services tip.
- Review Master Data Services tip.
- Review SQL Server Analysis Services Tutorial tips.
- Review SQL Server Business Intelligence tips.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
