Search code examples
web-servicesobiee

OBIEE: how to set 'is prompted' values in saved filter

My task is to automate testing of OBIEE report data. The main step is to get report's logical SQL.

I have dashboard with reports. Every report has named filter (not inline one) attached. So, I'd like to find a way to set up filter values and programmatically run generation of report SQL (so that WHERE clause is filled in with my values), play it and retrieve data. I have tried the following approaches:

  1. OBIEE WebServices. First, I use generateReportSQL, then call for executeSQLQuery. This approach works fine for inline filters, I managed to set them up in . But I can not get it working with saved filters. How to generate report with values set up for columns in attached saved filter? No information in documentation or in internet found.
  2. Generate Dashboard URL with all prompts set, run it and then read usage tracking tables to retrieve SQL queries. But it seems a bit strange approach, I believe there must be a simpler way to do the task. Moreover, usage tracking does not put information about report execution in its DB immediately, it has some timeout. Is there a way to avoid it?
  3. runcat.sh + nqcmd - still, have not found a way to set values for saved filter.

So, my question is: how generate report's logical SQL with prompt values set for saved filter attached?

Thanks in advance, Jol

UPDATE

Some examples:

  1. XML of my usage tracking analysis contains the following:

      <saw:filter>
 <sawx:expr xsi:type="sawx:logical" op="and">
    <sawx:expr xsi:type="sawx:special" op="prompted">
       <sawx:expr xsi:type="sawx:sqlExpression">"S_NQ_ACCT"."START_DT"</sawx:expr>
    </sawx:expr>
    <sawx:expr xsi:type="sawx:special" op="prompted">
       <sawx:expr xsi:type="sawx:sqlExpression">"S_NQ_ACCT"."USER_NAME"</sawx:expr>
    </sawx:expr>
    <sawx:expr xsi:type="sawx:special" op="prompted">
       <sawx:expr xsi:type="sawx:sqlExpression">"S_NQ_ACCT"."SAW_DASHBOARD_PG"</sawx:expr>
    </sawx:expr>
 </sawx:expr>

I can use filterExpressions tag of generateReportSQL to create logicalSQL that includes my values in WHERE clause. Everything is OK if tag filter is included in analysis's XML (the case of inline filters, as in the example above):

      <v7:generateReportSQL>
         <v7:reportRef>
            <v7:reportPath>report path</v7:reportPath>
         </v7:reportRef>
         <v7:reportParams>
            <!--Zero or more repetitions:-->
            <v7:filterExpressions>            
<![CDATA[<sawx:expr xsi:type="sawx:string" op="equal" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" subjectArea="Usage Tracking">
<sawx:expr xsi:type="sawx:sqlExpression">"S_NQ_ACCT"."USER_NAME"</sawx:expr>
<sawx:expr xsi:type="sawx:string">testuser</sawx:expr></sawx:expr>
]]>
</v7:filterExpressions>
.............................
         </v7:reportParams>
         <v7:sessionID>...</v7:sessionID>
      </v7:generateReportSQL>

  1. XML of my test analysis contains the following:

      <saw:filter>
 <sawx:expr xsi:type="sawx:savedFilter" path="/shared/myproject/_filters/myroject/my saved filter" name="my saved filter" /></saw:filter>

'my saved filter' has 'is prompted' columns that I'd like to set to my values and run an analysis to get dataset. But how to do it?

if webservices are useless here, what could be used?

Solution

  • Since those are normally used for completely dynamic population in terms of content (instantiated variables pulled from user profiles, stuff gotten from prompts, etc....) you won't get them in the LSQL.

    tl;dr - Robin wrote a bice post about load testing with LSQL https://www.rittmanmead.com/blog/2014/03/built-in-obiee-load-testing-with-nqcmd/

    Edit: Baseline Validation Tool (BVT) was proposed and is the answer.