Tag: Cube

Categories
Business Intelligence SQLServerPedia Syndication

Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

01_writeback

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. 🙂
02_whatif

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

03_valuechanged
04_valuechangedmenu

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

05_publishchanges

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

06_error

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

Microsoft BI: Creating Local OLAP Cubes using XMLA and ascmd.exe

Most people, when using OLAP cubes, are hitting the live version that is located on SQL Server Analysis Services (SSAS). They hit it with Excel, or some other reporting tool, etc. I have blogged previously about offline cubes and how you could possibly use them.

Now, the blog I did before, I talked about creating them with Excel, and that works great for end users. But what about automating the process? What about filtering dimension members, or measure groups, etc?  Now that you can use the Panorama Gadget for Google Apps/iGoogle (http://google-pivot-tables.blogspot.com/2008/04/panorama-analytics-gadget-for-google.html) you can upload .cub files and do BI in the cloud, how cool is that!

506479481_683e31e6db

Well, one option is purchase CubeSlice – http://www.localcubetask.com/  and use that to create your .cub files. CubeSlice works great, and is a good option if you want something with a ton of options and ease of use.

You can also create .cub’s using CREATE GLOBAL CUBE syntax in MDX, and you can also use XMLA to create a .cub. Options galore! Chris Webb blogged a few years about about using XMLA to create .cub’s here – http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry  He was using the MDX Sample App (I believe that comes with SQL 2000?)

What I was looking for was an easy way to us GLOBAL CUBE or XMLA and automate it, CubeSlice is nice, but there has to be a way to replicate (at least in a basic way) what they are doing. Thinking about XMLA – as to me it seems more powerful, that was the route I chose. I didn’t want to install the MX Sample App from SQL2K, and what Chris Webb says in his blog, and other things I read, basically the problem with using XMLA to create you .cub in SQL Server Management Studio is that you can’t specify a connection string, you have to connect to an SSAS instance. Using the MDX Sample App, you can specify a local file, and then run the XMLA and it will create the .cub file. So I just need to replicate that. 

I have also blogged about using ascmd.exe here . ascmd comes with the SSAS Samples with SQL 2005 (usually located here: C:Program FilesMicrosoft SQL Server90SamplesAnalysis ServicesAdministratorascmd) . You just need to compile it and you can use it to execute XMLA. So I decided to try that. I created an XMLA file to create my cube, and then executed it with ascmd.exe

ascmd -S c:MyOfflineCube.cub -i c:MyOfflineCube.xmla

In seconds, you have an offline cube. Now, in your XMLA you can have filters, etc. You could create an XMLA file that has some kind of variable, and you could create a replace task to replace that with what you wanted filtered, think maybe based on location, or employee, etc, and then kick out personal cubes for users, etc.

One thing I didn’t really get into is how to get your XMLA script. You could figure it out for yourself, but I actually just used CubeSlice for this example. You can create an offline cube with CubeSlice and actually see the XMLA code and use that. Maybe creating the XMLA script manually/yourself would be good info for another blog post 🙂

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS Cube Rule #1 – YOU NEED CALCULATE

Ok, I just spent a few hours debugging a cube that wasn’t bringing back any data. Checking all the relationships, the source data, the marts, etc, etc. And lo and behold, find out that a colleague commented out all the calculations, including the CALCULATE statement which says:

The CALCULATE command controls the aggregation of leaf cells in the cube.
If deleted or modified, the data within the cube will be affected.
This command should only be edited if you intend on manually specifying how the cube will be aggregated.

Remember, you NEED the “CALCULATE” command!