Business Intelligence Solutions Using Microsoft SQL: SSAS and SSIS
The Microsoft BI stack
is designed to provide multi-tiered data transformation pieces that can be layered and utilized as needed based upon end-user requirements and the medium to be deployed in. It provides enterprise-level scalable solutions at limited cost to implement due to the fact that most customers already own the pieces of the solution required to develop a complete integrated BI solution.
Many of today’s applications (accounting, ERP, CRM or other) store data in a SQL database. Even if a particular application is not storing data in SQL, the process to extract, transform and load (ETL) data from the existing database (Oracle, Progress, Informix, Access, FileMaker, etc.) into a SQL data warehouse is very straightforward and can be addressed using SQL Server Integration Services (SSIS) by the Sikich consulting team’s database experts.
Microsoft SQL Server Integration Services (SSIS)
SQL Server Integration Services is a robust data transformation, integration and migration tool. In this particular case, SSIS is leveraged to handle ETL tasks to move the data in a data warehouse for further preparation. During this ETL process, it is possible to select only the specific data of interest (to be used in analysis) and to clean and modify this data as required. Once the data is in the SQL DB in a data warehouse, the ability to use SQL Server Analysis Services (SSAS) becomes available.
Microsoft SQL Server Analysis Services (SSAS)
SQL Server Analysis Services is a data mining tool included in certain versions of SQL. SSAS allows for several key objectives to be accomplished:
- The data can be converted into a cube for use in data analysis; this cube may also be referred to as an Online Analytical Processing (OLAP) data source
- The cube will contain measures and dimensions for use in analysis
- Measures – Are the numeric data that will be displayed at different levels of aggregation depending on the dimensions used in conjunction with them during analysis
- Examples of Measures would include Sales Revenue, Inventory Quantity, Production Cost Amount, Scheduled Hours, etc.
- Dimensions – Are the qualitative or categorical fields that are most typically used to perform analysis of a measure
- Examples of Dimensions would include Customer, Year, Month, Item Number, Product Line, Work Center, etc.
- The nature of the cube allows for measures to be pre-aggregated and ready to be used in analysis without calculations having to run on the server at the time of the data request, making analysis much faster than report generation
- An SSAS cube can be set to refresh as often as required, however for most business applications, a refresh rate of once per day (around midnight) is usually sufficient
- Data from an SSAS cube can be connected to and displayed in dashboards or analytic tables via Excel or by a multitude of other third-party presentation layer solutions
Delivering business intelligence solutions using components of the Microsoft BI stack such as SQL, SSIS and SSAS is a cost effective way of deploying BI to end users within your organization. Please contact Sikich today to learn more.
With offices in Chicago, Decatur, Denver, Houston, Indianapolis, Milwaukee, Naperville, Rockford, Springfield and St. Louis, and with staff positioned across the country, we serve clients nationwide with the responsiveness of a local company.