Can I use Stratum data in Excel? Yes! – Insight Examples included

[Frank]

Yes! We just finished up an Insight class on this topic. You can register for these classes right off the homepage of Stratum or by gong to Silvon.com and checking out the Resources section. Also most of the Insight classes presented in the last year or so are available in the Archives section.

I mentioned I’d include some of the excel formula examples out here so here goes…..:

First – to create a connection in an xls sheet you’ll need to get the server, database and cube names for your interation of Stratum. These can be found by opening a view, then open the view explorer and displaying the properties of the view (First thing listed in the VE window – double click on it or right click and select properties). Last 3 things in the properties window are what you’ll need.

The connection type you want to create in excel is Analysis Services – This is in the Data section at the top of the ribbon in excel.

Once you have a connection you can either create a pivot table or chart. Or you can just use the connection in your sheet and use the CUBE functions in excel to reference data in Stratum.

Below is the xls example I used during the excel class, the stuff in green comes directly from Stratum using the division code to get the Division description and the sales and budget amounts for specific divisions. The Month name headers will also come from Stratum and will update automatically as the months change.

The actual Cube functions used are CUBEMEMBERPROPERTY and CUBEVAUE.

The CUBEMEMBERPRPOERTY to get the Division descriptions looks like:
=CUBEMEMBERPROPERTY(“Stratum”,”[Division].[Division].&[“&A7&”]”,(“Div Long Description”))

Stratum=the name of the data connection in excel that points to Stratum (described in the first part of this post)

“[Division].[Division].&[“&A7&”]” = the member we are looking for.
.&[“&A7&”]” – this is the goofy formatting required to get excel to reference a cell – in the case the division code in cell A7.

(“Div Long Description”)) = the member property you are looking for – in the case the division description

The CUBEVALUE function is used to data like sales for specific dimensions, in the case the divisions
=CUBEVALUE(“Stratum”,”([Time].[Year Based Months Based].[Year Based].[Current Year].[Previous Month],[Division].[Division].&[“&$A7&”],[Measures].[Actual Sales Sales Amount])”)

Stratum=the name of the data connection in excel that points to Stratum (described in the first part of this post)
“([Time].[Year Based Months Based].[Year Based].[Current Year].[Previous Month],[Division].[Division].&[“&$A7&”],[Measures].[Actual Sales Sales Amount])” = this is the data we wanted – The Current Year and Previous Month for the division in cell A7

During the class today I showed how you can find all these member and value strings in Stratum by using the Insert Calculated Measure window. Also check out the Stratum Help – search for MDX and you’ll find some good examples.

In not too long we’ll have the video of the class posted and you can also use that as a reference. If I remember I’ll post a link out here to it.

I’ll leave it at that – post here is you need further info or explanation.

• This topic was modified 2 years, 10 months ago by Frank.

[Author]

Posts