Measures / Sql Instructions again!! 1

[saramaia]

I submited a post already about this issue but i don´t think i explained myself very well..
The thing is I have several measures that exclude each other.. Sometimes i can solve the problem not using the where clause... But sometimes that just doensn´t solve it because it returns the wrong result. The ideal would be generate a different sql instruction for each measure..
Is this possible??? What are my alternatives??

Thanks

SaraMaia

 

[blom0344]

Hello Saramaia,

Without an example this one is bound to be answered in general terms, so here it goes:

1. If your dataset returns one set of objects , you can pay around with combined queries. (union,difference,cross-section) and the query panel gives you oppurtunity to create a complex correlated query with one dataprovider.

2. If input for top-query depents on result of a subquery,you can also handle this just in query-panel with a single data-provider.

3. If conditions differ for different sets of datasets returned you run into the limitation that the where part in SQL can only reference a table once (i.e. the where clause stands for all data returned. You can not solve this by creating universe-objects with where-clauses, cause the net result when using 1 dataprovider still gives you excluding where-clause.

The obvious (well, in BO case) is to split the input for the report into more than one dataprovider. At report level you have the opportunity to link the common dimensions (Data --> View Data) of both (or more than 2) datasets.

Suppose I want to fetch data for a range of customers and use 2 dataproviders. If both datasets contain the dimension 'Customer' BO will automatically link them at report level. Now set object 'Customer'from the first dataprovider as a master and you can show data from the second dataset as well as data from the first dataset within the section.

The matter becomes more complex if you want to show data from both sets in one table or crosstab.
All dimensions used in the table should be of the linked type, otherwise you get a compatibility error message. However you can freely use measures from both sets in the table. The total of information shown should resemble a full outer join between datasets. If you want to show a non-measure in the table that represents a dimension but is not of the linked kind, define it at universe-level as a detail.
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[saramaia]

Hello blom0344,

thank you a lot for your explanation.
I know now what i have to do to solve the problem.

SaraMaia

 

[saramaia]

Hello blom0344,

could you take a look at the example i gave in my other thread "different sql instructions for each measure" (posted in 5 August).
That is the concret problem i´m trying to solve...

I understood what you said about the data providers but how do I apply that concept to my example?

I still didn´t get it...

Thanks in advance,
SaraMaia

 

[saramaia]

Hello blom0344,

could you take a look at the example i gave in my other thread "different sql instructions for each measure" (posted in 5 August).
That is the concret problem i´m trying to solve...

I understood what you said about the data providers but how do I apply that concept to my example?

I still didn´t get it...

Thanks in advance,
SaraMaia

 

[blom0344]

Hello Saramaia,

I had a look at your examples, but I'm not exactly sure what you want.

Case 1.

You want the number of agreements with a certain condition and the total value that comes with it. The result is one row with number of agreements and 1 total value.

Case 2.

You want the agreements with a certain condition and the total value per agreement. In you example you expect 2 rows, one for agreement 1 and one for 9.


Solution 1.

Splitting this into 2 queries and you end up with 1 measure from each query and no dimension at all, so no way to link them at report level. However, both queries return one value, BO has no problem showing them both in one table.

query1:

SELECT Count(agreement)
FROM Table1
WHERE (((Table1.delay)>0 And (Table1.delay)<31));


query2:

SELECT Sum(value)
FROM Table1,Table2
WHERE Table1.agreement = Table2.agreement AND
((Table1.delay)>0 And (Table1.delay)<31));

Does this do the trick (maybe your SQL syntax is a bit different)? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[saramaia]

Hello blom0344 ,

yes.. the solution of using the two queries would be the ideal in this case. But when you say use 2 queries, my question is: when i drag both measures to my report BO generates one only query. That is my problem since the beginning... That´s why my first question was: how can i generate different sql instructions for different measures? One other thing: does BO always uses ONE query to each report?

You talked to me about dataproviders. Do you know any good sites about that matter? I guess i could use a little more information about that... How to split queries in BO...

Thanks you again

SaraMaia

 

[blom0344]

Hello Saramaia,

Try to change from thinking in terms of queries to the concept of a dataprovider. This can be a query (through universe , or free-hand SQL), but it can also be a stored procedure you call from the database or just a flatfile you import.

This is what you should do.
Start with a fresh blank document. You are given the option of 1 of 4 types of dataprovider when you create a new report if you have the wizard activated. If you work with a universe select the universe. In the query panel drag the objects and set conditions which will result in the query you want (check query syntax with the SQL button)
Run this report.
In the report panel choose Data --> New Data Provider. You can now choose to create a new query on the same universe or some other dataprovider. Choose the first and make you second query. Run this query too. Check Data -- View Data in the Datamanager on tab Definition to see if dimensions are linked (in the case of queries only returning 1 measure this does not apply)

If you do not use a universe, but use free-hand SQL use the same procedure, now opting to use the free-hand SQL dataprovider as input.

If you are still puzzled what to do send a mail to me and I'll send you an example.
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[saramaia]

Hello blom0344,

i tried some examples with it, don´t know if i´m doing it right but tell me something. (maybe that example you offered would be good). To a person who doesn´t understand anything at all about SQL, do you think this is a good solution. Because who is going to make the reports are peoplo who don´t know anything about this.. they just want to drag and drop dimension and measures and wanna see it work... I believe the work must be done in designer mode.

There are certainly very heavy reports and manage to calculate everything on BO it´s complicated, right? Maybe a good idea would be preparate data on my database in order to do simple select´s to data field´s..

I guess i´m trying to do to much calculus in BO and i don´t think that work very well, right?

How is it normally done? Prepare views in our database with the information &quot;prepared&quot; and do simple select´s or do all the calculus in BO???

SaraMaia

 

[blom0344]

Hello Saramaia,

Having users that know as much as nothing of SQL is indeed just the reason for using an intuitive tool like BO in the first place. (Reality is just a bit different , though) Doing some heavy calculation stuff on the client side has benefits too. Instead of using CPU time from the heavily charged source system you can use the powerful CPU of modern PC's who are literally 'sleeping' most of the time.
Creating views at the database side is the most userfriendly option. You save your users from complex situations, but it means maintenance on both database and universe level.
I can only speak for my own situation, but most users here want very complicated stuff (make comparisons between ERP environment and warehouse system for instance) and usually I cook something for them that they can modify ( as long as they don't save their efforts , nothing can go wrong)
Running reports on a datawarehouse environment usually will give you an easy time, cause the real data is de-normalised often leading to a single datatable with some definition tables in a star-schema. This leads to a simple universe (not needing such things as contexts e.d.) and hence straightforward SQL usually from one dataprovider.

Don't imagine that you can straighten everything out by expanding your universe with all sorts of additional objects. Start adding objects with additional where clauses and you'll end up with users on the phone complaining that their reports give them no data at all (the accumulated where clause of the query has an excluding effect) The structure of underlying database dictates the nature of a universe and there is only a bit room for bending.

If you want your manager to make up his own reports be sure to have a simple source structure , more often companies have a couple of super-users (quite often those who did their own queries straight at the source) and have them do the dirty work. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com