CTS4408: Module 15

HOLAP

Hybrid OLAP: The facts are left in the OLTP data source, and aggregations are stored in the SSAS server.

OLAP Storage Methods (3)

MOLAP ROLAP HOLAP

Your management wants to create a very responsive SSAS cube for their financial analysts to be able to quickly dig into details - storage costs aren't a concern. What SSAS storage method do you recommend and why?

MOLAP - quickest access

Four Methods of Delivering Data to Users

Microsoft's Excel (Power Pivot) Microsoft's Power BI Direct Access through query langs (similar to SQL for Relational data) · MDX - MultiDimensional eXpressions · DMX - Data Mining eXtensions Third Party Tools (Cognos, Brio, Micro Strategies, Hyperion..)

SQL Server Integration Service (SSIS):

Microsoft's platform for building enterprise-level data integration/transformations Microsoft's version of extraction, transformation, and loading (ETL)

SQL Server Analysis Service (SSAS):

Microsoft's platform for online analytical processing (OLAP)

MOLAP

Multidimensional OLAP: Most common & best performance Details & aggregations stored in a native OLAP format. No zero-activity records are stored. The dimension keys in the fact tables are compressed, and bitmap indexing is used. A high-speed MOLAP query processor retrieves the data.

Your management wants to be able to load data from Oracle and SQL Server databases into their SQL Server data warehouse using SSIS. What edition of SQL Server do the need to purchase on the SSIS server?

Enterprise edition

SSIS data flow task

creates, orders, & runs data flows, using data flow engine.

Dimension

def by aggregation levels of data needed to support data reqs. A dimension can be shared with other cubes, or it can be private to a cube

Workflows

definable precedence constraints that allow to link two tasks, based on whether the first task executes, executes successfully, or unsuccessfully.

SSIS packages

discrete, named collection of connections, tasks, and data flows that implement data movement/data transformation.

Database

logical container of 1+ cubes defined within Analysis Server

Level

nodes of hierarchy or data mining model. Each level contains the members. Millions of members are possible for each level.

SSIS transformations

one or more functions or operations applied against a piece of data before the data arrives at the destination.

Cell

part of data measure that is at intersection of dimensions. Contains data value. If an intersection has no value, it doesn't physically exist until populated.

Workflow Precedence Constraints (3)

Unconditional — doesn't matter if the preceding step failed or succeeded. On success — preceding step must succeed for execution of next step. On failure — This constraint returns the appropriate error.

Hierarchy

a set of members in a dimension and their position relative to each other. Hierarchies can either be balanced or unbalanced.

dtutil

can be used to move SSIS packages from one destination to another (e.g. one database to another or to the filesystem)

Measure group

collection (grouping) of 1+ measures into logical unit for business purposes. Doesn't occupy physical space. It is metadata only.

Partition

1+ partitions comprise a cube. Way to physically sep parts of a cube. If partition by dimension, can perform incremental updates to change that dimension independently of the rest of the cube

Name three parts of an SSAS cube and what each represents:

Cell: inner section of dimension containing value Measure: portion of cell Dimension: three parts Level: level within hierarchy

ROLAP

Relational OLAP: Uses fact data still in OLTP relational DB system to make data much more current (real-time). Summary tables (also known as materialized or indexed views) are populated by processes in the OLTP system and are NOT downloaded to SSAS. Indexes are built for dimension columns

Name four general items included with SSRS

Report Manager Report Server Configuration Manager API Report Builder Report Designer

SSRS Architecture: Databases

ReportServer - Stores all reporting objects, including reports, security settings, schedules, subs, snapshots, users, config settings, & encrypt keys ReportServerTempDB - Stores ephemeral report data (sometimes called intermediate processing products) cached reports/session/execution data.

SSRS Architecture: Report Manager

Web-based interface (URL defined by RSCM) that gives ability: Add/remove, organize, configure, and run all kinds of SSRS objects, (Reports, report resources, data sources, shared datasets, report parts, and folders) (Report models and data source views (used with Report Builder)) Administer the SSRS security model (Users and roles; Role assignments) Manage Report snapshot, history, and caching configuration; Schedules, subscriptions, and related settings; Report execution timeout duration.

dtexecui

a GUI interface to dtexec (not useful for batch, but good for testing)

Reporting Services Configuration Manager (RSCM)

a Windows GUI application that allows for the initialization of each SSRS Server

SQL Server Reporting Service (SSRS)

a cohesive set of development tools, config tools, web services, apps, and utilities, all working together to deliver enterprise-grade reporting

Measure

a data fact representation such as price, unit, or quantity.

Cube

a multidimensional rep of business facts. Types of cubes: regular or local

A developer wants to create a nightly job with complex logic to move data from an OLTP database to an SSAS cube. What technology & tool do you recommend that they use? a. SSIS & SSIS Designer b. SSRS & Report Builder c. SSIS & SSMS export/import wizard d. SSAS & SSDT Tools

a. SSIS & SSIS Designer

SSIS tasks

actions taken to accomplish desired data transformation/movement. Can execute any SQL statement, send mail, bulk insert data, run a Visual Studio Tool for Application script (VSTA), or launch another pkg or external program.

SSIS containers

groups 1+ related tasks that want to manage/reuse together

SSIS data flow

identifies sources/destinations that extract/load data; identifies transformations that manipulate/enhance data; and provides the paths that link sources, transformations, and destinations.

Data source

the origin of a cube's data.