SSAS 2005: Cube Performance Tuning Lessons

Intro. <br /><br />A recent project has forced me (which is a good thing) to learn both the internals of SSAS 2005 as well as various performance tuning techniques to get maximum performance out of the OLAP server. It goes without saying that the grain of both your underlying data warehouse's Dimensions & Facts will drive how large your cubes are (total cube space). It also should be a given that Processing Time & Query (MDX) Execution Time usually compete with one another. Given the same grain of a model, the more Grain Data, Indexing, and Aggreggations you process upfront should generally result in a more performant end-user experience (but not always). And while ETL & Cube Processing time is of importance, in the real-world it is the end-user experience (capability and performance) which drives the DW/BI solution's adoption! <br /><br />Throw-out unused Attributes/Optimizing Attributes/Leverage Member Properties <br /><br />The more dimensional attributes you create infers a larger cubespace, which also means more potential aggregations and indexes. Take the time to review with your clients the proposed set of attributes and be sure they all provide value as a 1st class Dimension Attribute. Also, if you find attributes are used primarily for informative purposes only consider leveraging Member Properties instead of creating an entire Dimension Attribute. Also, the surrogate key for your dimensions almost never add business value, delete those attributes and assign the keyColumns of your dimension's grain member (ie it's lowest level) attribute to the surrogate key. If an attribute participates in a natural hierarchy but is not useful as a stand-alone attribute hierarchy you should disable it's hierarchy via the AttributeHierarchyEnabled setting. Finally, be aware that if you have a 'deep' dimension (ie like 19 million members) at its lowest grain, any additional attributes you add will incur much overhead as they have a much higher degree of cardinality. <br /><br />Set Partition Slices <br /><br />The question of whether or not you must explicitly set a partition's SLICE property is clearly documented incorrectly in SQL Server 2005 Books Online (BOL). For all but the simplest partition designs you should consider setting the SLICE property to match the source property (ie the dataset definitions should match across both properties). For those who do not know, a partition's SLICE is useful for query execution purposes, the SLICE tells the formula|storage engine which partition(s) hold the data that is being requested of it. Please see resources section below for more information on this. <br /><br />Optimizing Attribute Relationships <br /><br />Attribute relationships are the developer's mechanism to inform the OLAP server of the relation between attributes. Just like Fact Tables (measure groups) relate to dimension in various ways (Regular, Referenced, Fact, Many-to-Many), dimension attributes can relate to one another in various forms (One-to-One or One-to-Many).Also, you can set the RelationshipType to Flexible or Rigid. If you know your member's change over time (ie reclass), make sure to leave this setting as Flexible, otherwise set it to Rigid. Take the time to thoroughly review your attribute relationships and ensure that both represent their natural hierarchy and are optimized! <br /><br />Scalable Measure Group Partitioning & Aggregation Strategies <br /><br />This is one of the better known techniques but it is still of utmost importance. Make sure to design your measure group's partitions to optimize their performance (both processing and query execution). If your system has a 'rolling window' requirement also account for this in your ETL design/framework. You should almost always partition your measure groups by the DATE_KEY and match the underlying relational data warehouse (RDW) partitioning scheme. The basics of this is your 'hot' (the current period) partition should be optimized for query-execution time via setting a different Aggregation Design as opposed to the 'colder' (older) partitions. Also, if your main storage device (ie SAN usually) cannot hold all of your required data, consider leveraging Remote Partitions to offload the extreme 'cold' partitions to slower storage. <br /><br />Continuously Tune Aggregations Based On Usage <br /><br />Most documentation in the community clearly states the order of creating effective aggregations is to first leverage the Aggregation Design Wizard, enable the Query Log, and then re-tune the aggregations using the Usage-Based Tuning Wizard. What is not mentioned (near enough anyway) is to continuously retune your aggregations using a refreshed Query Log using the Usage-Based Tuning Wizard. By doing so you are ensuring your aggregations are periodically revised based up recent, real-world usage of your cubes. <br /><br />Warming the Cache <br /><br />Another well known technique...by issuing real-world MDX queries onStartUp of the MSOLAP service your cube's cache will be pre-optimized. <br /><br />Be Mindful of Many-to-Many Dimensions Performance Implications <br /><br />While M:M dimensions are a powerful feature of SSAS 2005, that power comes at the cost of query-execution time (performance). There are a few modeling scenarios where you almost have to leverage them but be aware that if you are dealing with large amounts of data this could be a huge performance implication at query-time. <br /><br />Control of the Client Application (MDX): That is the Question <br /><br />A lesser discussed matter yet still very important is how much control you have over the MDX issued to your cubes. For example, Excel Pivot Tables and other analytical tools allow the user to explore your cubes with freedom pending the security (no Perspectives are not a security measure). If you can write (or control) the MDX being issued by the end-user then obviously you have more control to ensure that actual MDX is optimized. <br /><br />Got 64-Bit? Multi-Cores? <br /><br />For enterprise-class Microsoft DW/BI engagements forget about x86/single-core, period. Analysis Services can chew through (process) more data, in higher-degrees of parallelization with x64 multi-core CPUs. Storage|Formula engine cache rely on memory...long-story short, Analysis Services has been designed to perform at higher levels of scalability when running on multi-core/x64 CPUs. Also, be sure to set Analysis Service's Min/Max Thread settings properly for both Query & Processing. <br /><br />Conclusion <br /><br />I am dedicated to life-long learning. I cannot take full credit for my content above as much of this knowledge was the work of others as well as my own testing. The resources section listed below gives credit where it is due accordingly. Take the time to learn and implement highly-performant SSAS 2005 cubes to ensure your project's stakeholder’s first query is a performant one! <br /><br />Resources <br /><br />Mosha Pasumansky's Blog (MDX 'Father'): http://sqlblog.com/blogs/mosha/default.aspx <br /><br />Microsoft SQL Server 2005 Analysis Services (best SSAS 2005 OLAP internals book out!) by SAMS Publishing: http://safari.samspublishing.com/0672327821 <br /><br />SQL Server Analysis Services 2005 Performance Tuning Whitepaper (a great tuning document): download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc <br /><br />HP Solutions with Microsoft SQL Server: http://h71028.www7.hp.com/enterprise/cache/3887-0-0-0-121.html <br /><br />My Friends & Colleagues at Scalability Experts

