Transcript Slides - Zhangxi Lin`s - Texas Tech University

ISQS 6339, Data Management and Business Intelligence
Cubism – Bells and Whistles
Zhangxi Lin
Texas Tech University
1
Outline




Deploying and processing
Cube design tabs
\\bamctxfs\tshome
\\bamctxfs\users\<your_account>
2
Exercise 7: Customizing a Cube

Project name: MMMCube2014_lastname

Tasks
◦ Deploying and processing the cube created
◦ Define and debug calculations
◦ Define KPIs

Deliverable:
◦ Screenshot of the successful deployment
◦ Screenshot of the results from the cube: calculated
measures, KPIs, and drill down/up result.
3
Deploying and Processing
4
Deploying
The first step to creating s useful Analysis
Service structure, moving the project’s
definition from the development
environment to a server environment
 Allows access of users.
 The objects reside on an Analysis Service
database

Processing



The procedure that pumps the database full of the
good stuff.
After having been processed, an Analysis Service
database reads data from its data source.
Dimensions are populated with members. Measures
are populated with facts. The aggregates for all the
combinations within the dimensional hierarchy are
calculated.
Then the Analysis Service database is ready for
analysis to begin.
CUBE DESIGN TABS
7
Understanding the Cube Designer Tabs









Cube Structure: Use this tab to modify the architecture of a cube.
Dimension Usage: Use this tab to define the relationships between dimensions and
measure groups, and the granularity of each dimension within each measure group.
Calculations: Use this tab to examine calculations that are defined for the cube, to
define new calculations for the whole cube or for a subcube, to reorder existing
calculations, and to debug calculations step by step by using breakpoints.
KPIs: Use this tab to create, edit, and modify the Key Performance Indicators (KPIs) in a
cube.
Actions: Use this tab to create or modify drillthrough, reporting, and other actions for
the selected cube..
Partitions: Use this tab to create and manage the partitions for a cube. Partitions let you
store sections of a cube in different locations with different properties, such as
aggregation definitions.
Perspectives: Use this tab to create and manage the perspectives in a cube. A
perspective is a defined subset of a cube, and is used to reduce the perceived complexity
of a cube to the business user.
Translations: Use this tab to create and manage translated names for cube objects, such
as month or product names.
Browser: Use this tab to view data in the cube.
ISQS 6339, Data Mgmt & Business
Intelligence
8
Key Performance Indicators (KPIs)


Digital dashboard
Creating a KPI
ISQS 6339, Data Mgmt & Business
Intelligence
9
The MDX expression for KPI Status
Expression (MaxMinManufacturingDM)
Case
When ROUND([Measures].[percent Rejected],4) < 0.0103
Then 1
When ROUND([Measures].[percent Rejected],4) >= 0.0103
ROUND([Measures].[percent Rejected],4) >= 0.0104
Then .5
When ROUND([Measures].[percent Rejected],4) >= 0.0104
ROUND([Measures].[percent Rejected],4) >= 0.0105
Then 0
When ROUND([Measures].[percent Rejected],4) >= 0.0105
ROUND([Measures].[percent Rejected],4) >= 0.0106
Then -.5
Else -1
End
AND
AND
AND
ISQS 6339, Data Mgmt & Business
Intelligence
10
This should be
“Standard”
Calculated measure
(for a different fact table)
11
KPI definition and
deployment
12
KPI Browser
Browser View
ISQS 6339, Data Mgmt & Business
Intelligence
13
Actions
Instructions stored inside the cube
 Allow the OLAP cubes to “reach out and touch someone.”
 Enable us to define commands, statements, and directives
that are to be executed outside of the cube
 Linked to certain objects in the cube, which can be enacted
as a menu when a user is browsing the objects. The user can
select one of the these actions to accomplish certain tasks.

ISQS 6339, Data Mgmt & Business
Intelligence
14
Types of Actions
Action
◦ Dataset
◦ Proprietary
◦ Rowset - Retrieve a rowset.
◦ Statement
◦ URL
 Drillthrough Action. Defines a dataset to be returned as a
drillthrough to a more detailed level
 Report Action. Launch a SQL Server 2005 Reporting
Services report

ISQS 6339, Data Mgmt & Business
Intelligence
15
Defining Actions
16
Perspectives
17
Translations
18
Q &A

Conceptual level
◦ What are rationale behind the structure of “Data Source”, “Data Source View”
and “Cube”?
◦ Why time dimension is so important in a data mart?
◦ Why is the multi-levels of dimensions, such as Material-MachineType-Machine in
MaxMinManufacturingDM, useful?
◦ Why do you need to change the primary key of DimTime after it was created
from the MaxMinManufacturingFact table?
◦ Can you summarize a number of main differences between a regular database
design and a data mart design?

Technical level
◦ After you made changes in a data source node why do you have to check
“Mapping” in the data destination node again?
◦ When there is a red wave line under an object, such as a table during cube
design, what does it imply? How to solve it? Specifically, when a fact table has
such a problem how could it be fixed?
◦ Why not all dimensions appear in the cube structure diagram?
◦ Do you understand the parameters configured in the data flow tasks, such as
those in data sources, data destination, Aggregate node, Derived Column node,
etc?
19
Data Mining with SQL Server 2008

Microsoft Data Mining Demo -- Scenario
Analysis 4’42”