13 September 2016

SSAS via Python

After announcing the SQL server on Linux I have realized it would be good to have SSAS on Linux as well. However SSAS 2016 has been released with no new major interesting features, thus I doubt it'll be ported to Linux or at least opensourced to give a chance for being ported by community. Actually MDX could get new functions or improvements also, but I haven't seen any for a decade (or is it just me?).

I have face pretty the same issues with the frontends like Excel. It's pretty powerful: you draw charts, calculate around with its functions, etc. In other words, you reuse the received data with advanced tools. Nonetheless, Excel has its disadvantages I don't like. I'll describe just one of them.

My customers often complain of the performance issue. You can read more about it on Chris Webb's blog. The issue has been partially fixed but not for default behavior. So, if you upgrade your Excel it wouldn't magically be fix itself, alas. I don't consider adding extra optimization (like joining two attributions into one that I used) just in order to set Excel into the right direction. It's definitely an Excel bug, not the OLAP one.

That said, if we rely on the closed source software and face an issue within software, it's embarrassing due tof our helplessness. We, developers, naturally want more control.

I have recently found Bennett Kanuka's blogpost about accessing OLAP cubes with Python. It uses xmla to speak with OLAP what is enough to do pretty anything with the cubes. Well, it's amazing! Here comes an example code from Python interpreter:

Connect to SSAS:

>>> import olap.xmla.xmla as xmla
No handlers could be found for logger "olap.xmla.requests_kerberosauth"
>>> provider = xmla.XMLAProvider()
>>> connect = provider.connect(location='http://localhost/OLAP/msmdpump.dll',username='test_user',password='1234567')
>>> source = connect.getOLAPSource()

Check the cube metadata:

>>> print source.getCatalog("TestCube")
XMLACatalog:(row){
CATALOG_NAME = "TestCube"
DESCRIPTION = ""
DATE_MODIFIED = "2016-08-07T09:47:05.026667"
}

Check the first dimension metadata:

 >>> print source.getCatalog("TestCube").getCube("BI fake").getDimensions()[1]
XMLADimension:(row){
    CATALOG_NAME = "TestCube"
    CUBE_NAME = "BI Fake"
    DIMENSION_NAME = "Date"
    DIMENSION_UNIQUE_NAME = "[Date]"
    DIMENSION_CAPTION = "Date"
    DIMENSION_ORDINAL = "5"
    DIMENSION_TYPE = "1"
    DIMENSION_CARDINALITY = "1554"
    DEFAULT_HIERARCHY = "[Date].[Year]"
    DESCRIPTION = ""
    IS_VIRTUAL = "false"
    IS_READWRITE = "false"
    DIMENSION_UNIQUE_SETTINGS = "1"
    DIMENSION_MASTER_NAME = "Date"
    DIMENSION_IS_VISIBLE = "true"
    }

Check number of dimensions:

>>> print len(source.getCatalog("TestCube").getCube("BI fake").getDimensions())
6

Enter MDX query:

>>> cmd= """
... select
... Non Empty [Client].[Client].[All clients] * {[Measures].[Invoice Count], [Measures].[Invoice Amount]} on 0,
... Non Empty [Date].[Year].[Year].AllMembers on 1
... from [BI Fake]
... """

Execute the query:

 >>> resource = connect.Execute(cmd,Catalog="TestCube")

Get values:

>>> resource.getSlice()
[ [(Cell){
    _CellOrdinal = "0"
    Value = 390664
    FmtValue = "390664"
}, (Cell){
    _CellOrdinal = "1"
    Value = 88766777.13
    FmtValue = "88766777.13"
}], [(Cell){
    _CellOrdinal = "2"
    Value = 1800581
    FmtValue = "1800581"
}, (Cell){
    _CellOrdinal = "3"
    Value = 209878594.34
    FmtValue = "209878594.34"
}], [(Cell){
    _CellOrdinal = "4"
    Value = 2053493
    FmtValue = "2053493"
}, (Cell){
    _CellOrdinal = "5"
    Value = 225361763.06
    FmtValue = "225361763.06"
}], [(Cell){
    _CellOrdinal = "6"
    Value = 2608820
    FmtValue = "2608820"
}, (Cell){
    _CellOrdinal = "7"
    Value = 327210445.23
    FmtValue = "327210445.230001"
}], [(Cell){
    _CellOrdinal = "8"
    Value = 1704502
    FmtValue = "1704502"
}, (Cell){
    _CellOrdinal = "9"
    Value = 220613671.79
    FmtValue = "220613671.789999"
}]]

Get axis:

>>> resource.getAxisTuple(0)
[ [(Member){
    _Hierarchy = "[Client].[Client]"
    UName = "[Client].[Client].[All clients]"
    Caption = "All clients"
    LName = "[Client].[Client].[(All)]"
    LNum = "0"
    DisplayInfo = "65535"
}, (Member){
    _Hierarchy = "[Measures]"
    UName = "[Measures].[Invoice Count]"
    Caption = "Invoice Count"
    LName = "[Measures].[MeasuresLevel]"
    LNum = "0"
    DisplayInfo = "0"
}], [(Member){
    _Hierarchy = "[Client].[Client]"
    UName = "[Client].[Client].[All clients]"
    Caption = "All clients"
    LName = "[Client].[Client].[(All)]"
    LNum = "0"
    DisplayInfo = "196607"
}, (Member){
    _Hierarchy = "[Measures]"
    UName = "[Measures].[Invoice Amount]"
    Caption = "Invoice Amount"
    LName = "[Measures].[MeasuresLevel]"
    LNum = "0"
    DisplayInfo = "131072"
}]]
>>> resource.getAxisTuple(1)
[(Member){
    _Hierarchy = "[Date].[Year]"
    UName = "[Date].[Year].&[2012]"
    Caption = "2012"
    LName = "[Date].[Year].[Year]"
    LNum = "1"
    DisplayInfo = "0"
}, (Member){
    _Hierarchy = "[Date].[Year]"
    UName = "[Date].[Year].&[2013]"
    Caption = "2013"
    LName = "[Date].[Year].[Year]"
    LNum = "1"
    DisplayInfo = "131072"
}, (Member){
    _Hierarchy = "[Date].[Year]"
    UName = "[Date].[Year].&[2014]"
    Caption = "2014"
    LName = "[Date].[Year].[Year]"
    LNum = "1"
    DisplayInfo = "131072"
}, (Member){
    _Hierarchy = "[Date].[Year]"
    UName = "[Date].[Year].&[2015]"
    Caption = "2015"
    LName = "[Date].[Year].[Year]"
    LNum = "1"
    DisplayInfo = "131072"
}, (Member){
    _Hierarchy = "[Date].[Year]"
    UName = "[Date].[Year].&[2016]"
    Caption = "2016"
    LName = "[Date].[Year].[Year]"
    LNum = "1"
    DisplayInfo = "131072"
}]
>>>