SQL Server Training | SQL Training

Introduction

Individuals will learn how to use Microsoft SQL Server 2008/2012 Analysis Services (SSAS) to design and implement Multidimensional OLAP cubes to support Business Intelligence (BI) Solutions. Delegates will learn about the Microsoft SQL Server Visual Studio development environment (SQL Server 2008 Business Intelligence Development Studio (BIDS), SQL Server 2012 Data Tools) for design, creation and deployment of SQL Server BISM (Business Intelligence Semantic Model) Multidimensional Mode projects, and SQL Server Management Studio for the management of Multidimensional databases. Included in this course, is an introduction to the concepts of Data Warehouse relational databases and OLAP Cube Fundamentals, becoming familiar with terminology such as Fact Tables, Dimension Tables, Data Sources, Data Source Views, Cubes, Dimensions, Attributes, Hierarchies, Measures, Measure Groups and Relationships. This course will take delegates through building a multidimensional cube in its simplest form through to extending the features within the cube by implementing MDX calculations, Key Performance Indicators (KPIs), Actions and Dimension/Cube Intelligence. The main emphasis of the course is on the design and creation of cubes, but basic administration and security of cubes are also covered. An introduction of the MDX language will provide an insight into querying cubes through MDX and extending the functionality of cubes with scripting. SQL Server Analysis Services 2008 / 2012 covers the interrogation of multidimensional cubes from Microsoft Excel 2007/2010/2013 and the use of multidimensional cubes as data sources for SQL Server Reporting Services.

Pre-Requisites

• No prior experience is needed for this course.
• Experience with SQL Server 2005, 2008 or 2012, basic database concepts such as tables, queries and indexing would be useful.

Course Outline

Introduction to Business Intelligence & Data Warehousing

• Data Warehouses & Data Marts
• Fact Tables
• Dimension Tables
• Dimensions Cubes
• Star Schemas
• Snowflake Schemas
• Business Intelligence Semantic Model (BISM)
• Multidimensional Unified Dimension Model (UDM)
• Tabular Model

Introduction to SQL Server Analysis Services (SSAS)

• Architecture
• Features
• Tools
• Terminology

Introduction to MDX

• Fundamental Concepts
• Members
• Cells
• Tuples
• Sets
• Simple MDX Queries
• MDX Expressions: Operators, MDX Functions

Data Sources & Data Source Views

• Supported Data Sources
• Data Source View (DSV) Wizard
• DSV Designer

Dimension Design

• Database Dimensions
• Dimension Wizard
• Dimension Designer
• Attributes
• Attribute Relationships
• Hierarchies and Levels
• Deploying & Processing Dimensions
• Browsing Dimensions
• Sorting Dimension Members
• Dimension Translations
• Snowflake Dimensions
• Time Dimensions
• Parent-Child Hierarchies

Cube Design

• UDM
• Cube Wizard
• Deploying & Processing Cubes
• Browsing Cubes
• Cube Dimensions
• Measures & Measure Groups
• Calculated Members
• Cube Perspectives
• Cube Translations
• Browsing Perspectives and Translations

Advanced Dimension Design

• Custom Rollups
• Unary Operators
• Naming Levels in Parent-Child Hierarchies
• Customising Dimension Properties: Ordering, All Member, Default Member, Unknown Member, Error Configurations, Storage Mode, Grouping Members
• Dimension Intelligence: Account, Time and Dimension
• Dimension Write back

Advanced Cube Design

• Measure Groups & Measures
• Adding & Enhancing Dimensions
• Fact Relationships
• Many-To-Many Relationships
• Data Mining Dimensions
• Role-Playing Dimensions
• Adding Calculations To Cubes
• Key Performance Indicators (KPIs)
• Actions: URL redirection, Report redirection, Drill-through
• Cube Intelligence: Semi-Additive Measures, Currency Conversion
• Cube Partitions: Storage, Aggregation Design, Usage-based Optimisation

Administering SSAS

• Management Studio
• Database Creation
• Managing SSAS Objects
• XMLA Scripts
• Processing SSAS Objects
• Managing Partitions
• Backup & Restore
• Detach and Attach
• Synchronisation
• Managing SSAS Security

Securing Data in SSAS

• Security Architecture
• Cube Roles
• Database Dimension Security
• Cube Dimension Security

Designing For Performance

• Fine Tuning Dimensions & Cubes
• Optimising For Processing
• Designing Aggregations

Data Mining Introduction

• Data Mining Process
• Data Mining Algorithms
• Data Mining Models

Analysing Cubes with Microsoft Office Components

• Analysing Data in Excel: Pivot Tables, Pivot Charts
• Analysing Data in PowerPivot

Other Features

• BISM Tabular Mode Overview
• PowerPivot Overview