SQL Analysis Services 2005

1
SQL Analysis Services 2005
2

• 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

3
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

4
Terminologies

• Cube
• The basic unit of storage and analysis in
  Analysis Services is the cube. A cube is a
  collection of data thats 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
  thats amenable to summing and averaging.

5
(No Transcript)
6
ARCHITECTURE
7
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
  entity-relationship 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.

8

• 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.

9

• 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.

10

• 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.

11

• 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 .

12

• 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.

13
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.

14

• 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

15

• To create a new Analysis Services project, follow
  these steps
• Select Microsoft SQL Server 2005 gt SQL Server
  Business Intelligence Development Studio from the
  Programs menu to launch Business Intelligence
  Development Studio.

16

• 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.

17

• 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.

18

• BIDS will automatically display the schema of the
  new data source view

19

• To create the new cube, follow these steps
• Right-click on the Cubes folder in Solution
  Explorer and select New Cube.

20
Deploying ,Processing, Browsing a Cube
21
(No Transcript)
22
Aggregations Aggregation Wizard

• Pre calculated summaries of data from
• leaf levels

23
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.

24
Aggregation Design Wizard.

• Microsoft provides a nice wizard to generate
  aggregates on measure groups and partitions

25
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

26
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.

27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
SSAS 2005 Day 2
31
KPIs

• 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)

32
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.

33
(No Transcript)
34
(No Transcript)
35
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.
36
Actions

• Cube supports actions and action taken in basis
  of data
• 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 exampleFor a product, go to the company
  website describing that product.
• Reporting
• 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.

37
Actions- Drillthrough
38

• 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.

39
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.

40
Partitions
41
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.

42
Role based Security
43
Security
44
(No Transcript)
45
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.

46
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.

47

• From a client computer that has a French locale,
  both the cube and the query results would be
  displayed in French