microsoft analysis services july 2010
DESCRIPTION
This is an introduction to Analysis Services focusing on when to use it and how to use it effectively. Dan Bulos covers the three pillars of multidimensional databases: Aggregation Management, Embedded Metadata, and the Calculation Engine. For analytical applications, performance management, or just about anything with a KPI, Analysis Services is the perfect companion for SQL Server. These are complementary databases that do different things well. Using the right tool for the right job will make development faster and usability easier. You Will Learn: • How SSAS aggregation management saves development time and runs faster • How the embedded metadata (semantic layer) of SSAS creates a more usable application • The benefits of using MDX as calculation language • How to make effective use of both the relational & multidimensional SQL Server databasesTRANSCRIPT
Why Cubes?Why Cubes?Why Cubes?Why Cubes?Benefits of Using Analysis ServicesBenefits of Using Analysis Servicesg yg y
Speaker: Dan BulosSymmetry CorporationSymmetry Corporation
July 2010
Mark Ginnebaugh, User Group Leader
Why Cubes?Why Cubes?Benefits of Using Analysis Services
Dan Bulos – [email protected]
©2010 Symmetry Corporation http://www.symcorp.com
Why NOT Cubes?
I already know SQL
RDBMSs do everything I need to do
MDBs are not as mature
Cubes are hard
My SQL skills are translatable to other vendors
©2010 Symmetry Corporation
vendors
http://www.symcorp.com
Why Cubes are a Good Idea
Aggregation Aggregation SemanticSemanticAggregation Aggregation ManagementManagement
SemanticSemanticLayerLayer
StrategyStrategyManagementManagementMaintenanceMaintenance
DimensionsDimensionsHierarchiesHierarchies
Time IntelligenceTime Intelligence
CalculationCalculationE iE i
NavigationNavigation KPIs & MetricsKPIs & Metrics
EngineEngineClear FormulasClear Formulas
Faster CalculationsFaster Calculations
©2010 Symmetry Corporation http://www.symcorp.com
Faster CalculationsFaster CalculationsBusiness FunctionsBusiness Functions
No Correlated No Correlated SubqueriesSubqueries
Aggregation Management
Aggregation Strategy▪ What aggregates to create
Aggregation Navigation▪ Query independence
Aggregation Maintenancegg g▪ Re-Aggregation following a change in strategy
Usage-Based Optimizationg p▪ Logging of queries and aggregates used
Fast Aggregations
©2010 Symmetry Corporation
Fast Aggregations▪ No joins
http://www.symcorp.com
Semantic Layer
Translation of data into information▪ A layer between the DBA and User
Familiar terms▪ Schema knowledge not required
Understands relationshipsp▪ Dimensions, Attributes, Hierarchies
Represents the business perspectivep p p▪ Not the data
©2010 Symmetry Corporation http://www.symcorp.com
Calculation Engine
Clear Formulas▪ Designed for the job
Faster Calculations▪ Random access to data
Business Functions▪ Time intelligence (e.g. Lag)
No Correlated Subqueries
©2010 Symmetry Corporation http://www.symcorp.com
MDX – The Good, Bad, & Ugly
The Good▪ Best calculation language
The Bad▪ No enough knowledge in the marketplace
The Ugly▪ () {} [Product].[Corporate].[Group].&[14563]
©2010 Symmetry Corporation http://www.symcorp.com
Analysis ServicesMgt StudioMgt Studio ClientClient
ToolsToolsBI Wizards
IntelliCubeBI Dev StudioBI Dev Studio
Analysis Services
UDMUDMData
Mining
PPSPPS
A
DO
MD
IntegrationIntegrationServicesServices
UDMUDMData Sources ViewsData Sources Views
DimensionsDimensionsCubesCubes
MeasuresMeasures
Mining
KPIsExcelExcel
XML/
A MeasuresMeasuresCalculation ScriptsCalculation Scripts
Actions
Excel Excel
©2010 Symmetry Corporation http://www.symcorp.com
Data SourcesSQL, OLE DB, ODBC,
Oracle,Analysis Server
ReportingReportingServicesServices
Security Translation
When NOT to Use CubesPre-Aggregation Calculations▪ Allocations▪ Allocations
Listing Reports▪ Data Dumps
Write BackWrite Back▪ Budgeting
Business Modeling▪ What-If Scenarios
©2010 Symmetry Corporation http://www.symcorp.com
3rd Party Options
©2010 Symmetry Corporation http://www.symcorp.com
PowerPivot
©2010 Symmetry Corporation http://www.symcorp.com
Symmetry – Who We Are
ExperienceExperienceFounded in 1986 Founded in 1986 Qualified Consultants: Qualified Consultants: minimum 15 minimum 15 ExperienceExperience Qualified Consultants: Qualified Consultants: minimum 15 minimum 15
years BI, 10 years MS BIyears BI, 10 years MS BI
ExpertiseExpertiseBI is our SpecialtyBI is our SpecialtyBusiness Solutions, not TechnologyBusiness Solutions, not TechnologyConsulting, not ContractingConsulting, not Contracting
EngineeringEngineeringADAPTADAPTTMTM Design MethodologyDesign MethodologyPROOF Application FrameworkPROOF Application FrameworkF t t & B t POC/D /P t tF t t & B t POC/D /P t t
©2010 Symmetry Corporation http://www.symcorp.com
Fastest & Best POC/Demo/PrototypesFastest & Best POC/Demo/Prototypes
Symmetry & Microsoft
“T h l ”“T h l ”“T d”“T d” “Technology”“Technology”“Trusted”“Trusted”
Licensed technology Licensed technology
PremierPremierBIBI
Licensed technology Licensed technology to SQL Server groupto SQL Server group
Basis of SSABIBasis of SSABIBIBI
PartnerPartnerEmbedded withEmbedded withPerformancePointPerformancePoint
Frequent speakingFrequent speakingengagements at engagements at
“Teach”“Teach”“Teamwork”“Teamwork”
dev team for 2 yearsdev team for 2 years MS conferencesMS conferences
©2010 Symmetry Corporation http://www.symcorp.com
TeachTeachTeamworkTeamwork
To learn more or attend a meeting: www.bayareamicrosoftbi.org
To inquire about sponsorship or speaking opportunities:
Mark Ginnebaugh, User Group Leader [email protected]