Skip to Content
Author's profile photo Arun Varadarajan

Are SQL editors relevant in an SAP BI world ..?

Monday Morning … you get a question from one of the users who wants the aggregated sales by material group… This is just a one time requirement which the user wants for some presentation to be given to someone and he(or she.. not too gender biased …)  wants this information yesterday ( as always… )

In a SAP BI world …. ( in a typical world with a lot of processes … )
The question is decomposed into a query that can be run… in our case lets assume that this information is also available in a DSO … lets say ZCSALES is the cube for this information and ZOSALES is the DSO for the same… and the information is available in the DSO and Cube.

Once the query design is finalized … the query has to be created but then it is not recommended to create queries in production and you will need to create the same and transport the query from development to production.
When it comes to transports .. you need approvals and QA testing and finally the change goes in after the necessary documentation and processes…

I am not trying to poke fun at processes or try to prove that these processes are not required …. what I am trying to get at is that sometimes processes put in a definite lag in the system so that even small requests like these take a long time to be delivered and in these one off cases …

You either enforce the process which in turn disenchants the person who requested it in the first place or if you decide to make an exception and develop things for the user .. slowly but steadily the exception becomes the rule … ( you want a query … send a mail to person X and mark me in CC .. I will approve and the query can get done in production!!! )

The bad part of course is that the user does not want to use this query later because it was a one off request and the business analyst feels bad that his/her documentation and the pressure of getting it written id double time was not worth the effort

Now lets look at another way to do this…
Every BW system has a Database and this means that the information the query accesses is actually available in a table ( or tables )

What if for this requirement .. you could write an SQl query and give the user the data in about 15 minutes … and since you are just querying the data – you need not create any specific object for this information request …

In this case it could be

select sum(sales), matl_group from zosales group by matl_group 

In this case you could get the data output which you could then put into Excel or a text document and send it across….

**The SQL here is for indicative purposes only … for instance the query should be against the active table of the DSO which is /bic/azosales00 and not zosales as given above ….

** I have chosen a very simple example .. consider something like the user wants all the purchase orders for materials whose sales is greater than 1000 and in this case you would have to do a lot of development to deliver BW Objects and it would not be worth the effort if this was for a one off requirement…

Sounds easy right..? but then there are pitfalls….

1. Security

If you have secure areas in the database like finance information – then this kind of access would not be something that is recommended – especially since you are going to access the same through a different tool – unless you have a very good team of DBAs and setup security at the table level based on the user ID ( not sure if this is possible though )

2. Accessing Cubes

Accessing cubes is a bit sticky – since you have to access the fact table and then joining the dimension tables based on the DIMIDs and then join the SID Tables….

3. Performance

Unlike BEx you do not have any OLAP processor , cache etc – it is a plain relational query that is being fired… think twice before triggering a massive query which takes way too long.

4. Editing rights

Depending on how the user IDs are configured – you can get write permission on the tables as well – make sure that you do not give this to users unless absolutely required … users can be creative and you might have some data entered into the tables and set off alarm bells for SOX

5. Cannot use accelerators

Since you are hitting the tables directly even though you may have BW Accelerator indices on the DSO ( post BWA7.3 ) you cannot use them.

6. Educating people about SQL Use

You do not want BASIS to come at you with firearms because you executed a select * from on a table with 15 million rows …

7. Managing DB Sessions

It is dangerous especially when you have someone who wants a dump of a very large table and decides to do a select * from and then exports it to a text table from the desktop…

8. Nature of access

You actually do not need an SQL client – you can do the same from SQL Plus which can be accessed from the command prompt but then editors are so much eaiser to work with…

In all SQL editor access to the BW tables is something that might save you a lot of time and increase the responsiveness of the IT team if used properly … and if not used properly then you might find yourself on the wrong end of the line with possible licensing issues and a lot of BW issues relating to data that was not handled properly…

Given the constraints – do you think it is worth giving this level of access to a small team of SQL aware developers and possibly decrease response times ?

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      That's the problem and the solution. I don't recommend to let every user make queries in production system. All depends on the company and the way people use BW. But in the end having a power user with rights to create queries in production and using a intelligent authorization concept this shouldn't be a problem from my point of view.
      Starting to give people an SQL editor is like opening Pandora's Box.

      Best regards,

      Author's profile photo Ethan Jewett
      Ethan Jewett
      Hi Arun,

      It is an interesting question if there is a use case for this approach, but I'm going to have to agree with Peter here. Routing ad-hoc analysis requests through IT resources with an SQL console and access to the underlying DB seems like not the best way to accomplish this. Yes, you bypass one process, but you are still dealing with a cumbersome Line of Business -> IT -> Line of Business process when what we really need is a self-service solution. Even just in native BW, more viable options would be:

      1. Allowing certain power users in the line of business to create queries to service these ad-hoc requests. (Peter's suggestion)

      2. Giving access to APD for line of business power users.

      3. Creating queries on analysis cubes (or even standard DSOs) that contain all characteristics in free characteristics, allowing a user to create this query on their own in BEx.

      Introducing the BusinessObjects tools introduces another level of self-service, though to be fair it is based on the type of query defined in option 3 above and mostly provides a nicer user interface (super important!) rather than significantly improved functionality.

      Food for thought.


      Author's profile photo Former Member
      Former Member
      I'd love to see a SQL step to be added to process chains, allowing one to write native sql in it and use it as a source for a transformation.
      This can give a huge performance boost.

      Also, infosets should be more similar to SQL views, specifying the definition in plain old SQL would be great and allow to take advantage of the more advanced features like "window functions" (e.g. rank(), connect by )

      Author's profile photo Arun Varadarajan
      Arun Varadarajan
      Blog Post Author
      I believe that this can be done to some extent if you have tools like data federator which allows you to write custom SQL in the transformations...
      Author's profile photo Former Member
      Former Member
      What about MDX Queries. It checks authorization access. There are UI tools which can help in building the MDX syntax.
      Author's profile photo Former Member
      Former Member
      Hi all

      Just use Option 3 as Ethan suggests and on top create an OLAP Universe and then use WebIntelligence. Exactly for this scenario, WebIntelligence is built for (Self-service BI).

      Cheers, Roger

      Author's profile photo Arun Varadarajan
      Arun Varadarajan
      Blog Post Author
      We implemented this approach in one project where we had all the charactersitics in the free characteristics and allowed users to create saved views and then we created a BSP page which would list all the saved views by user so that they could execute their saved views directly instead of generating their queries again...
      I had mentioned this approach some time ago in
      Expose Saved Views on Web
      which could be used to give users ad-hoc query view creation ...
      The main advantage of giving SQL access is that if you have a one-off requirement to join multiple datasources like combining Sales and PO to generate some numbers and you do not have anything that satisfies the same in your BW environment.
      Also in some cases - the user is just trying to see if it makes sense to get data together like this and the effort it takes to join these objects as multiproviders / infosets etc is higher than the benefit .
      Another main advantage is that you can have more SQl aware people as opposed to BW developers who can help out and find their place in the IT team despite not being 'BW' aware....