Is your BW the Data Warehouse, anyway?
There seems to be a big wall between the worlds of BW and “traditional EDW” (whatever it means). Go to the LinkedIn forum of The Data Warehouse Institute (TDWI) and try to open a mouth that you are working with BW – you will be smashed immediately with many comments like “[BW] has always been an ancient, clunky and monstrous solution to implement.”, “I’m yet to come across an SAP customer that isn’t into their Nth attempt at trying to get it to work properly or efficiently.” or “How much did [BW implementation] cost (software, hardware, consulting, etc.), how long did it take and how much frustration did the business users feel while waiting for a solution?”
Same when I talk to my data warehousing colleagues specialized in traditional BI/DW/ETL tools: the only things they want to hear from me are “What are weak points of BW?”, “Why performance in BW is so bad?” and “How can we replace BW with ERWin/Informatica/Netizza/Microstrategy (or any other set of tools)?”.
I had quite many discussions like these on forums and face-to-face, and I had always been on the position to defence BW as a rich, powerful, open and flexible data warehousing platform. But here at SDN I want to openly ask my BW fellows: “Is your BW the Data Warehouse truly?”. Far too often I found that it is not, and here are my thoughts why:
- Roots are in the history of BW as a reporting extension to core R/3. Let’s have a look at the BW purpose as defined for release 3.0B: “The SAP Business Information Warehouse allows you to analyze data from operative SAP applications as well as all other business applications and external data sources such as databases, online services and the Internet.” Yes, it comes with powerful OLAP engine, but no one talked about BW as a data warehouse at that time; it came later, but it’s true meaning is still not recognized.
- IMHO many BW application developers lack data warehousing education. Names like Kimball or Inman are just an empty sound for them. I hoped that Arun Varadarajan would develop discussion that he had Are you an Inmonite or a Kimbalite ?, but the discussion had stopped before even started. Most of BW developers are coming from ABAP, sometimes from other SAP functional area (“BW is hot on the job market!”) or directly from schools. While knowledge of programing language (i.e. ABAP) is much more crucial in BW comparing to other market DW tools and knowledge of FI, CRM or SD source tables is extremely helpful, many BW people have How many skills does BW consultant need? in the data warehouse architecturing, DW data modeling or data quality implementation.
- To previous point I would add a problem of belief in the “Best practices”. Often I had been asked about best practices in specific BW topic, and my reply always was “What are your requirements?”. Still people prefer to hear that there are magic out-of-the-box one-size-ftis-all tricks that turn their BW into the robust data warehouse overnight. I saw when it was enough that consultant said “It is the best practice to code all transformations as an expert routines” and the statement was taken as rock solid (real-life example).
- Unfortunately SAP does not provide many examples of good data warehousing structures either. BI Content is simply not following data warehousing principles: not designed for performance, not designed for multi-layers architecture, not designed for integration of data from multiple source systems. Even much promoted SAP Layered Scalable Architecture (which I am a big fan of) is not reflected in SAP-delivered BW objects from Business Content.
- Another problem is that there is no SAP methodology for BW implementations. ASAP was not the right methodology for BW and trying to deliver BW projects accordingly to it was like trying to fit a cube into a round hole. I have hoped that after acquisition of Business Objects SAP will develop BO’s “The BI Solution Accelerator” to the next level as SAP’s own BI/DW implementation methodology, but I do not see this happening so far.
- Anywhere else I haven’t seen such a big disconnect between two groups: application developers (i.e. BW apps developers) and system administrators (i.e. Basis) as it is in the world of BW. It is getting even worse when customers are outsourcing BW development to one company and Basis to another. To squeeze optimal performance from BW you need these two groups work together in constant dialog right from the beginning. My favorite example is when BW consultants are tuning OLAP Cache in RSRCACHE to 200MB, while Basis keeps system parameter for available export/import memory in RZ11 at default 4MB. And untill these two groups start talking to each other, BW data warehouse performance will suffer giving us bad publicity in the outside world.
Net, it is as well up to us to fix the perception of BW as non-relevant data warehouse in the world outside SAP and at the same time to join and influence that EDW world as an equal partner.
you are so right with many things. I think, most of the doubts on SAP BW come from bad education and therefore bad implementations on the one side. On the other side people often see just old releases or heard from them and don't know what a BW can do today.
Sometimes I think the way to SAP is to far from project to development.
Regards,
Peter
The comments around expert transformations are truly scary.
I couldn't agree more that ASAP does not work for BW. My experience is rapid prototyping works better, along with documenting and formal technical reviews of models.
The comments about Basis/BW working together as a team are so true. Who's responsible for performance? Teamwork between the two groups is needed.
Great blog, Vitaliy, and you may have inspired me to write more about this.
Tammy
If standard content changes to reflect more of an EDW approach, I think this way of designing BW environments will propagate thru BW developer community.
I know from my current customer BW can be an EDW. We run 12 TBs data supporting almost all business functions.
Keep up the good work.
Dae Jin
Great blog. I agree with all points, especially 3. I have faced that same situation so many times and it's funny we have the same answer "What are your requirements?".
It is such a shame BW real value is not appreciate mostly because of bad projects including consultants and people without the proper knowledge but as you said, it is up to us to change that perception, little by little whenever there's an opportunity.
Keep the good job!
Luis
I agree with many of your thoughts. We teach BI at Victoria University. The subject includes extensive "hands on" SAP BW.. The things we thought were important to include in addition to building a BI reporting environment was data modelling, reporting strategy, knwoledge discovery and EDW. We want our students to enter the marketplace with more than knowledge of just how to construct a SAP BI environment. We want them to have an impact.
The subject culminates in a practical exam where students need to construct a BI environment based on supplied specifications.
In terms of EDW, I think SAP were starting to push and educate customers about this before BOBJ came a long. When BOBJ arrived the EDW messages changed in the BI environment to more of a BOBJ marketing exercise and EDW faded away.
The approaches BI design and reporting strategy need to developed and promoted to customers.
Paul Hawking
I just updated the text with few more hyperlinks, and would especially recommend those who use LinkedIn and treat BW and DW seriously to join TDWI group: http://www.linkedin.com/groups?gid=45685.
I’m sitting here nodding, as I recognize your points.
I miss one point in your list and that is lack of overview and consistency in the supplied documentation from SAP. I’m afraid that if your point about BI Content was fixed by SAP (a huge job by the way), it would all drown in technicalities and the ordinary developer would never use it.
Have you been thinking about how SAP should fulfil your wish 4? How many existing customers would throw away their existing models? I guess only a very few badly implemented systems would be upgraded. It's hard to fund an upgrade just because your DW isn't compliant with some principles.
And by the way: my BW is a DW, it's not perfect and complete, but it's moving in the right direction.
you're perfectly right here. And yet after 10 years on working with SAP BW I'll have to find a data warehouse that's better along the whole data flow.
One problem is that too many consultants are doing their own stuff and don't look at the other parts of the BW. How often do I hear that a project has its data providers finished before the company decides on the reports they want to see? Or that they want the reports to look like the old ones and don't think about usefulness?
I hope that people stop talking about the reporting tools and start talking about data warehouse design again.
thanks for writing that blog and thanks to all who have commented. While this is certainly encouraging for us in BW dev there is also quite some food for thought and it is helpful when it gets clearly and openly articulated. So thanks for that.
I'd like to throw in a few thoughts that have come up when talking to customers, analysts and colleagues:
1. So here is an equation for the DB-minded people: EDW = RDBMS + X.
This translates into the following notion: an RDBMS is a central component of an EDW but you need something else beyond a pure SQL database. Let's call that X. That X can comprise proprietary programs, scripts, data models, processes, consistency constraints etc. possibly implemented by using a set of tools for ETL, administration, scheduling, data modeling. In the context of SAP, it is X = BW.
2. This means that comparing BW with an RDBMS – possibly one specialized for DW like Teradata, Greenplum or Netezza – is a flawed exercise as all those RDBMS will need to be complemented with quite a number of tools and services to create the "X" for the respective DB platform.
3. An interesting aspect and a good (and frequently unexpected) example for what is part of "X" is consistency inside the EDW. First of all, it is usually mixed up with transactional consistency in the RDBMS (see ACID constraints). However, the latter is simply a mechanism on which the EDW consistency is built. Examples for EDW consistency can be found in many LSA-based designs, e.g. plausibility gates that release data for reporting (or further processing) only when certain conditions are met (e.g. data uploads from all countries for current quarter are completed; all master data has been harmonized; …).
Anyway, it is an interesting and vital discussion. Thanks again for triggering it.
Thomas
Thanks for deepening the discussion! I couldn't agree more with you and many others who left the comments before.
To your points,
#1 Hasso Plattner didn't/doesn't get BW, once in-memory becomes reality for OLTP, his plan is to move the reporting back, so that no extraction is needed, everything's real time.
BW as Enterprise Data Warehouse? I don't think it's a concern/thought for any non-BW person in SAP.
#2 Agreed. Probably for middle management & consultants, the focus is always on meeting the requirement and keeping the system running. EDW is not in their vision. For BW team, EDW is just more work on top of the daily chores. BTW, it's Inmon with an "O". 🙂
#3 What happened to that consultant? I bet he/she is still getting paid prolifically.
#4 Unrelated random thought, why didn't SAP call Business content Rapid Mart?
#5 Thanks for the BO link. I like the "Evolve" phase. Incremental approach works for me most of the time.
#6 IMO, BW administrator, a partial DBA-Basis-BW_architect person, is critical for the success of BW.
Again, great blog, congratulations!
Sandy
PS. As an interesting fact - this blog has been translated into Chinese, and published at http://byinsight.com/blog/?p=18
My thoughts on why there is a big wall between the words of BW and "traditional EDW".
Complexity, BW developers and best practices:
SAP BW is a more complex application. To add fuel to the fire, as you stated, most BW developers didn't have a formal DW education. As a result, BW developers started focusing on the intricacies of SAP-BW to deliver "something" to the business instead of learning DW concepts first. Ideally BW developers should have learnt DW concepts first and then started learning BW. As SAP was under pressure to deliver-because they entered DW world late-SAP also pumped fuel to the fire by delivering "one-size-fits-all" data models(aka best practices). These models probably have not been updated since 2000 to align with the changes/enhancements introduced in the database. Almost every BW developer I worked/working with, follow the same data model for every solution- R/3 to ODS(one per year) to cube(one per year and almost 1:1 relationship between ODS and cube from the number of records standpoint!!!). BW developers don't understand the complexity(number of objects, query properties maintenance etc) of this model. SAP probably introduced this model for two reasons:
1) In 2000/01, DB partitioning was not very popular even though Informix introduced this great feature in 1996 with Oracle introducing in 1998. So earlier versions of BW didn't take advantage of this feature. Even today,SAP is not taking full advantage of this feature.
2) Earlier versions of BW didn't handle large data volumes very well so SAP alleviated this by providing Multi-provider functionality. This in my opinion only added the complexity. I am not really convinced on the advantages of MP in all situations.
So in short, here are the challenges :
1) SAP entered DW market late
2) BW architecture is more complex than other DW systems
3) Lack of "DW practiced/educated" BW developers
4) Hurry to deliver something
5) Worst Practices in the name of Best Practices (I am open to listening why MP is a good concept in all situations. I know there is time and place for everything. I want to know why every BW developer (I worked/working with) implements MP in all situations).
In addition, R/3 users were familiar with R/3 reporting functionality. Even though it is not efficient, due to communication challenges combined with time requirements to implement in BW, they still continue to use that. Even Hasso Plattner is convinced that OLTP systems could support BW requirements when in-memory database becomes reality. Really? People appear to see only one side of the equation when they say disk and memory is getting cheaper. What about data growth? As disk/memory gets cheaper, the data volume is growing at a much faster rate.
I agree there is a disconnect between Basis and BW developers. However I am not sure I understand the relationship between RSRCACHE and export/import parameter. The default is 200MB for RSRCACHE and 40MB(not 4MB) for export/import. If ST02 doesn't show any swaps, should this(exp/imp) still be increased? IMHO, both RSRCACHE and exp/imp belong to Basis from the configuration standpoint (there are other considerations as well, BLOB, Persistence, file, main memory etc). I don't know if increase in RSRCACHE utilization will automatically require more exp/imp. Regardless of whether RSRCACHE is used or not, depending on data model/query/volume of data, exp/imp may require higher value. I wouldn't increase exp/imp to Y just because RSRCACHE value is X. Am I missing something?
Thanks for your time,
Bala
thank you for your contribution. I think such discussions are very important for the community.
I would like to know more about thinks what are 'fundamental DW principles' BW developers doesn't follow.
I would say, I'm a pure BW developer and have no other DW experiences. But I would say I can explain every data model and every transformation I do in BW. But I think it is always and for every professional important to reflect how to work.
Sure, I can read books from Inmon or Kimball and so on. But maybe one time someone can bring these DW principles to the point für us pure BW developers?
Best regards,
Peter
Here is a list of few observations I have made:
1) Source system -> ODS1 --> Cube1(based on year)
ODS2 --> Cube2(based on year)
ODS3 --> Cube3(based on year)
Surprise: The number of records between ODS and Cube is almost 1:1.
DW principle 1: Summarize or aggregate data from ODS and then load to Cube. So I don't understand almost 1:1 relationship between ODS and Cube.
DW principle 2: Load data to ODS only when necessary. One example where you may need ODS: when data comes from more than one source. Mostly this is true in non-SAP environments where several home-developed OLTP systems may feed data to DW. I am not suggesting you wouldn't need ODS when data comes from just one source. In BW, I have (almost) always seen data flowing to ODS first with no supporting documentation. So I don't know whether ODS is a business need or just following the same data model for all situations.
2) Large dimension tables with 1:1 relationship to cube. 65million+ records cube contains 65 million+ records dimension tables. This cube is also used to load another cube in data-mart scenario.
Kimball's recommendation: he doesn't recommend dimension tables larger than 30million records in any situation.
SAP's recommendation: keep the size between 10-20% of the cube size.So the developer didn't follow the recommendation of either one.
3) Data mart: one cube feeds another cube almost 1:1 ratio. What is the purpose of second cube? Again no documentation so I can't explain
Inmon's recommendation: The data marts are treated as sub sets of the data warehouse.
Here is what he says:
Source: http://www.exforsys.com/tutorials/msas/data-warehouse-design-kimball-vs-inmon.html
"Bill Inmon saw a need to transfer data from diverse OLTP systems into a centralized place where the data could be used for analysis. He insisted that data should be organized into subject oriented, integrated, non volatile and time variant structures. The data should be accessible at detailed atomic levels by drilling down or at summarized levels by drilling up. The data marts are treated as sub sets of the data warehouse. Each data mart is built for an individual department and is optimized for analysis needs of the particular department for which it is created."
=====================================
Bottom line: DW design or BW design requires a lot of patience, business knowledge, analytical abilities, DB knowledge for performance, data knowledge etc. So far, as Vitaliy suggested, due to disconnect between Basis(I am basis person by the way) and BW developers, I don't believe I ever had a meaningful conversation with BW developers on design.
Last but not the least, most of what Bill Inmon or Ralph Kimball states, seems like common sense stuff(why build another cube of the same size as source cube in data mart scenario or why keep ODS size and cube size the same etc. if you do, there has to be a strong reason).
The observations I have made are based purely on people I worked with; I hope you understand that. In case any of my statements give you or anyone of a feeling of "general", I apologize in advance.
Thanks,
Bala