Transcript SQL Analysis Services

SQL Analysis Services 2005
Microsoft® SQL Server 2005 Analysis Services provides
unified, fully integrated views of your business data
to support online analytical processing (OLAP), key
performance indicator (KPI) scorecards, and powerful
data mining capabilities. It provides reliable business
decision support solutions

SQL Server 2005 Analysis Services (SSAS) provides
 Unified and integrated view of all your business data
 Reporting, online analytical processing (OLAP) analysis
 Key Performance Indicator (KPI) scorecards
 Data mining
Advantages


Microsoft® SQL Server 2005 Analysis Services, organizations now have a
single, consistent solution for reporting against either OLTP or OLAP data
stores.
Reduces the amount of effort required to provide a consistent view of
data that is integrated from an array of disparate applications and formats
Terminologies

Cube
The basic unit of storage and analysis in Analysis Services is the cube.
A cube is a collection of data that’s been aggregated to allow queries
to return data quickly.

Dimension
Each cube has one or more dimensions, each based on one or more
dimension tables. A dimension represents a category for analyzing
business data

Fact table
A fact table contains the basic information that you wish to summarize.
This might be order detail information, payroll records, or anything
else that’s amenable to summing and averaging.
ARCHITECTURE
A schema is a collection of database objects, including tables, views, indexes, and synonyms .
Star Schema




A relational database schema for representing multidimensional data.
It is the simplest form of data warehouse schema that contains one or more
dimensions and fact tables. It is called a star schema because the entityrelationship diagram between dimensions and fact tables resembles a star where
one fact table is connected to multiple dimensions.
The center of the star schema consists of a large fact table and it points towards
the dimension tables.
The advantage of star schema are slicing down, performance increase and easy
understanding of data.
Snowflake schema

A star schema structure normalized through the use of outrigger tables. i.e dimension
table hierachies are broken into simpler tables.

In OLAP, this snow flake schema approach increases the number of joins and poor
performance in retrieval of data. Since dimension tables hold less space, snow flake
schema approach may be avoided.
Important aspects of Star Schema & Snow Flake Schema

In a star schema every dimension will have a primary key.

In a star schema, a dimension table will not have any parent table.

Whereas in a snow flake schema, a dimension table will have one or
more parent tables.

Hierarchies for the dimensions are stored in the dimensional table itself
in star schema.

Whereas hierarchies are broken into separate tables in snow flake
schema. These hierarchies helps to drill down the data from topmost
hierarchies to the lowermost hierarchies.
OLAP world, there are mainly 3 different types:

Multidimensional OLAP (MOLAP)

Advantages





Excellent performance
In MOLAP, data is stored in a multidimensional cube.
The storage is not in the relational database, but in proprietary
formats.
MOLAP cubes are built for fast data retrieval, and are optimal for
slicing and dicing operations.
Disadvantages:


It is limited in the amount of data it can handle. Because all
calculations are performed when the cube is built, it is not possible
to include a large amount of data in the cube itself.
It requires an additional investment in human and capital resources
are needed.
Relational OLAP (ROLAP)
This methodology relies on manipulating the data stored in the relational database


Advantages:


It can handle large amounts of data, ROLAP itself places no limitation on
data amount
Disadvantages:


Performance can be slow. Because each ROLAP report is essentially a SQL
query (or multiple SQL queries) in the relational database, the query time
can be long if the underlying data size is large .
It is difficult to perform complex calculations .

Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and
ROLAP.

Advantages


For summary-type information, HOLAP leverages cube technology for
faster performance.
When detail information is needed, HOLAP can "drill through" from
the cube into the underlying relational data.
Advantages of SSAS Cubes



SSAS is fast even on a large volume of data
SSAS calculated measures are fast execution-wise and easy reusable
They are defined centrally in the SSAS database, and the reports pick
and choose the calculated measures they want.
To build a new data cube using BIDS, you need
to perform these steps:
 Create a new Analysis Services project
 Define a data source
 Define a data source view
 Invoke the Cube Wizard
To create a new Analysis Services project,
follow these steps:

Select Microsoft SQL Server 2005 > SQL Server Business Intelligence
Development Studio from the Programs menu to launch Business Intelligence
Development Studio.


To define a Data source for the new cube, follow these steps:
Right-click on the Data Sources folder in Solution Explorer and select New
Data Source.


To create a new data source view, follow these steps:
Right-click on the Data Source Views folder in Solution Explorer and select
New Data Source View.

BIDS will automatically display the schema
of the new data source view

To create the new cube, follow these steps:

Right-click on the Cubes folder in Solution Explorer and select New Cube.
Deploying ,Processing, Browsing a Cube
Aggregations & Aggregation Wizard
Pre calculated summaries of data from
leaf levels

Aggregations



