Analysis Services cubes ontwerpen voor Excel PivotTables (2)

Kort geleden wees ik al op het bestaan van een whitepaper over het ontwerpen van Analysis Services cubes voor gebruik in Excel 2007: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables. Het whitepaper is in mijn optiek een zogenaamd level 200 document (niet al te veel technische diepgang). Toch wil ik er twee onderwerpen uithalen die erg zinvol zijn.

PivotTable Field List measure group drop-down list

In een Analysis Services cube kunnen heel veel dimensies zitten en meerdere measure groups, één voor iedere fact table waaruit de gegevens voor de kubus afkomstig zijn. Per combinatie van iedere dimensie en measure group kun je op de Dimension Usage tab van de cube designer aangeven of die dimensie gerelateerd is aan de measure group en zo ja, tot op welk detailniveau. In onderstaande afbeelding zijn aan de Sales Targets measure group slechts een drietal dimensies gerelateerd waarbij de tijddimensie niet verder dan kwartaalniveau gaat.

Gebruik je een kubus in Excel waar veel measure groups in zitten en veel dimensies, dan wordt de PivotTable Field List al snel onoverzichtelijk en is het heel eenvoudig om ongeldige combinaties van measures en dimensies te kiezen. De PivotTable Field List bevat daarom een handige drop-down waarmee je één measure group kunt kiezen waarna de field list alleen nog de measures én dimensies laat zien die gerelateerd zijn aan die measure group.

Member properties in het rapport

Wanneer je in Excel de eigenschappen van een dimensie member wilt tonen (bijv. kleur, maat en prijs van een product), dan ben je snel geneigd die eigenschappen in de PivotTable Field List aan te klikken.

Als je dat, zoals in bovenstaande afbeelding is gedaan, met een aantal eigenschappen doet, geeft dat niet de gewenste rapport layout, maar nog veel erger, de performance van het rapport wordt dramatisch.

Wat je doet bij het op deze manier gebruiken van de field list is een aantal verschillend attribuut hierarchieën uit één dimensie combineren. Terwijl je eigenlijk alleen maar extra eigenschappen van ieder product wilt zien.

In Analysis Services 2000 bestond dit 'probleem' nog niet. Een dimensie kon toen alleen user hierarchies bevatten en de members van een dimensie konden member properties hebben. In 2005 definieer je voor een dimensie attributes en ten behoeve van de navigatie user hierarchies. Het default gedrag is vervolgens dat voor iedere attribuut een attribute hierarchie wordt gemaakt; eigenlijk hetzelfde als een user hierarchie, maar met slechts twee levels: een totaal en het detail niveau van de attribuut. Een mooie feature want hierdoor wordt het heel eenvoudig te analyseren hoeveel zilverkleurige producten we hebben verkocht of hoeveel producten er in de maat XXL zijn verkocht. Maar als je eigenlijk wilt analyseren hoeveel mountain bikes er verkocht zijn van een bepaald type met als extra informatie eigenschappen als kleur, framemaat, etc., moet je niet deze attribute hierarchies gebruiken maar de good old member properties. Die zijn er nog steeds en ontstaan middels de veelbesproken attribute relationships. In Excel kun je die vervolgens via twee user interface elementen weergeven.

De eerste optie is met de rechter muisknop op een dimensie member in de pivot table te klikken en in het popup menu Show Properties in Report te kiezen, gevolgd door de gewenste property.

Als je dit voor een aantal properties moet doen, is dat niet zo handig. Daarom is er nog een manier: op de Options tab in de ribbon op OLAP tools klikken en vervolgens op Property Fields… Het volgende venster verschijnt dan:

Hierin kun je eenvoudig aangeven welke properties je wilt zien. Het resultaat in het Excel rapport ziet er nu netjes uit en geeft een véél betere performance dan wanneer je hiervoor attribuut hierarchieën probeert te gebruiken!

Share this