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 ?
