I would like to continue my discussions on ideal functional/technical skills mix topic. The primary objective of this blog (I hope to give at least one more example in next blog) is to discuss the journey, one, with mixed skills set, should be willing to undertake! This journey, in my opinion, would be more challenging than for someone with one skill. First a few words about my background:
I started as database developer 20+ years ago. At that time, I was fortunate enough to work as developer, DBA and Unix System administrator, all at the same time. During that time, I was also developing Unix Bourne shell scripts. After several years of development experience, I started working primarily as non-SAP Data Warehouse(DW) modeler/DBA for a few years, and then started working as Basis Administrator.
Due to my non-SAP Data Warehouse experience, I started playing an effective role as BI Basis Administrator. I reviewed a lot of BW 2.1c documentation to understand the differences between Star Schema that I used in non-SAP DW world and Extended Star schema SAP uses. That reading also helped me understand SAP’s ETL architecture. Due to this knowledge, I know a bit more BI than a lot of my Basis friends.
As a result of my experiences in the development, DBA space, DW, SAP and BI worlds , I consider myself having 80%/20% skill set, 80 percent on Basis and 20% on BW architecture.
About 15 months ago, BW functional team in one of the sites developed a new solution to replace current solution. The solution would create two ODSs(or DSO, as it is known now) , one for holding historic data and another ODS to hold current and future data. At that time, the functional team’s only concern was to get data loaded. They were not concerned with reporting aspects at that time. Since they were planning to load 100+ million records, the Basis team’s recommendation was not to build any additional indices for two reasons:
- Data load would run slower with additional indices and
- We didn’t know what indices new reports(not developed yet) would need
Everyone agreed, loaded data in December 2008(15+ months back) in QA system, a few months later into the Production system. The functional team’s idea was to run both old and new solutions in parallel in Production for a few months to make sure:
- the data in new solution is consistent with old solution
- the users feel comfortable in using the new solution
The functional team doesn’t believe in testing in QA system so no testing was performed in QA; therefore we didn’t know what additional indices needed to be built from QA system’s data.
(Functional team’s reason for not testing in QA: no valid/adequate data even though this system was copied from Production two years back; with my development experience, I know this is not a valid reason. QA system had enough historic (almost as much as production) data so historic ODS could have been tested at the minimum)
The production system was holding same data for two solutions since July last year. We were under the impression that the users were testing/validating new solution since July. I can’t think of any reason why someone would load data into the Production system and not test for more than 7 months. Having two copies of same data is an overhead. All of us know that or that’s what I thought. So Basis team was looking forward to working with them to make sure new solution is implemented ASAP. This would help them drop old solution and release about 200GB of disk space (25% of the database size!). However no issues on new solution were brought to Basis Team’s attention so everyone assumed everything was working flawlessly:).
Fast Fast Forward….
Just recently we came to know that no one was using the new solution. What was the purpose of holding two copies of same data in the Production system is for another blog. For now I would like to focus on topic which triggered writing this blog.
A week ago, Basis team received an email stating that one of the queries was running slow in that site’s sandbox which was copied(system copy I mean) from our production system about a month ago. That query was accessing one of new ODSs and didn’t have an appropriate index. Immediately the Basis team recommended defining the index in RSA1 and transport it to other systems. Since that ODS had 100+ million records, the Basis team would build the index in the background using Oracle’s parallel clause (CREATE INDEX …. PARALLEL 10; after the index is built, we would alter the parallelism to 1, default value) before the transport is imported. Since SAP doesn’t support Oracle’s parallelism while building the index, we wanted to build it outside SAP. We wanted functional team to define the index in SAP so we would meet the following objectives:
- The indexes would be visible to everyone (I mean anyone with access to SAP Tcodes SE11 and SE14 could check the details on the indexes)
- SAP and Oracle would be consistent. This would be a great benefit during the system upgrade.
- We would be following SAP’s best practices.
More importantly Basis team thought defining an index in RSA1 is a very simple task. (Note: Whoever has worked in BI would know the ODS indexes can’t be defined in SE11 as SAP creates them in $TMP package.) However the functional team thought otherwise. They thought since ODSs contain several million records, defining an index in RSA1 would corrupt data! Since index definition would necessitate ODS activation, they thought the activation process might corrupt data (Fear Factor?)! Since the issue appeared to be lack of experience, we suggested testing the solution in QA system since it is not used for testing anyway! They were not open to that idea. Since they showed a sense of urgency almost 15 months after loading data in QA system, the Basis team decided to create the index in Oracle.
Currently SE11 and SE14 don’t show the index we created in Oracle. So in order to see them, one needs to login to the Unix server, login to sqlplus and execute SQL command on all_indexes, all_ind_columns etc. Not only we can’t see everything in one screen/format but also would require additional steps to see the details on indexes in a format bit difficult to understand. As you all know, not everyone has access or know-how to access unix system so this information is available only to a privileged few.
In addition, that index also shows up under “Unknown Objects in ABAP dictionary” in ST04. Without resolving this issue, we would not be able to proceed with upgrade in future. And only option (unless you have other ideas) we will have during the upgrade is to drop the index, upgrade the system and then remember to build them as post-upgrade procedures.
As you can see, there is a conflict between the functional team and the basis team. The reason for this,I believe, is because the basis person has 80%/20% skill mix; the functional team can’t see the benefits of keeping ABAP/Oracle dictionaries consistent because they don’t have basis knowledge. And the functional team uses the fear factor (ODS activation may lead to corruption and data wouldn’t be available to users) to not follow the best practices.