SQL Server Business Intelligence Reporting
By: Siddharth Mehta
The last layer in our solution architecture is reporting. Reporting is mostly the face of the analytics solution to the end-client. Reports can source data from Online Transaction Processing (OLTP) systems as well as Online Analytical Processing (OLAP) systems. Reports can be categorized at a higher level into three categories - Operational, Analytical and Strategic. We will look at how these three categories of reports facilitate analytics.
Operational reports are generally used for reporting transactional or short-term data from transactional systems. Examples of operational reports are daily status reports, product catalogs, Excel like tabular reports, etc. These category of reports are usually short-lived and are used for aiding day-to-day operational activities. Usually the users of these reports are the personnel who work in the field like sales executives, operations manager, store attendants, etc.
Analytical reports are generally used for reporting analytical or a large volume of historic data from Data Warehouses or Data Marts. Examples of analytical reports are production reports with pivoted and aggregated data represented in a matrix, such as country level sales reports that use charts and graphs to represent data, customer sentiment analysis data represented on a geo-spatial map, etc. This category of reports are usually used for problem-decomposition, trend analysis, pattern recognition, etc. Usually the user of these reports are mid-level managers, data analysts, statisticians, data curators, etc.
Strategic reports are generally used for reporting a mix of operational and analytical data against a pre-defined set of thresholds based on the goals of an organization. Typical examples of strategic reports are dashboards composed of scorecards, gauges, trend-lines, indicators, etc. These reports are also known as decision support reports. This category of reports are usually used as decision support and monitoring progress. Usually the user of these reports are senior-management or leadership of the organization.
SQL Server Reporting Services (SSRS) is the reporting technology for the Microsoft Business Intelligence technology stack. Using SSRS, one can create professional quality reports in each category. Consider reading the SSRS tutorial to start learning SSRS. You can download AdventureWorks SSRS Samples and analyze the sample reports, which also has reports that are relevant to our business scenario as shown below.
- Consider reading SSRS tips to learn more about SSRS development.