Transcript [Powerpoints] - Getting_Started_with_Tabularx

Getting Started with Tabular
April 11, 2015
Phillip Labry




Sr. BI Engineer
IT development for over 25 years
Developer, DBA, Business Intelligence
Experience with Manufacturing, Telecom, Banking, Retail,
Government, Insurance, Healthcare, Consulting, Energy
Twitter: @phillipLabry
Blog: http://www.skylinetechnologies.com
Blog: http://phillip.labry.com
Skyline Technologies
•
•
•
•
Established in 1992
Associate owned
Offices in Appleton, Green Bay, and Milwaukee
Over 135+ Technology Professionals
— 50+ Microsoft Certified Professionals
— 7 Project Management Professionals (PMP)
— 20+ Certified Scrum Masters (CSM)
— 2 Certified Scrum Professionals (CSP)
• Microsoft Gold Certified Partner - most certified in state
— Very well connected with Microsoft
— 3 Microsoft MVP’s
— 5 Virtual Technology Specialists (VTSP)
— 2 Partner Advisory Council members
• Proven Methodologies
― Agile
― PMBOK
― BABOK
― Testing
CLIENT SUCCESS
BASIC TERMS
Measure
Fact
Dimension
Numeric value that can be aggregated (Sales Amount)
Collection of fields mainly consisting of Measures
Table of values that describes a fact (people, places, things)
Star Schema
Dimension tables radiating out from a related fact table
Snowflake Schema
Dimensions related to other dimensions
Aggregate
Attribute
A mathematical summarization of measures
A property that describes a field
Analysis Services





Released in Version 7 as OLAP Services
Row based storage
Implements Multi Dimensional Expressions(MDX)
Similar enough to SQL to be Very Confusing
Performance gains through preprocessing of
aggregations
 Data Mining added in 2005
Analysis Services Tabular
 Released in 2012
 Column based storage(Xvelocity Engine)
 Performance via in memory storage and
compression
 Implements Data Analysis Expressions(DAX)
 Not like SQL at all
 Consumes data from many sources
DATA SOURCES
SQL Server
Excel
SQL Azure
Analysis Services
Azure Data
Marketplace
APS(PDW)
SSRS reports
MS Access
DATA SOURCES
SQL Server
Excel
Oracle
Informix
SQL Azure
Analysis Services
Teradata
Azure Data
Marketplace
APS(PDW)
SSRS reports
Sybase
OLEDB/ODBC
MS Access
Text files
DB2
Odata feeds
BI SEMANTIC MODEL: VISION
Third-party
applications
Databases
Reporting
Services
LOB Applications
Power
View
Excel
Files
PowerPivot
OData Feeds
SharePoint
Insights
Cloud Services
FOR DEVELOPMENT
Install From SQL 2014 Developer Edition
Must Choose Tabular Server
Only Tabular Or OLAP Per Instance
Use local machine for workspace if you can
DO NOT CHOOSE PRODUCTION SERVER FOR
WORKSPACE
UPDATES
DON’T FORGET TO APPLY SERVICE PACK 2 FOR SQL
SERVER 2012
DIMENSIONS
Wide and shallow
Describe facts
Can contain hierarchies
Can contain calculated columns
DEMO
HIERARCHIES
 Predefine common hierarchies for the users
 Hierarchies are defined from largest group to smallest
 Year
 Quarter
 Month
 Hide columns used for hierarchies where appropriate
DEMO
FACT TABLES
Deep and narrow
Mostly measures
Keys to dimensions
Natural repository for calculated measures
DEMO
TIPS FOR DEVELOPMENT
 Clean table names on first import
 Settle on column names before creating any calculated
column or measures
 Flatten out snowflakes where possible
 Don’t create calculated columns for intermediate measures
 Use views for source data
 Use meaningful and verbose names
 Use attribute properties and formatting
CURRENT TABULAR CHALLENGES






Many to many relationships
True role playing dimensions
Multiple language support
Complex or ragged hierarchies
Measure security
Cell level security