cancel
Showing results for 
Search instead for 
Did you mean: 

WEbi MicroCube

Former Member
0 Kudos

I need to understand how data is stored and read from the microcube in BO for a webi report.

If say I have two queries and they have identical dimensions but I merge them does this mean that BO would be able to run parallel process and read the data from two microcubes and do a join? Or is it that the data is joined into one micro cube ?

what is best practice when you want to read data from Multicube for prior year and current year data if you only need actual data for PY but act,Bud and forcast for the current year ?

I cannot work out what is better from a performance point of view - one combined query in Bex or two quuries in Bex ?

Any thoughts to share ?

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Matthew_Shaw
Advisor
Advisor
0 Kudos

Hello Balbir,

A single Web Intelligence document can contain zero, one or many data providers.

A data provider can be a:

- query, based off a universe (universes can be based off Relational or OLAP sources, and also Stored Procedures)

- query based off BW query (connects using BICS. Actually a universe is created/destroyed every time)

- a local data source (like XLS, CSV file)

- a web service

(last 2 can only be created on the Web Intelligence Rich Client, but can still be refreshed on the server)

Each data provider returns a set of data. This data is held in at least one microcube.

(Sometimes multiple queries are generated, from a single 'query panel' and multiple result sets are returned. Depending upon how well the data relates to each other, one or multiple cubes are actually created. This really only happens for relational sources and the multiple cubes are typically 'joined' or 'synchronized' together. This is completely transparent to end users. End users don't need to worry about 'joining' data sets (cubes) together.).

A 'cube' is the perfect structure to store the data, as it allows very quick 'projections' (or aggregation) of data when displaying/analyzing in a report. Whilst the 'result set' from a database may be 'flat', as the data is returned its stored in a cube. There's no 'load time' it just happens as fast as the data is returned. These microcubes are used for all data sources, relational, local files, web service and OLAP.  Microcubes are completely transparent to the user. They are stored directly inside the Web Intelligence document (.wid).

If your source is a BW query, it will almost certainly just return one result set, and that result set will be stored in just one cube. If you have 3 data providers, you'll have 3 microcubes.

Each data provider is refreshed against the data source in series, one after the other. You can change the order. Sometimes the product will determine the order, because you have defined one query to be based off the result set of another.

As mentioned earlier, a single data provider can generate multiple queries, but these are limited to relational sources. Typically this is the case when querying multiple fact tables. These multiple queries, by default, are also executed in series. However if the multiple queries are 'joined' (rather than 'synchronized') they can be run in parallel by asking the RDBMS to do the join instead of Web Intelligence. You just set ANSI92 and JOIN_BY_SQL to Yes within the universe parameters.  (if you're an Enterprise Support customer, you can attend the 'Universe Optimisation and Best Practice' Expert Guided Implementation and learn all about this https://service.sap.com/~form/sapnet?_SHORTKEY=01100035870000717440&_SCENARIO=01100035870000000202)

When you create multiple data providers off the same universe (inc BW via BICS) source, the common dimensions are automatically joined. There's a setting to disable this if you want, somewhere in the options/preferences. Its a good idea to limit the number of data providers. Too many makes it complex, increasing ownership and maintenance costs. 'Forcing' users to create multiple queries to answer simple business questions like comparing Actual, Forecast and Budgets should also be avoided. Users shouldn't really need to learn of understand complexities like 'merging' cubes, though it is a great and simple user interface! The data source should be designed to allow the user to ask for all Actual, Forecast and Budgets within one query. For relational sources where this data is held in multiple fact tables, you need to define contexts. For OLAP sources, including BW, you need to expose the measures appropriately.

For you, it looks like this is fine as you can query all Actual, Forecast and Budgets within one query. Great.

For common comparisons of measures like 'this year' and 'last year' then its a good idea to create 'duplicate' or 'dedicated' measures for this. It makes it easier for users to consume and eases document complexity. You don't want a measure of 'every year': Revenue for this year, Revenue for last year, Revenue for the year before last... that would be a bit too much. So there's a balance between making the selection easy for users and maintaining the OLAP structure/universe objects. A measure for 'this year' and 'last year' should fit most use-cases. You'll end up with a set of objects for 'this year' and set of 'last year'.

When a user needs something else, then you consider alternatives. This would mean creating another 'set' of objects that isn't for any particular year. The user would have use the 'Year' object to display or filter the data to a particular year. To create this separate set of objects depends on many things, could be a new universe (new BW query), or just a modifications to the existing universe or BW query. You need to think carefully of the implications and flexibility of these options.

So here you have the option. You either have 2 queries, one for current year and one for last year. The common dimensions will be 'joined' within the document and you can display and calculate the difference between the 2 years. These queries will be run in series, one after the other. OR you just have 1 query and do all the 'work' in BW or in the BW query.

Tricky to answer the 'performance' question as every case is different, but on the whole I would say you'll better off doing as much in the back end as possible. Best would be in the BW cube, then next best BW query, least best multiple data providers in Web Intelligence. But you never know!

Former Member
0 Kudos

Hi Matthew Firstly,thank you so much for your detailed response and explanation, and for taking the time putting it together. It is greatly apprecaited. Its guys like yourself, Henry and Simone (all have given me awesome responses) that really make a difference in SCN (of course amongst others too) Reading your reply and also one from Henry (http://scn.sap.com/thread/3207816) I believe I will be implementing one query on the multiprovider and will be doing as much as the calculations as possible in Bex. I will provide the required measures in Bex even though there could be duplciation I too believe it making it easier for the users to be able to use/understand the universe structure with ease. I will also, where possible, combine the data in BEx and avoid the joins on the BO side (Merge Dimensions). In henry's post he has given a link to a great webi performance tuning document that will also help. I will also share this post and provide a link in Henry post mentioned above as they both are related. once again thanks very much. Balbir

Answers (0)