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

Tagged: , , ,

This topic contains 0 replies, has 1 voice, and was last updated by  Frank 1 year, 6 months ago.

  • Author
    Posts
  • #473
     Frank 
    Keymaster
    • Topics Started - 52
    • Replies Written - 47

    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.

    xls example using Cube functions

    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 1 year, 6 months ago by  Frank.

You must be logged in to reply to this topic.

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending

©2019 KLEO Template a premium and multipurpose theme from Seventh Queen

Log in with your credentials

or    

Forgot your details?