Some days ago we (me and a colleague of mine) were talking about a brand new InfoCube we have to develop.
Then I said "OK, let's build a MultiProvider, on top of the new InfoProdiver". And he answered "Why? There's no advantage in building a MultiProvider over a single InfoProvider". At this point I answered "No advantage? According to me there are some good reasons, even if today you can't see any advantage. The main reason is the fact that making this choice today we will have a lower TCO in the future, because you won't have to copy all the Queries from the InfoProvider to the MultiProvider and, as a consequence, adapting all the reporting applications (Web Application, Reports, WorkBooks)!"
We examinated the issue for ten minutes, but I won't report you the exact words, but only a summary that should explain the answer I gave to my colleague.
I think the business scenario introduced it's quite familiar to you: you are going to create a new InfoProvider. According to me you should consider the farseeing possibility to build a MultiProvider that will be used to provide data to all the Queries, rather than building Queries directly on the "basic" InfoProvider, expecially if you think the amount of data will be relevant. The main reasons for this choice is the fact that number of data will increase over the next years. I guess you already know index management, compression, aggregates functionalities as key factors to guarantee performances. But what about Logical Partitioning? There are two kind of partitioning in SAP BW/BI:
- Physical Partitioning: is a DBMS functionality that can be managed, for an InfoCube, using 0CALMONTH or 0FISCPER Time Characteristics in order to create (in the DB not in ABAP DDIC) smaller chunks of the Compressed Fact Table (E-Fact Table). Obviously this functionality gives good results only with compressed data. Sometimes I call this “Low level” partitioning, because is realized on the DB Level.
- Logical Partitioning: is a Workbench solution that consists in creating two or more InfoProviders, each one associated to a set of data (Division, Region, Companies, Time …) depending on one (or more Characteristics. All the involved InfoCubes are then included in a MultiProvider that realizes the UNION. I call this “High level” partitioning, because is realized on a higher level.
Introducing Logical Partition when you have created several Queries has a cost: I know you can use Transaction RSZC – Copy, but what about Web Applications, Reports, WorkBooks and so on? A lot of time has to be spent to "adapt" all these applications to the Multeprovider scenario. Typical scenarios that can drive to Logical Partitioning, in my experience, are:
Transactional InfoCubes: “old” data are often frozen and therefore can be stored in a “non transactional” InfoCube that isn’t accessed during planning function execution.
Large InfoCubes: that frequently are accessed by a single Company Code, Department, or whatever Characteristic included in the business model … Lets’s say that 80% of the user are interested in one single Department and only 20% on the whole Company!
Model re-structuring: assume you have to modify Dimensions or to review the Logical Model. Generally this leads to a fundamental issue: how long will be the downtime from the end-user perspective? And Who of you will be totally confident in Re-Modeling functionality within a Productive Environment?
Obviously there are other (dozen?) scenarios to be considered … but I think the message is clear to everybody! For more informations about partitioning search in SDN or take a look to the following links:
