Alphanumeric Group Counters in DS

I was thinking to write some posts for our blog from the day we launched this blog but due to work pressure and time constraints I was unable to do so. But I feel, in spite of being so busy in my daily work I must take out some time for technical writings as well. This post of mine is a step forward meeting the objectives of this blog. Hope you would like it.

By Mohammad Shahanshah Ansari

You might have come across a scenario where you need to create more than 99 group counters for a particular group. Taking the instance of Task List Object in PM. Task List object has three important tabs and those are Header, Operation and Operation Maint. Package. Header has a field called group counter which is of max two digits length in SAP which means it can’t exceed 99. So if your group counter is less than or equal to 99 then two digit numbers is ideal to use as group counter. But this may not be always the case. What in case you have more than 99 group counters for a group? In that case we have no other option left but to generate an alphanumeric group counter.

How to generate Alphanumeric Group Counters:

There could be many ways and logic of generating the alphanumeric group counters. I would here in this post illustrate one of the simplest and easiest ways of doing it. Since two chars is the limitation for Task List group counter, let’s create two digit alphanumeric GC to cope up with the current requirement of more than 99 group counters. We can take the combination of two alphabets. We have 26 alphabets in English so the no. of combination we can generate for group counters is 26*26=676. I am assuming that your group counter won’t go beyond 676. SAP recommendation is maximum 99 group counters for each group.

Steps to create the Group Counters:

1. Create the header, operation and Maint. Package in task list as usual but in case of group counter instead of generating a 2 digit number for group counter generate three digit numbers using gen_row_num_by_group(Group_Name) function. Group counters are generated for each group separately.

2. Create a lookup table (Permanent Table) to map numeric group counters to its alphanumeric group counters. Your lookup will have alphanumeric group counters like AA, AB, AC, AD, ….AX, BA, BB, BC,..BX…CA, CB....,CX......and so on. You can download the lookup table here or using the following URL which can be used in your program.

http://www.4shared.com/get/z59rzLIn/Group_Counters_Mapping.html

3. In dataflow for Header, Operation and Maint. Package add a query transform at the end to use lookup_ext() function. This function will map the three digit group counters to its alphanumeric group counters.

Your lookup function for a group counter field in query transform will look like this:

lookup_ext([DS_SEC_GEN.DBO.MAP_GROUP_COUNTER,'PRE_LOAD_CACHE','MAX'], [CHAR_GROUP_COUNTER],[NULL],[GROUP_COUNTER,'=',Query_6_1_1_1.Group_Counter]) SET ("run_as_separate_process"='no', "output_cols_info"='')

I hope this information helped. If you know any other option, feel free to share with us. Please do not forget to leave a comment.

(Mohammad Shahanshah Ansari, BODI certified professional is a Data Migration Specialist at Utopia Inc. He can be reached at shahanshah.java@gmail.com)

Read-only repository!

                Often I hop into the BOBJ technical forums lately. Only few topics really seize my attention, others I don’t know much about or I don’t want to plow into it. One such discussion was the possibility of creating two logins for a local repository with one account having the capability to change and other to just read only. Can this be done within the management console? .The answer is no. Finding a solution to this need a basic understanding of the repositories and how it works.

                What is a local repository? .Technically it’s a database contains metadata describing the objects (design components) in the repository. The login information to enter into the repository with the designer is the same as the login information to access the repository DB. So our objective here is clear: We need two logins to the same repository (not at the same time).But only one should be having the authorization to modify the objects and the other should be able to view only. Let’s look how can could implement his.

Here’s one of the solution with SQL server 2005.

1. Create a login called DS_Read_Only_Login in data base server where your local repository exists

2.Associate DS_Read_Only_Login with existing local repository LocalRepo (Make sure that LocalRepo already associated with the login DS_Full_Perm_Login which has the full permission) 

3. Grant SELECT and UPDATE permission for DS_Read_Only_Login to LocalRepo (UPDATE permission is required as when you login the designer, the table AL_USERS gets updated in the local repository.)(How to grant the permission: Object explorer -> Right click on the DB->Properties ->Permission)

4. Open the designer and login using DS_Full_Perm_Login and execute one of the error free jobs. And make sure that its executing without any error.

5. Open the designer and login using DS_Read_Only_Login and execute the same job. You will see the series of "Permission denied" errors!

 Here's the kicker, is it possible for the user with permission DS_Read_Only_Login able to modify the objects, as the permission UPDATE is granted for them? .No

