Sidste nyt vedr. Analysis Services og BISM fra TechEd

De fleste husker sikkert at der var en del diskussion, da de første planer for hvordan Vertipaq (motoren bag PowerPivot) og Analysis Services skulle smelte sammen, blev præsenteret på SQLPASS 2010 i november. Bladt andet markerede Chris Webb sig med et par ret kritiske blog posts der efterfølgende blev forsøgt glattet ud af via et blog post fra Microsoft. Det har derfor været meget spændende om Microsoft har taget kritikken til sig og hvordan de i givet fald ser en mulig løsning.

På TechEd der finder sted i denne uge i Atlanta, har Microsoft offentliggjort det nye Roadmap over fremtiden for Analysis Services.

Kort fortalt, har Microsoft gjort en stor indsats for at synliggøre at den oprindelige MOLAP/ROLAP teknologi også er en del af den fremtidige vision, men at det i forhold til FrontEnds og slutbrugere skal være transparent hvilken teknologi der anvendes. Med andre ord er visionen at udviklerne frit kan vælge om de ønsker at implementere via en tabuler teknologi (Vertipaq) eller en multi-dimensionel teknologi (MOLAP/ROLAP) uden at det påvirker FrontEnds og slutbrugere. Endvidere er der nu en plan for, hvordan eksisterende UDM løsninger kan migreres ind i den nye BI Semantic Model (BISM).

En ting er vision noget andet er praktik… I det kommende Preview release (CTP 3) af næste version af SQL Serveren (Denali), bliver visionen desværre ikke fuldt implementeret. Her vil FrontEnd værktøjerne blive begrænset således at de værtøjer der er baseret på DAX (f.eks. Project Cresent) ikke vil supportere løsninger baseret på en multi-dimensionel teknologi. Microsoft erkender at dette er en uhensigtsmæssig begrænsning, men giver ingen løftet om hvorvidt det vil blive løst i forhold til den endelige version af Denali, der forventes frigivet omkring årsskiftet.

T.K. Anand fra Microsoft, der er Principal Group Program Manager for Analysis Services teamet har skrevet dette blog post, hvor I kan læse mere omkring detaljerne, mens vi venter på at Denali CTP 3 bliver frigivet i løbet af sommeren.

Enabling Writeback to SSAS in Excel 2010

Main topic of last week’s community meeting in ExcelBI.dk was Writeback in Excel 2010.

With Excel 2010 it is finally possible to modify data in a SSAS cube, without using any macros or third party add-ins, and it works like a charm. This opens for a lot of common business scenarios like budgeting and planning directly from Excel 2010.

Setup is very easy. Here some few guidelines on how to enable Writeback to Adventure Works.

Writeback has to be enabled on the specific measure group on which you want to modify data. This can be done from either Business Intelligence Development Studio (BIDS) or SQL Server Management Studio (SSMS). Here I’ll focus on how to do it from BIDS:

On the partition tab of the Cube Designer choose the wanted measure group and right-click on one of the partitions. (Writeback can only be enable on measure groups where all measures has the Aggregation Type set to Sum)

Select Writeback Settings and choose the name for the Writeback table that SSAS will create in the selected Data Source.

After selecting OK, you need to deploy and process the cube, before you can go to the Data Source and review the new Writeback table in SSMS.

One last thing you need to do in BIDS is to setup security to allow Writeback to the end users. This is done on the Cubes tab in the Cube Role Editor, by setting the Access property to Read/Write.

Seen from a SSAS perspective Writeback is now possible from Pivot Tables in Excel 2010.

After connecting a Pivot Table in Excel 2010 to the modified SSAS cube one last thing needs to be done before it’s possible to modify the data from Excel.

What If-Analysis needs to be enabled. This is done by selecting What–If Analysis under Pivot Table Setting in the Ribbon.

Now you can modify the data in the Pivot Table.

A little triangle will appear in the right-bottom-corner of the cell, after data has been modified. When selecting the modified cell, a menu shows up beside the triangle, from which you can recalculate the Pivot Table to show the new totals.

To commit the changes back to the cube (before committing changes will only exist in the Pivot Table) you have to select Publish Changes under What If-Analysis in the Ribbon.

After data has been published the little triangle disappears from the cell, and data it can be found in the Writeback table in the Data Source.

Notice that entries have been allocated to the leaf level of the measure group.

In Excel it is possible to allocate entries on non-leaf level in some very interesting ways.

Because all entries will be allocated and written to leaf level of the measure group, choosing the right data model for you Writeback measure group, becomes very important to keep good performance.

Above are both interesting topics that I’ll maybe cover in a future post.