SSAS 2005: Cube Performance Tuning Lessons

Intro. A recent project has forced me (which is a good thing) to learn both the internals of SSAS 2005 as well as various performance tuning...

Read more »

Article in Norwegian about Office 2007 and MS SQL 2005

Ti fordeler med Microsoft Business Intelligence<br /><br />Microsoft Business Intelligence, som er en fullstendig integrert programserie med server-, klient- og utviklerprodukter gjennomført integrert med 2007 Microsoft Office System, gir deg den rette informasjonen til rett tid og i rett format. Business Intelligence presenterer anvendelig informasjon til personer der de arbeider, samarbeider og tar avgjørelser. Her er de ti måtene Business Intelligence kan hjelpe deg å få bedre resultater ved å gi deg forretningsintelligens i hele organisasjonen.<br /><br />Koble mennesker og informasjon enkelt og effektivt<br /><br />Business Intelligence gjør det enklere for beslutningstakere å få tilgang til og analysere informasjon hvor som helst, når som helst. Oppdatert informasjon er tilgjengelig samme hvor personer arbeider, de kan samarbeide og ta avgjørelser, enten det er på skrivebordet eller over Internett.<br /> <br /> <br />Gi de ansatte medbestemmelse<br /><br />Når analysedata er tilgjengelige og forståelige, kan de ansatte enklere handle slik at det forbedrer ytelsen og støtter den overordnede forretningsstrategien. Business Intelligence inneholder robuste, dynamiske forretningsresultatkort-, analyse- og rapportverktøy slik at alle i selskapet kan ta bedre avgjørelser raskere.<br /> <br /> <br />Enklere samarbeid og deling<br /><br />Forbedre organisasjonens effektivitet ved å bruke Microsofts teknologi for forretningsinformasjon og samarbeid. Tett integrasjon med Microsoft Office SharePoint Server 2007 gjør at du kan dele informasjon på en enkel måte i et sikret, administrert webmiljø med kollegaer, kunder og partnere. Nå har du et sentralt sted der du kan overvåke ytelsesindikatorene, få tilgang til rapporter, analysere dataene i tillegg til å dele dokumenter, chatte på direkten og finne relevante emner. <br /> <br /> <br />Analysere og få kunnskap<br /><br />Med 2007 Microsoft Office System, som er fullt integrert med Microsoft SQL Server 2005, kan brukere få ny kunnskap ved å bruke kjente, brukervennlige verktøy som leveres av en utprøvd, skalerbar Business Intelligence-plattform. Når informasjon er tilgjenglig og lett å få tilgang til – legge til data for å analysere, gjøre beregninger og gå mer i detalj – er brukerne bedre i stand til å analysere og vurdere informasjon for så å ta veloverveide forretningsbeslutninger.<br /> <br /> <br />Forbedre strategier<br /><br />Business Intelligence forbedrer strategien i hele organisasjonen. Bestem strategi, sett mål, overvåk ytelse, utfør gruppeanalyser for deretter å ta veloverveide beslutninger som er i tråd med forretningsstrategien. Ledere kan lett opprette ansvarslinjer i et strategikart og ansatte kan justere målene sine i tråd med bedriftens mål.<br /> <br /> <br />Utnytte fordelene med 2007 Microsoft Office System<br /><br />Forbedringer er blant annet økt kapasitet for interaktiv analyse i Office Excel 2007 for søk, levering og samarbeid i Microsoft Office SharePoint Server 2007, og forbedret visualiseringsteknologi i alle Microsoft Office-pakkene. Business Intelligence er også integrert med SQL Server 2005 Reporting Services og SQL Server 2005 Analysis Services – bransjeledende plattformteknologi for forretningsintelligens – som øker nytten av informasjonen på skrivebordet.<br /> <br /> <br />Levere forretningsintelligens til hele organisasjonen<br /><br />Business Intelligence støtter bredden i organisasjonens forretningsintelligensbehov. Strategisk planlegging blir enklere med kjente verktøy, informasjonsstyring blir enklere i et sentralisert og fullt integrert BI-miljø, og utvikling er mer kostnadseffektivt ved bruk av utviklingsmiljø som er standard i bransjen. Med det brukervennlige grensesnittet til Microsoft Office System, forretningsintelligensplattformen SQL Server 2005 i bakgrunnen og tilpasset utvikling gjennom Microsoft Visual Studio, støtter Business Intelligence alle – informasjonsarbeidere, IT-medarbeidere og utviklere i din organisasjon.<br /> <br /> <br />Redusere opplæringsbehov<br /><br />Med Business Intelligence kan brukere i organisasjonen samhandle med data hvor de vil – i 2007 Microsoft Office System. Ved hjelp av verktøy som er kjente, tilgjenglige og som det finnes støtte for, kan du oppnå en betydelig reduksjon i kostnader og redusere lærekurven.<br /> <br /> <br />Lage avanserte analyser og rapporter<br /><br />Med avanserte resultatkortfunksjoner som støttes av rapporter, diagrammer, grafer og analyser, kan ansatte enkelt spore ytelsesindikatorer (KPI) mot forretningsmålene. Med en forståelse og analyse av forholdet mellom KPIer og forretningsmålene kan du oppnå bedre forståelse av hvordan virksomheten går i dag, ikke i slutten av måneden, kvartalet eller året, når det er for sent å handle og påvirke resultatene.<br /> <br /> <br />Levere forrentingsintelligens på bedriftsnivå<br /><br />Med den fullstendig og fullt integrerte plattformen med SQL Server 2005 i bakgrunnen, leverer Business Intelligence ETL-funksjonalitet (Extract-Transform-Load), elektronisk analytisk behandlingsdatabase (OLAP), datauttrekking, prediktiv analyse og rapportering – alt i én pakke. Business Intelligence som er fullt skalerbar, gir kraft, stabilitet, forbedret sikkerhet og lave totalkostnader for eierskap.<br /> <br />Reference:<br />Microsoft Norway

Article in Norwegian about Office 2007 and MS SQL 2005

Ti fordeler med Microsoft Business Intelligence Microsoft Business Intelligence, som er en fullstendig integrert programserie med server-, k...

Read more »