When you modify the objects within the location repository and try to save, the first table which get affected (an INSERT) is AL_LANG.Since we have only UPDATE permission granted for DS_Read_Only_Login, INSERT operation fails and saving the designer objects too.

Hope this explanation serves up the purpose. Please share your thoughts about this post and we are looking forward to hear any other alternative solution.

BusinessObjects Data Services

BusinessObjects Data Services is a complex platform for the data integration process and constitutes the latest version of Business Objects application.

In comparison with previous versions of BO ETL (BusinessObjects Data Integrator), a Data Quality module is the integral part of Data Services.

The SAP BusinessObjects Data Services platform has a modular structure and consists of the following tools:

Data Services Designer

It is a fat-client application that enables the data workflows which are classified in tasks to be monitored. Data Designer helps define connections with the various data sources that are stored as separate data sources (DataSource) and all changes in a given connection are updated once and any other occurrences of data sources will be updated automatically.

BusinessObjects Data Services application offers a number of pre-defined transformations and functional objects that allow modeling of the ETL flows.

Among other, these objects are:

• data merging (MERGE),
• column mapping (QUERY),
• performance of SQL queries (SQL),
• validation of records (VALIDATION),
• comparison of table contents (TABLE COMPARISION),
• Parent-child relationship management
• Surrogate and natural keys generation
• conditional expressions which model the data flow

There is also a possibility to programme specialized functions with the use of the particular script language. Thanks to those functions it is possible to manage data operations, refer them directly to the data sources and communicate with controls and objects.

Any data flow that is already defined and loaded into the repository can be used repeatedly and constantly. Using Data Designer gives the opportunity to design and monitor the executions of the data flows.

Management Console

This application is available from the internet browser (Internet Explorer and Firefox) and consists of the following functional modules:

Administrator

This module is designed for the ETL platform and the application repository management and for scheduling and monitoring ETL jobs. Each of the tasks processed by the ETL server generates log file that is available for a given period of time. The administrator is allowed to access the transformed tasks and check the details such as starting and finishing time of the task, execution time and the status of the data.

The monitoring process can show incorrectness of some tasks (on a log-level) which delivers specific reports of these errors (from the ETL server or database). There is also a possibility of defining our own reports using the script language. ETL tasks can be executed on demand or using a schedule. Schedules can vary from simple to advanced with the use of Data Services scheduler or BusinessObjects Enterprise platform scheduler ( available in the BOE BI portal).

Auto Documentation

This module generates the ETL projects documentations (rft/pdf) with required level of specifications.

Data validation

It allows to preview data validation on the basis of the defined rules and estimation and identification the potential errors or inconsistencies of the data source

Impact analysis and Data Lineage Analysis

This module integrates metadata of the SAP Business Objects platforms from the source to the report. It allows tracing a given raport element or value back to the source system and find out what transformations has been performed to calculate this value.

Operational Dashboard

This module presents statistics and history of the ETL server tasks as functional dashboard. The user has an opportunity to preview the execution status of the tasks with the detailed information in the defined period of time.

Data Quality Reports

This module allows to review the predefined set of reports. Reports are generated during the ETL jobs implementing the Data Quality data processing.

Central Repository

BusinessObjects Data Services solutions are built based on a central repository which is independent from the local repositories of the ETL developers. Without the necessity of installation of the additional modules, it supports the team work in the ETL projects. The central repository allows adding tested scripts and then import them to local repositories or check them out and open them in exclusion mode. Versioning is also fully supported.

Contact Us

You can contact us on bobjexperts03@gmail.com. If you have any technical questions we shall recommend you to use the comment box on the blog. Our regular members and experts on blog will answer to your queries.

About Us

Welcome to the SAP BOBJ DS Experts

This Blog is dedicated to providing you with information on the SAP Business Objects. Everything from great technical articles,tips and tricks in BODI to product, Video Tutorials and book reviews shall be available to help you become the best Business Objects Developer. Our own subject matter experts will attempt to answer your question if our regular members don't get to you first. We do not take technical questions via email. This blog is initiated by Professionals BOBJ Developers from Utopia Inc. If you have a any comment regarding your experience at BOBJ Experts Blog, feel free to contact us at bobjexperts03@gmail.com.