SQL Server Business Intelligence

The estimated reading time for this post is 5 minutes

I already explained about Data Warehouse Project, A data warehouse is the foundation for a business intelligence (BI) solution, enabling business users, information workers, and data analysts to make faster, better decisions. This blog post introduces BI, describing the components of Microsoft SQL Server that you can use to create a BI solution, and the client tools with which users can create reports and analyze data.

BI technologies enable you to create decision support systems that enable the individual in an organization to work more effectively. However, a BI solution is only as good as the data that it processes. Therefore, using a data warehouse as the platform for your BI solution makes a lot of sense. The primary purpose of creating a data warehouse is to provide a platform enabling BI workers to access historical data so that they can perform reporting and data analysis. A well-designed data warehouse will include mechanisms that ensure the quality and accuracy of data, and will be optimized to provide the best performance for BI applications.

Business Intelligence Data Source

The primary purpose of creating a data warehouse is to provide a platform enabling BI workers to access historical data so that they can perform reporting and data analysis. A well-designed data warehouse will include mechanisms that ensure the quality and accuracy of data, as described in previous modules, and will be optimized to provide the best performance for BI applications.

Using a data warehouse as a data source for BI provides many benefits, including:

  • Data quality and accuracy: Effective reporting and data analysis rely on the availability of data that is complete, accurate, consistent, and which does not contain duplicate data, or any other data that might compromise the accuracy of the reports and data analyses that information workers create.
  • Data availability: The data used by information workers must also be easily available, so that individuals do not have to search for information.
  • Complete and up-to-date data: To support reporting and data analysis, the data in the data warehouse needs to be complete and up to date. SQL Server Integration Services provides comprehensive extract, transform, and load (ETL) capabilities, enabling you to populate your data warehouse and maintain it by using periodic or incremental updates.
  • Query performance: BI queries can be highly demanding, requiring a significant amount of processing power. A dedicated data warehouse that is optimized for BI queries.

SQL Server provides a complete platform for data warehousing, and supports reporting tools such as SQL Server Reporting Services, Report Builder, and Power View, and data analysis tools like SQL Server Analysis Services, Microsoft Excel, and PerformancePoint Services in SharePoint Server. 

Data Analysis

Data analysis involves exploring data to find interesting and useful information that can help companies to identify patterns, opportunities, inefficiencies, and so on. Data in the data warehouse can be used to perform different types of analysis, such as identifying sales patterns over the previous quarter, or predicting future performance based on past data.

Key data analysis scenarios include:

  • Exploring data to identify patterns.
  • Self-service analysis and data mash-ups.
  • Data mining.

Data analysis tools combine the data and metadata from data warehouses or other stores to create models that make this data available as useable information. SQL Server Analysis Services is the SQL Server component that BI developers can use to create data models, enabling users to access data to perform analysis. Excel can help analysts and business users to create PivotTables and other data visualizations from enterprise data models.

Enterprise Business Intelligence

In many organizations, an enterprise BI solution is designed, implemented, and managed by the IT department to support reporting and analytical requirements. This BI solution is often based on the foundation provided by an enterprise data warehouse.

Data warehouses and marts are at the core of an IT-managed enterprise BI solution, providing a central source of validated business data for reports, dashboards, and analyses. BI specialists create analytical data models that aggregate the data in the data warehouse, defining hierarchies and key performance indicators (KPIs) to support well-defined business requirements. These hierarchies and KPIs can be viewed in dashboards Additionally, business reports are created from the data models, and often directly from the data warehouse. These reports can be viewed interactively in a web browser, and are sometimes distributed automatically by email at regularly scheduled intervals.

SQL Server Analysis Services

You create a data model to enable users to analyze business data, without having to understand the complexities of the underlying database, and to enable optimal performance for BI queries. Data models expose data in a format that users can interact with more easily. SQL Server Analysis Services enables you to create enterprisescale data models to support BI applications including Reporting Services, Microsoft Excel, PerformancePoint Services in SharePoint Server, and other third-party tools. Analysis Services in SQL Server 2016 enables you to create two different kinds of data model, each with its own features and performance characteristics:

  • Multidimensional data models have been used in all versions of SQL Server Analysis Services, up to and including SQL Server 2016 Analysis Services, and are familiar to experienced BI specialists. Multidimensional data models expose data through dimensions and cubes. They use the Multidimensional Expressions (MDX) language to implement business logic, and can provide access to data through relational online analytical processing (ROLAP) storage or multidimensional online analytical processing (MOLAP) storage. 
  • Tabular data models were first introduced in SQL Server 2012 Analysis Services. They are constructed from tables and relationships, making them easier to work with for database professionals with no multidimensional data modeling experience. Tabular data models use the Data Analysis Expressions (DAX) language to implement business logic, and can provide access to data by using the in-memory xVelocity engine. They also use DirectQuery mode to access data in the underlying data source, which is often a data warehouse.

SQL Server Analysis Services supports a concept known as the BI semantic model (BISM), which abstracts the specific implementation details of the data model, enabling client tools to use standard protocols and interfaces to access it, regardless of the specific model type.

SQL Server Reporting Services

SQL Server Reporting Services is available in most editions of SQL Server, including SQL Server Express with Advanced Services, though not all features are supported in every edition. When you install SQL Server Reporting Services, you must choose between two possible deployment modes: 

  • SharePoint integrated mode: In this mode, the report server is installed as a service application in a SharePoint Server farm, and users manage and view reports in a SharePoint site.  
  • Native mode: In this mode, Reporting Services provides a management and report viewing user interface (UI) called Report Manager, which is implemented as a stand-alone web application.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn