Objective:

To bring all BUSINESS NUMBERS, relevant to decision-making, together to improve ease of analysis across the whole product range.

Implication:

Need for DATAWAREHOUSING & OLAP (On-Line Analytical analysis)

Why SQL2000:

Because it does both datawarehousing & OLAP. Together they enable the building of a DECISION SUPPORT SYSTEM (DSS). Another element to a DSS is DATA-MINING. Also, to simplify the building of a data warehouse, ETL (Extract, Transform, Load) tools are used. SQL2000 has such a tool, it is called DTS (Data Transformation Services).

Datawarehousing:

All ACTUALS need to be brought together. The actual NUMERIC quantities are stored in a 'FACT TABLE' in SQL Server. 

OLAP:

It is these fact tables that populate the CUBES. The cubes are essentially views, stored multidimensionally, to enable fast query analysis of the data. Cubes exist in a sub-section of SQL Server called ANALYSIS SERVICES. Cube design requires the existence of 'DIMENSION TABLES'.

Dimension Tables:

Alongside the relational fact tables, in the SQL database, are dimension tables. Theses tables are a list of the unique data members that exist in the NON-NUMERIC columns of the consolidated fact table. For example, the fact table will have a column called Cover Type. All the unique Cover Types need to be listed in a separate dimension table. These Cover Types can be grouped at higher levels. This grouping is done in the dimension tables.

Tools for Datawarehousing:

A SQL Server is accessed using ‘ENTERPRISE MANAGER’. From here work can be done on the databases. Enterprise Manager also enables access to DTS. SQL code can be written automatically by DTS using its graphical facilities. However, a basic understanding of SQL code is necessary. DTS will enable the updating of all tables with new monthly data. There will also be a significant amount of data transformation that will need to take place; again this can be done in DTS. There is another tool outside of Enterprise Manager that is also part of SQL2000; this is Query Analyzer.  This is for the direct writing of SQL queries.

Databases:

The main components of the SQL Server database are the TABLES of data, VIEWS on that data, DIAGRAMS of how the tables interact & STORED PROCEDURES, which store SQL programmes.

Cubes:

Cubes are created & maintained in ‘ANALYSIS MANAGER’. Cubes are made up of DIMENSIONS, which in turn are comprised of MEMBERS. Dimensions may have a number of different LEVELS (e.g. a time dimension may be made up of three levels: month, quarter & year). The dimension may also have more than one HIERARCHY (e.g. days/weeks/year & days/months/year). The dimensions are created from the dimension tables & the measures (the actual numbers) from the fact table. Hence, the close connection between SQL tables & cubes. Members can be calculated in cubes (e.g. the avg. incurred amount measure can be calculated from the incurred amount & claims count measures). These CALCULATED MEMBERS can be applied to dimension members as well as measures. They are created using a multidimensional version of the SQL code called MDX (multidimensional expressions). An MDX builder in the cube or dimension editor enables a certain amount of drag & drop. To do more advanced calculations knowledge of MDX is required.

Accessing Cube Data:

All cube data is accessed via PIVOT TABLE SERVICES. This is true whether the front-end is Excel or a third-party tool such as Crystal Analysis. In all cases MDX queries are being written. One of the big advantages of accessing cube data is that the reports are DYNAMIC. That is the numbers can be changed, almost instantly, by changing the value of a drop-down box (e.g. selecting a different product).

Query Performance:

The performance of front-end tools depends to a large extent on the design of the cubes. Analysis Manager has a number of facilities to help fine tune performance. Speed of query performance is often traded against size of the cube. The more the number of aggregates pre-calculated the bigger the cube's size. This design storage facility controls the problem of data explosion that affects many OLAP tools. By ensuring query speed is minimal end-users are more likely to be happy using the tool & hence do more analysis based on facts, which feeds through into BETTER DECISIONS & more VALUE ADDED.