Database Cube and Browsing the Cube Centre of

Database Cube and Browsing the Cube Centre of Competence on data warehouse Workshop Helsinki 24. 9. 2014 Mark Rantala

From Data Warehouse to Publication SAS Cube Browser Excel/SAS-EG Browsing Aggregate level Unit level Cube AS (analysis server) Data warehouse (SQL-server) SAS-tabulation SAS-data

Analysis Services overview: Microsoft’s application for creating multidimensional OLAP databases, also called Cubes l part of the Business Intelligence Development Studio l included with the SQL Server License l for queries used MDX language l Cube browsers: Excel, SAS-EG, Tableau, Qlik. View, XLCubed, Pro. Clarity… l

Why Store Data in a Cube? Analysis services is a platform for storing data, but instead of tables data is stored into cubes n Why store data in cubes instead of tables l Better query performance l Fast, optimazed aggregations calculations l Richer calculation possibilities, supporting stored measures, calculated measures and key performance indicator l An easier to understand data model for the end user n

Cube - Base on the Unit level data - Updates data: automatically or manually (ROLAP or MOLAP mode) - Constraints: maximum number for dimensions, attributes, cubes in database, measures groups, hierarchies in dimension, etc. is 2^31 -1

Structure of Cube Additional Features - Stored measures - Calculated measures - Measure groups - Key performance indicators - User group perspectives Fact table A key characteristic of a fact table is that it contains numerical data (facts) and usually large number of rows. Dimension - Nace - Letter level - 2. digits - 3. digits - 4. digits - 5. digits Dimension - Time - Year - Quarter - Month Dimension - Area - Country - Province - Municipality

Demo - International trade in services Statistics on international trade in services describe Finnish enterprises’ international sales and imports of services by service type and target country. n Demofile n

From the cube to publication

Thank you for your attention!