ssas coding conventions, best practices, tips and programming guidelines for sql server
TRANSCRIPT
SSASCoding Conventions, Best Practices, Tips and Programming Guidelines
BY-VISHAL PAWAR
Data Source Design Best Practices
Dimension Design Best Practices
Cube Design Best Practices
Partition Design Best Practices
IgnoreUnrelatedDimensions property usage
AggregationUsage Property
Partitioning the measure groups
Use Service Account for Deployments
Eliminate Data type conversions
Enhancing Dimension Processing Performance
Aggregation Design Best Practices
Use only supported OLEDB Providers
Defining default member
partitions to physically segregate active data
SSAS installation To Do’s Tuning Server ResourcesUsage Based
Optimization WizardData Source Network
Packet SizeUse Views as the Source for Cubes & Dimensions
Relational Data Source Design
OLAP aggregations to optimize data retrieval
Handling Dimensions with Many Children
Understanding Member Unique Names
Limitations of Static Named Sets
Highly Normalized – Be Prepared to Pay Price
SSAS Internal Architecture
Edition specific SSAS development using BIDS
Create Grouping Levels Manually
XMLA script to perform certain processing
Analysis Services Aggregation Designs
Moving Calculations to the Relational Engine
Use Views – Provide an Abstraction Layer
use SSAS 2008 Warning Framework effectively
Drill Down for SSAS Use Member Properties
Cloning Partitions and Aggregations
differences between MOLAP and ROLAP
(SSAS) 2012 Top and Bottom Functions
Use Views – Query Binding Dimensions
Role Playing DimensionsSSAS Dynamic
Management ViewsRole of MDX
Creating an Empty Partition
SSAS Flavors of AnalysisSSAS Processing Error
Configurations SSAS linked objects in SSASMachine Learning with
Data mining SSAS instance/hardware resources optimization
Multidimensional is a Dimensional Model
SSAS PerspectivesModifying fact and
dimension relationshipsSSAS ProcessAdd for a
PartitionIsolation, Timeout, and Maximum Connections
DirectQuery Mode in SQL Server 2012
Use Unified Dimension Model (UDM)
Selecting a Recovery Mode for Warehouse
dimension editor for clear presentation
data security using SSAS roles
Use Views – Processing Through Views
Dynamic XMLA using T-SQL for SSAS
SSAS Logistic Regression Data Mining
Combine Multiple Data Sources in SSAS
Tuning Relational Partition Query
SSAS report actions to access reports
Include only Required columns for Processing
a cross tab report is easy in SSAS
Named Calculation and Named Query
Neural Network Data Mining Algorithm SSAS
ASSL- (Analysis Service Scripting Language)
Understanding and Measuring Processing
SQL Server Analysis Services Discretization
Specify attribute relationship type
Fact & Dim direct, referenced, m to m
Turn Off the Attribute Hierarchy, Use Property
Define cascading attribute relationships
DMX- (Data Mining Extensions)
Optimizing MDXProcessing the test
partitionsCreate a ‘Static Speed’
View for testingCreating user defined
hierarchiesAttributeHierarchyVisibl
e propertyAppropriately set
KeyColumns propertyOLAP calculated
members with MDX
Maximizing the Value of Aggregations
Query Processor Architecture
Cache Warming Cube Synchronization Cube ProcessingReferenced relationship
of dimension & groupDistinct count measures
Maximizing the Value of Aggregations
• BI Solution Architect • Blogging @ http://bimentalist.com , http://sqlmentalist.com• Knowledge sharing - 350+ BI Articles ,400 SQL Article , 350+ SQL Script • 4 Complex SQL tool innovator on Codeplex & open source all- https://goo.gl/OI3sB4• Slide share publication - http://www.slideshare.net/VishalPawar_BI• 2 LinkedIn Post - https://www.linkedin.com/today/author/120635304• Free Power BI Course publish on Udemy - https://goo.gl/ArL0qe , 1400 + Student • Leading Global Power BI User Group http://goo.gl/oV7IHz , 1200+ Member , 12 + Leaders • Professional Microsoft BI Trainer Mentalist Network • Founder & BI Consulting Partner of Right Click Info • NMUG Community Founder Leader , Speaker - http://nmug.org/• Microsoft Community Representative for Navi Mumbai region.• SQL PASS Chapter Lead for Mumbai & Navi Mumbai • MCTS: Microsoft® Certified Technology Specialist -SQL Server 2008 R2, BI • MCSA: Microsoft® Certified Solutions Associate• CSM: Certified Scrum Master from Scrum Alliance, US• SQL Server Expert in Architecting and Optimization • BE - Computer Science & Engineering , RAIT , Mumbai• MBA-Information System, Arianth College , Pune
About Me