I am trying to figure out a good way to some SQL Server data available in BW as an InfoCube.
If I have a MS SQL Server Analysis Services MOLAP cube, can this be made available as a BW InfoCube? I know that BW can use MS AS as a storage mechanism for aggregates, but I have not seen if it is possible to make an existing MOLAP cube available from BW.
I guess the other strategy would be to assess SQL Server via DB Connect and build an InfoCube using the warehouse management BAPIs. This sounds pretty hard to me.
We are building a standard solution so manually creating the InfoCube in not an option we need to do it via vb.net or similar.
Any advice is appreciated. Thanks!
in contrast of the previous replies, you can make almost any data source reportable as a BW infoprovider. You might want to look at the following document:
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sapportals.km.docs/documents/a1-8-4/how to implement a virtual infocube with services
Basically, you need to look into the "virtual infoprovider with services" feature. Internally, we have already build various kinds of such infoproviders, e.g.
- to report live stock data as published on the web
- to make "Foodmart" data reportable
- to expose MS Analysis Server based MOLAP cubes reportable in BW
- to analyze Apache log files
However, if you are looking into a generic way you need to first look into the metamodels and find a generic mapping.
Thomas, BI Development
Thanks for all of your comments - I appreciate your help.
The information that we want to push to BW is actually very, very simple. Its really just a couple of moderately sized tables.
We are trying to make this data from our non-SAP tool available to the BW without going to flat files or something like that. The virtual cube approach is nice because when we publish results to the SQL Server cube, they are automatically visible in the BW world.
We don't know in advance what this data will be used for or what reports need to be run - this will be left up the the client that buys our tool. So we are just looking at the simplest way to get our data into the BW world.
you cannot make existing MOLAP cubes (or any other database tables) available as BW InfoProviders. You cannot even use existing MOLAP cubes as aggregates for existing InfoProviders. If you really want to automatically create InfoCubes from MOLAP cubes you need to call BAPI_CUBE_CREATE from your .NET application. Before this you need to create the InfoObjects, afterwards you need an InfoSource. And I haven't found a BAPI to create transfer rules or update rules, so you would have to do some manual work anyway.
BTW what use is there to automatically create an InfoCube from a MOLAP cube? Creating the InfoCube itself is very easy but the hard work is doing the analysis thatgives you the design. Simply copying an MS AS cube and ignoring the warehouse specifics sounds like the best way into lots of trouble during query design.