Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


MS SSAS & Tableau

No description

Mario Casillas

on 21 February 2015

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of MS SSAS & Tableau

Analysis Services
Business Question:

What was my Profit Margin for Tables in February?
The Star Schema
Query Performance Issues?
The Challenges
Relational vs. OLAP
Overview of SSAS
SSAS is Microsoft's tool for building Online Analytical Processing (OLAP) solutions. Data is presented and analyzed in multiple dimensions as opposed to two dimensional tabular format.

SSAS forms part of the Microsoft Business Intelligence suite along with SSRS and SSIS

Other similar tools:
How is Data Organized?
Challenges in Tableau
Functional differences
Loss of some features/flexibility
Lack of resources with the right skills
Working with SSAS
Writing MDX
Cube cannot be a secondary data source

Examples of Lost Functionality:
Most efficient for OLAP
Dimensional Relational Advantages:

• Relational database structures are largely vendor independent and especially dimensional structures are pretty easily ported.
• SQL expertise is widely available in the marketplace.
• Hand-coded SQL is generally readable.
• There are many different ways to control DBMS performance, including schema designs, indexes, aggregates and materialized views.
• Relational databases are extremely stable and are suitable for serious archiving and backup.
• Database sizes are pretty much unlimited.
• High-end relational databases can join many large tables.
Using SQL Profiler, determine the culprit:

Query Processor

Storage Engine

Optimize MDX
Avoid functions that are processing hogs (i.e. IIF)

If Memory Bound
add Memory

If CPU Bound
add CPU
Grand Prize Question(s)
Thank you
that is
MS SSAS is an OLAP database
OLTP is about executing transactions
OLTP > Performance of transaction input and update
OLAP is about analysing information
OLAP > Query Performance
OLTP > Raw Data

OLAP > Raw Data, Consolidated Data, Summary Data, Calculated data
OLTP > Current
OLAP > Current and Historic
Microsoft's BI Suite
SSIS - Integration Services - ETL

SSRS - Reporting Services - Reports

SSAS - Analysis Services - OLAP
Where does SSAS fit in?
Oracle Essbase
SAP Olap

...among others
Cubes in More Detail
Multi dimensional data sources
Contain dimensions and measures
Measures (or facts) are the numbers to be filtered, sliced, and/or diced
Dimensions akin to filters or values that add context
Measures pre-aggregated for fast processing
MDX used to query cubes just as T-SQL used to query SQL DB
Aggregate Calculation Functions
Tableau Groups
Tableau Bins
Tableau String Manipulation Functions
Tableau Type Conversion Functions
Cube KPI Data Type
Cube Actions
Tableau Hierarchies
Tableau Custom SQL Connection
Tableau Quick Filter Limitations
Tableau Extracts
Tableau Aliases
Aggregate Functions
Already occurred in the cube and cannot be further aggregated; can use Table Calcs for aggregations on the cell-level results.
Tableau Groups
Can be done in the cube as dimensional attributes or cube sets
Can write custom calculated (MDX) member directly in Tableau to create a group
Tableau Quick Filter Limitations
Dimensions retain their hierarchical look and feel; by design; you can create a Tableau set of certain values from the hierarchy. This set can then be used as a quick filter.
Tableau Extracts
Cannot perform an extract. This is by design; you cannot take an OLAP cube offline and all analysis must occur directly against the cube data source.
Tableau Aliases
Also by design - all alias values must be defined in the cube.
More Info: http://kb.tableausoftware.com/articles/knowledgebase/functional-differences-olap-relational?lang=en-us
For Example: Bins
String Manipulation
Other "Workarounds"
Type Conversion Functions
Should be defined at the cube level or via custom calculated members inside Tableau.
Cube Actions
Not relevant since it can be done better in Tableau.
Cube KPI Data Type
Can be written in Tableau.
Tableau Hierarchies
Can be defined in the cube.
Tableau Custom SQL Connection
Tableau uses optimized MDX queries during analysis. No workaround - these cannot be customized.
Logically very similar to structure of an OLAP cube
Measures (facts)

Ralph Kimball methodology
Leading proponent of the dimensional approach to building data warehouses
A data warehouse is "a copy of transaction data specifically structured for query and analysis"
Bottom-up approach

Star Schema
To OLAP...
Not to OLAP...
[Date].[Calendar Year].[2014] ON COLUMNS,
[Product].[Product].[Table] ON ROWS
FROM [Super Store]
WHERE ([measures].[Sales])
Dimensional Relational Disadvantages

SQL is a truly horrible language for powerful analysis and complex applications.
In spite of performance tuning capabilities, it is still too easy to lose control of performance.
Users generally must know how to join the right tables.
OLAP Advantages

• Generally much better performance than relational when the cubes are designed correctly.
• With MDX, much more powerful analytic capabilities than relational.
• Certain vertical industries, especially financial services, have developed awesome OLAP solutions.
• OLAP encourages more complex security scenarios, especially for ad hoc access. By comparison, it’s difficult to set up a relational database to protect detailed data (sales by sales rep), but provide more open access to summarized data (sales by region). This is especially true for ad hoc access on the relational side.
OLAP Disadvantages

• Don’t expect to port an OLAP implementation to another vendor’s product.
• There is no accepted, universally implemented access language for OLAP, although Microsoft’s MDX is the closest thing to a standard access language.
• MDX in its full glory may be too complex for IT personnel to write by hand, or understand a complex application.
• Much less industry expertise in MDX than in SQL.
• OLAP cubes are not considered stable enough for serious archiving and backup: this is a strong reason for creating a set of dimensional relational tables duplicating the content of the cube for those purposes.
• OLAP vendors have certain size limits not present in relational implementations including possibly the number of members in a dimension, the number of distinct values at various levels in a hierarchy and the overall size of the cube.
• When you must rebuild a cube, it may be a VERY time consuming process.
Multidimensional Expressions (MDX)
Storage Engine
Optimize Dimensions
Identify Attribute Relationships
Use User Hierarchies
Attribute relationships help performance in two significant ways:
Indexes are built and cross products need not go through the key attribute.
Aggregations built on attributes can be reused for queries on related attributes.
Storage Engine
Build Aggregations
Suggesting aggregation candidates
Usage-based optimization

Build Partitions
Separate measure group data into physical units.
(Couple other...)
Query Processor
Our Positioning
If you have existing cubes, do not reinvent the wheel, use Tableau to connect to them.

You will lose some flexibility that you get when connecting to the underlying SQL Server tables.

Creating cubes just for Tableau is usually not worth the investment.

It might be faster, thanks to the cube pre-calculated aggregations, but it won’t be as easy or flexible to use in Desktop compared to connecting to the tables.
Why is it called a "Star Schema"?
Who is US playing today?
What is Bill's biggest pet peeve?
Who Am I?
Mario Casillas
Solution Architect

Started March 2014

Remote: Fort Worth, TX

20+ Years in HE
10+ Years in BI
Data Source for SSAS Cubes
Full transcript