( Geek2Live.net )

Encouraged by my good friend Dallas Marks, I thought I would actually contribute to my blog for once and discuss the new SAP certification I achieved last week.

The official certification is ‘SAP Certified Support Associate – Incident Mangement with SAP BusinessObjects’, and details on the SAP website can be found here.

So what is the certification for? Well sorry if you are an SAP customer, because this certification isn’t for you personally – its for SAP partners only. It is a certification SAP have put in place to ensure quality support is provided to you by partners. The partner that I now work for, ASG Group, run an SAP-certified support desk for existing SAP customers using Solution Manager, so by achieving the BusinessObjects Support certification, we are also now certified to support BusinessObjects customers as well.

I found the most useful resource for studying for the exam was this blog post by Clariba. Having done quite a few other BusinessObjects exams, I found this exam quite straightforward and the study materials provided on the SAP partner portal are adequate to answer the questions. Preferably, you should already be using Solution Manager as hands-on experience helps a lot.

So, if you are a partner consultant and want to provide SAP-certified BusinessObjects product support, then look no further than this certification.

This week I presented at the Mastering BusinessObjects 2010 conference, hosted in Melbourne, Australia.  The conference was organised by Eventful Management, who did a fantastic job.  International presenters included Timo Elliott, Ingo Hilgefort, Dave Rathbun, Rajeev Kapur and Tom Nather.  You can see some photos of the event by Timo here.

My topic was called ‘SAP BusinessObjects XI 3.1 End-to-End Demonstration’, in which I wanted to do a live demonstration of one dataset being used with most of the BOBJ tools, including Data Integrator, Universe Designer, Web Intelligence, Xcelsius, Live Office, QaaWS and Explorer.

Here is a link to the PowerPoint deck, though there’s not many slides.. (Fletcher_EndtoEndSBO_MBO)

I want to provide a walkthrough of the presentation, and include links to the datasets I used.  This should hopefully allow anyone (who has access to the SAP BusinessObjects XI 3.1 environment) to follow my steps.

To begin with, I introduced the dataset I used.  I downloaded it from http://data.australia.gov.au/, and it shows the Number of Crimes in New South Wales, Australia, broken down by Local Government Authority (LGA), Month/Year (over 12 years), Offence Category and Subcategory.  All in all it represents over 1.7 million records, so not a small dataset.  I also extended the dataset by introducing State Region (multiple LGA’s belong to a Region) in another CSV file.

To start with, the first part of the presentation was to use Data Integrator to import data from two CSV files into a SQL Server database, including de-normalising the data into a dimensional model.  You can download the CSV files from the Datasets page of my blog, under the NSW Crimes heading.  You will also need to create a database (I used SQL Server) to host the tables in the Target Datastore.

Here are the steps I following to build the ETL job to move the data from the CSV files to the SQL Server database.

1. Create a new Flat File Format for the ‘NSW Crimes Data 95-08.csv’ (CSV#1) for extracting data
2. Create a new Flat File Format for the ‘Regions and LGAs.csv’ (CSV#2) for extracting data
3. Create a new database to store the extracted data in, and create a new Datastore (I called mine DEMO_E2E_SBO)
4. Create a new Project and Job
5. Within the new Job, create a new dataflow for staging the main source file, using a template table
6. Run the job to stage the data
7. Import the template table and set to overwrite
8. Add a new dataflow for creating the DIM_DATE table, using the Date_Generation transform
   1. Set the date generation from 1995.01.01 to 2008.12.31, monthly
   2. Add the following output columns to the Query transform
      1. Date_SK (julian of Date)
      2. Year
      3. Quarter
      4. Month
      5. Date
9. Add a new dataflow for DIM_LGA, using the CSV#1 flat file format as a source
   1. Add the following output columns to the Query transform
      1. LGA_SK (use gen_row_number function)
      2. State_Region (leave blank for now)
      3. Local_Government Authority
   2. Group by the LGA column
   3. Add in the CSV#2 as another source, combining in another Query transform, with the following changes
      1. Populate State_Region
      2. Make sure the WHERE clause includes LGA=LGA
10. Create a new dataflow for DIM_OFFENCE_TYPE, using the following columns in the Query transform
    1. Offence_Type_SK (use gen_row_num to populate)
    2. Offence Category
    3. Offence Subcategory
    4. Make sure to GROUP BY Offence Category and Subcategory
11. Run the job to create and load the dimension tables, then import each of the dimension tables and set them to overwrite values
12. Create a dataflow for loading the FACT_CRIMES
13. Add in a pivot transform, using the following settings
    1. Pivot sequence column called ‘Seq_Number’
    2. Non-pivot Columns
       1. LGA
       2. Offence Category
       3. Offence Subcategory
    3. Pivot Columns
       1. All month columns
    4. Data field column called ‘Number_of_Crimes’
    5. Header column called’ Month’
14. Add in a query transform, with settings as follows
    1. Add the following columns
       1. Crime _SK (use gen_row_num to populate)
       2. Date_SK – use this mapping = julian(to_date(’01 ‘ || Pivot.Month,’DD MON YYYY’))
       3. LGA_SK – use lookup_ext function to populate with the LGA_SK from the DIM_LGA table
       4. Offence_Type_SK – use lookup_ext function to populate with the Offence_Type_SK from the DIM_OFFENCE_TYPE table
       5. Number_of_Crimes – direct mapping
15. Run the job
16. Import fact table and set to overwrite, and you’re done!

I’ve also exported the entire Target Datastore to an Access DB (also available on the the Datasets page of my blog), as well as exporting the entire job to an ATL file which you can also download.  I created the job in Data Services XI 3.2, so the ATL file won’t be compatible with versions prior to that.

I hope that someone finds the above of value if they are learning Data Services for the first time.  In the next post I will cover the rest of the presentation, so stay tuned!

Service Pack 2 for SAP BusinessObjects Enterprise was recently released, and included some great new features like Web Intelligence Input Controls – see Coy Yonce’s blog post here (needs an SAP login).

However, my frustrations lie in that no SP2 for Edge XI 3.1 was announced.  Remembering back several years, service packs for BOE could be applied to Edge and even Crystal Reports Server, as they were all compatible.

However, it seems that the codebase has started to diverge, or the teams responsible for service packs aren’t coordinated across the different platform offerings.

As a consultant who deploys Edge frequently, this is frustrating considering that there are no discernible differences when using BO Edge compared to BO Enterprise.  Having no programming background at all, I may be swinging wild here, but it seems to me that using license keys to switch features on and off (like clustering, or Federation) would be simpler than diverging what is essentially the same codebase and maintaining separate service pack and fix packs.

I’m not across the success of SAP BusinessObjects Edge in the greater world community, however in Australia it is becoming a very successful platform, and I often need to tell our clients that they have to wait another (estimated) 6 months for a service pack, when the features they require are already available to Enterprise users.

Does anyone else see a need for coordinated delivery of service packs for the BI platform?  Or perhaps someone from SAP can weigh in on why they are delivered separately?

If there are good reasons, I’d love to hear them as most of the annoyance comes from the lack of communication.

– Josh