Turning Data into Information
If we use an example of a simple sales report (Fig 1. Sales System Database), to access the information required the report must get data from seventeen tables; some of these tables are three joins deep. Not only is this inefficient but it does not solve the problem of then adding the budget data from the budget system to the report. This is very difficult to achieve off the sales and budget systems in the example below, and if a report is written it is usually inflexible, difficult to maintain and processing can take a long time.
By implementing a Sales Data Warehouse (Fig 2. Kimball Star Schema Data Warehouse), based on design principles evolved from decades of industry implementations and best practices, the same report can be built quickly off five tables, all of which are directly attached to the sales record. Included in the sales record is the associated budget data, so from this single location, the sales vs budget question can be answered. Now we can also run the same report over a number of different dates including fiscal, tax or business years. We can also easily add to and remove data from the reports.
In the past Data Warehouses have been the domain of large organisations willing to spend vast sums of money, time and effort. Many of these attempts have not been able to deliver the expected value and have struggled to provide a return of investment. Part of the reason for this is that traditionally organisations have seen Data Warehousing as a technical problem and delivered a technical solution, however successful data warehouse solutions have always been a business problem supported by a technical solution.
Warehousing is now a mature technology and the ability to deliver real business value in a short timeframe with minimal cost is now a reality.
The Microsoft SQL server platform is ideally suited to deliver fast, cost effective solutions that meet the requirements of the business. Many organisations already have an investment in Microsoft SQL Server and Mero can help leverage this investment to provide a platform of data that can be used to drive the business forward.
Microsoft provide three core tools as a foundation for their Warehousing:
1. SQL Server Database
Enterprise level data storage and support for a number of Data Warehouse specific requirements.
2. Integration Services (SSIS)
Extracts, transforms and loads (ETL) data from a large number of different sources into your warehouse. SSIS has a number of prebuilt data transformations to reduce development time and complexity.
3. Analysis Services (SSAS)
Industry Leading OLAP data source. SSAS allows the data to be stored in a business understood structure with advanced calculations. This means SSAS is aware of the relationships in the data like cities belonging to a country, and can then aggregate values accordingly.