Data Analysis - Business intelligence 'BI'


Digital data has exploded in the last few years. This data explosion is driven by the rise of new data sources such as business information systems, social media, cell phones, smart sensors, and dramatic gains in the computer industry. Most industries now agree that we are collecting more data than ever before. The large volumes of data being collected also enables you to build more accurate predictive models.

Organizations that now use data and business analytics to aid decision making are more productive and experience higher returns than competitors that do not.

Data Analytics

Data analytics is data science. If business intelligence is the decision-making phase, then data analytics is the process of asking questions. Organizations deploy analytics software when they want to try and forecast what will happen in the future, whereas BI tools help transform those forecasts and predictive models into common language.

Business Intelligence

Business intelligence is the use of data to help make business decisions. BI often refers to the process that is undertaken by business analysts in order to learn from the data they collect in a post-analysis phase. Business Intelligence can also be used to describe the tools, strategies, and plans that are involved with data-driven decision making.

Implementation of Business Intelligence using (BI) Tools

Business intelligence (BI) tools are types of application software that collect and process large amounts of unstructured and structured data from internal and external systems, books, journals, documents, health records, images, files, email, video, and other business sources.

BI tools provide a way of amassing data to find information primarily through queries. These tools also help prepare data for analysis so that you can create reports, dashboards, and data visualizations.

Microsoft BI Tools


SQL Server Integration Services (SSIS)

The SQL Server Integration Services 'SSIS' is a ETL tool, which is used for building enterprise-level data transformation, and data integration solutions. SSIS is one of the best and more powerful tool to perform Extraction, Transformation and Load (ETL) operations.

SSIS is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

SQL Server Integration Services includes tools for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management database, SSISDB, for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.

SSIS Packages can be created with a VS development environment for building Business Intelligence solutions. Business Intelligence Development Studio is the Visual Studio environment with enhancements that are specific to business intelligence solutions.

SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) is technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions. In simple terms, you can use SSAS to create cubes using data from data marts / data warehouse for deeper and faster data analysis.

Cubes are multi-dimensional data sources which have dimensions and facts (also known as measures) as its basic constituents. From a relational perspective dimensions can be thought of as master tables and facts can be thought of as measureable details. These details are generally stored in a pre-aggregated proprietary format and users can analyze huge amounts of data and slice this data by dimensions very easily. Multi-dimensional expression (MDX) is the query language used to query a cube, similar to the way T-SQL is used to query a table in SQL Server.

Multidimensional Expressions (MDX) is a query language for OLAP databases, much like SQL is a query language for relational databases MDX is a query language that is used to interact and perform tasks with multidimensional databses (also called: OLAP Cubes).

SSAS Tabular models are in-memory databases that model data with relational constructs such as tables and relationships, in order to provide a rapid and powerful way of providing self-service BI to client applications such as Microsoft Excel and Microsoft Power BI.

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Administered via a Web interface, it can be used to prepare and deliver a variety of interactive and printed reports. The SSRS service provides an interface into Microsoft Visual Studio so that developers as well as SQL administrators can connect to SQL databases and use SSRS tools to format SQL reports in many complex ways. It also provides a 'Report Builder' tool for less technical users to format SQL reports of lesser complexity.

Reports defined by Report Definition Language (RDL) can be downloaded to a variety of formats including Excel, PDF, CSV, XML, TIFF, and HTML Web Archive. Report Definition Language is a standard proposed by Microsoft for defining reports. RDL is an XML application primarily used with Microsoft SQL Server Reporting Services. RDL is usually written using Visual Studio, although there are also third-party tools; it may also be created or edited by hand in a text editor.

Users can interact with the Report Server web service directly, or instead use Report Manager, a Web-based application that interfaces with the Report Server web service. With Report Manager, users can view, subscribe to, and manage reports as well as manage and maintain data sources and security settings. Report Manager can also deliver SQL reports by e-mail, or place them on a file share.