Aggregations provide performance improvements by allowing Microsoft SQL
Server 2005 Analysis Services (SSAS) to retrieve pre-calculated totals directly
from cube storage instead of having to recalculate data from an underlying data
source for each query.
The Aggregation Design Wizard uses a sophisticated algorithm to select
aggregations for pre calculation so that other aggregations can be quickly
computed from the pre calculated values.
This technique saves processing time and reduces storage requirements, with
minimal effect on query response time.

After the aggregation has been created, if the structure of a cube ever changes,
or if data is added to or changed in a cube's source tables, it is usually necessary
to review the cube's aggregations and process the cube again.
Aggregation Design Wizard.

Microsoft provides a nice wizard to generate aggregates
on measure groups and partitions
MDX



Multidimensional Expressions (MDX) is the query language that you use to work with and
retrieve multidimensional data in Microsoft SQL Server 2005 Analysis Services (SSAS).
MDX is superficially similar in many ways to the SQL syntax that is typically used with
relational databases. However, MDX is not an extension of the SQL language and is
different from SQL in many ways.
Basic MDX Select Query :
Calculations

Calculated members are customized measures or dimension
members that are defined based on a combination of cube
data, arithmetic operators, numbers, and functions. For
example, you can create a calculated member that calculates
the sum of two physical measures in the cube.
SSAS 2005 Day 2
KPI’s





KPIs or Key Performance Indicators are one of the most important entities in driving
business decisions. It can be defined as a (quantifiable) measurement used to define and
measure an organization's progress in achieving business goals.
SQL Server 2005 Analysis Services, allows for the creation of KPIs on its cubes.
KPI measure the health of a business.
KPI uses graphic displays to display status and trend eg. Traffic light
KPI defines 4 expressions for performance metrics
 Actual Value (-1 to 1)
 Goal Value
 Status (-1 to 1)
 Trend (-1 to 1)
KPI Terms used in SSAS






Value
 The value is an MDX expression used to return the actual value of the KPI
Goal
 The goal is an MDX expression used to specify the target value of the KPI.
Status
 Ideal values for the status would be a max of 1 (good) to a minimum of -1
(bad), while 0 indicates neutral status
Status Indicator
 The status indicator is a visual element which is used to present the status of
the KPI. Eg gauges, traffic lights or smileys.
Trend
 The trend is an MDX expression that evaluates the value of a KPI across time. It
can be expressed using any time based criteria. Using this, the business user
will be able to determine how the KPI's value has progressed over time.
Trend Indicator
 The trend indicator is a visual element which is used to present the trend of
the KPI.
The KPIs are done! Next, process the cube. You will be able to view the KPIs using the built-in KPI
Browser under the KPIs tab in BIDS.
Actions

Cube supports actions and action taken in basis of data


Reporting


URL: Go to a specified URL. This type of action supports both directing the user to some URL to
obtain further information, and directing the user to some Web-based application that allows a
new task to be performed.

For example:
For a product, go to the company website describing that product.
Execute a specified report. For eg: for a given product code the action could execute a
parameterized report providing description and current order status
Drill through

User can drill through to the lowest level of detail.
Actions- Drillthrough


The most important aspect of it is that drill
through returns detail level data from within
the cube.
The target can be a cube, dimension,
hierarchy, level, dimension members,
hierarchy members, level members, set,
cells, etc. An action that targets cells can be
further restricted to a subspace of the cube
using an MDX expression.
Partitions


A database partition is an independent subset of a database that contains its own
data, indexes, configuration files, and transaction logs.
A partition group is a logical grouping of one or more database partitions that lets
you control the placement of table spaces and buffer pools within the database
partitions.
Partitions
Security

Cube provide role based security. Roles can be defined and permissions
can be granted to the role.

Administrative permissions can be granted independently of data access
permissions. Also, separate permissions can be defined for reading the metadata
of the object, and for read/write access to the data.
Data can be secured at levels of granularity down to individual cells.

Role based Security
Security
Perspectives
Users engaged on a particular task generally do not have
to see the complete model. To avoid overwhelming users
with the sheer size of the model, we need the ability to
define a view that shows a subset of the model
 The cube provides such views, called perspectives. A
cube can have many perspectives, each one presenting
only a specific subset of the model (measures,
dimensions, attributes, and so on) that is relevant to a
particular group of users. Each perspective can then be
associated with the user security roles that define the
users who are permitted to see that perspective.
Translations


International users frequently have a need to view
metadata in their local language. To address this, the
cube allows translations of metadata to be provided
in any language. A client application that connects
using a particular locale would receive all metadata
in the appropriate language.
The model can also provide translations of data. An
attribute can map to different elements in the data
source, and provide the translations for those
elements in different languages.
From a client computer that has a French locale, both the cube and the query results would be displayed in French