Business Intelligence - how to determine the configuration settings for a data mart or warehouse project - notes from field
Fast query response times and timely data refresh are two well-established performance requirements of Online Analytical Processing (OLAP) systems and to provide fast analysis, OLAP systems traditionally use hierarchies to efficiently organize and summarize data.
The content of data warehouse (OLAP) database changes infrequently compared to that of transactional database (OLTP). By default the architecture and charter of data warehouse, the storage requirements increase over time and frequently reach the multi-terabyte range. So you need to maintain such the requirement of a data warehouse seperate from transactional database, the analysis applications should not impact the response time for mission-critical transactional applications. SO the data warehouse usage will be business intelligence, data mining and report generation, this is where reporting platform should be scaled out for better management & performance.
SQL Server Analysis Services combines the benefits of traditional hierarchical analysis with the flexibility of a new generation of attribute hierarchies. Attribute hierarchies allow users to freely organize data at query time, rather than being limited to the predefined navigation paths of the OLAP architect. To support this flexibility, the Analysis Services OLAP architecture is specifically designed to accommodate both attribute and hierarchical analysis while maintaining the fast query performance of conventional OLAP databases.
To start with Analysis Services setup you have to determine the configuration settings and to do that (for newer project) you may have to run traditional calculations by performing an estimate of number of queries per hour that your user population will generate. This will tell you if you want to use the low, medium or high throughput tables. Don’t forget that real people do not submit queries back-to-back with zero think time between queries.
After you estimate your query rate per hour, cross reference to the appropriate column in the tables below based upon database size, this will give you system’s critical components. It is important to note that reference configurations and sizing models are very useful tools for estimating hardware requirements based upon a pre-defined workload for new business intelligence applications where the expected workload is being defined and estimated by discussions between the IT department and the user base. However, if a customer has an existing data warehouse, data mart or pilot/prototype, I strongly recommend using actual Perfmon data to determine the most accurate configuration required to support any given workload. Using real world performance data is the most desirable approach when performing upgrades, consolidation or ports, as opposed to using any sizing tool or reference configuration.
It should be noted that the medium, complex and very complex queries that were profiled for the reference configuration benchmarks in the tables below (low, medium or high throughput), tended to be very I/O bound. It is possible that customers can improve performance by using materialized views, summary tables, more indexes, or other means to reduce the hardware required to support their user population.
However, when sizing the ETL tier you are not concerned with users or "queries per hour." The SQL Server reference configurations for the ETL tier are most significantly influenced by:
- Load/insert rates
- Index builds
- Complex business logic
- Table look-ups
If not known, in order to estimate daily or weekly load/insert rates per batch window, it is possible to calculate backwards. In the real world, actual daily loads will have more or less than 15 million rows. So, you’ll have to decide if you want to size for peak (could be 2 – 5 times average) or use the average volume for initial estimates. It should also be noted that, in the real world, more data is loaded than simply the fact table. For example, dimension table updates are typically very small and have negligible impact on system performance.
People may be surprised that the load/insert rate generally does not put a heavy load on the server. For example, you need the same number of processors to insert 500,000 or 15 million rows within one hour and build 10 indexes on 200 million row tables or partitions within 3 hours. Of course, CPU utilization on the 1 processor (dual-core) server will be higher when loading 15 million rows than loading 500,000 rows within the same time window, but, the workload still fits within a one processor server. If your ETL batch window does not interfere with end user query or cube processing, you may want to consider consolidating the 1 processor (2 core); 4GB of memory into your ROLAP data warehouse.
This consolidation of the ETL tier with the ROLAP tier will reduce network traffic between tiers, especially with regard to returning result sets for table lookups. It is also a best practice to partition your fact table and large dimension tables for optimal performance. Some of the more significant advantages of partitioning are:
- Parallel I/O across multiple partitions will maximize performance.
- If a fact table is partitioned by date, then the oldest partition may be dropped as it ages off of the data warehouse (that is, greater than 5 years old in the reference configurations).
- Instead of building indexes on the whole fact table, it is far more efficient to build indexes on the partition(s) which were affected during the load. So, only the most recent partition(s) need to have rebuilt indexes after a bulk load.
Table look-ups are generally SQL statements used to perform data cleansing and validation operations. They may be costly ETL operations depending upon the complexity of the SQL queries executed. However, the bulk of the table look-up overhead is born by the data warehouse and typically not by the ETL tier, but, the ETL tier (Integration Services) will have to compare data elements, convert encoded data into text, etc. as part of its data cleansing and transformation functions.
Do you need to add hardware to support SQL queries that perform table "look-up" operations during the ETL batch window? Yes!
Reference configurations are usually beneficial before a customer has a data warehouse up and running. At this point in time, a customer’s understanding of table-look-up complexity is typically not known. Therefore, the reference configurations assume that the data being loaded is clean and that any data cleansing, edit checks, remapping of columns, etc. will not consume a significant amount of extra CPU resources over and above load/insert operations. It is important to note that table look-ups and other data cleansing operations usually execute during off hours, when business user queries are very low or non-existent. Therefore, it is fair to assume that the data warehouse tier, which normally handles end-user queries during the day, will have plenty of resources to execute table look-up operations to support typical ETL workloads. The use of SQL Server Analysis Services is optional and is not used in all business intelligence environments. However, as customers realize the benefits of OLAP cube technology, HP is finding that it is being implemented more frequently to provide users with significantly faster response times than that of traditional ROLAP data warehouses / data marts. OLAP cubes also provide data mining capabilities and may be front ended by PerformancePoint and/or Excel which provides users with executive dashboards and other advanced BI features.
In order to determine which OLAP reference configuration will support an estimated number of user queries per hour in your environment, you first should determine which HP server model will support your cube size. (Note: When using a cell-based server, each cell board may be figured with 128GB of memory. This allows an Integrity Superdome server to support up to 2TB of physical memory). One of Microsoft’s "best practices" is to insure that your most frequently accessed cells and aggregates are memory resident. Since this "hit" percentage is frequently not known in the early phases of software development, configurations assume that the entire cube will be memory resident in its OLAP cube reference configurations. Ideally, a memory resident cube will not have any disk I/O once it is in memory. But, some queries may require extra computation, sorts and aggregation. If the customer decides not to keep the entire cube in memory and if non-memory resident cells and aggregates are accessed to execute the query, the customer should expect more disk I/O activity and increased CPU utilization levels. This will, obviously, have a counterbalancing effect on query throughput and expected response times, but a hardware may be easily configured to support shifting workloads and may even be modeled by using capacity planning techniques. Since each server has different memory limits, after you determine your cube size, the table below will tell you the minimum number of processors required to support your memory requirements, so it is good to involve the Hardware vendor.
In order to determine which OLAP build reference configuration will support parallel processing of the cube in your environment, you first should determine which server model & resources will support the number of measure groups and partitions that must be processed in parallel, and supports your cube size. A process full build of an OLAP cube involves three distinct phases of processing: data, index and aggregation, and merge aggregations on disk. Generally, the data phase will have the bulk of its resources consumed extracting data from the ROLAP data warehouse. HP recommends that the number of cores on the data warehouse server be at least as great as the number of partitions being processed at once.
The reference configurations assume that the aggregation and index phase will execute in parallel. In order for this to happen the SSAS (SQL Server Analysis Services) memory configuration parameters must be set such that each partition will fit into its own pre-defined memory space. If more partitions are processed than can fit into the allocated memory space then all the partitions may not execute in parallel and temporary files on disk will be used which can potentially result in performance degradation during processing. The merge aggregations on disk phase is a relatively fast phase when building an OLAP cube, but it does run as a single threaded operation per partition. Processing for an OLAP cube requires the proper configuration of both the database server and the OLAP build server. The database server must be able to support the query issued per partition processed during the build process.
At the same time the OLAP build server must have the resources to support the parallel processing of the results of these partition build queries and the I/O throughput to write the cube data to disk. Assuming that best practices are followed for the number of cores, memory, and I/O throughput requirements, the time required to fully process the selected partitions is based on the size of the largest partition and the speed of the processors used.
Much more to cover on this topic which will be slated down in seperate blog posts....keep this space.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
