Direct Database Requests

Direct Database Request allows one to run SQL statements directly on to a data source. The BI Server sends across user entered physical SQL statements directly to the connected data source.

Log on to Answers. You should have the privilege to perform a Direct Database Request.

To check whether you have privileges, go to Administration-> Security ->Manage Privileges.

If you are part of the group mentioned here, you have privileges to execute Direct Database Requests.

Now, let’s start creating one!

Click New -> Analysis -> Create Direct Database Request

Once here, mention the connection pool you want to use as mentioned in the rpd. 

Once this task is completed, write down the sql query you wish to execute in the SQL Statement tab.

As can be seen, the SQL query is written as you would write at the back end.. However, if we need to use this report in a dashboard which requires prompts to be applied to it, we need to create presentation variables.  So in this particular case, we are using presentation variables for name, region and product. In this particular syntax we have equated the required columns to their respective presentation variables. What we have mentioned after the presentation variable is the default value that will be displayed on taking up the report before passing the prompt.

We may have a requirement wherein we need the report to display all the values without restricting it to the default values in its original view. In this case we need a small change in the SQL entered. The SQL entered will be as follows.

What does this SQL do? When no prompts are passed, the presentation variable is passed as it is. In that scenario, it uses a case statement to make the default value as all values and only take the prompt when it is applied to it.

Once the SQL’s are typed in, click on ‘Validate SQL and Retrieve Columns’.  Once this is done, click on ‘Results’ to view the results.

If you have a revenue column like in our case and want to use the grand total etc in the tabular/ pivot view, you have to make a small change in the Criteria tab. Click on the fx in the measure column. Change the aggregation rule from Default to Sum or Count Distinct or Average as per need.  In our case, it is going to be sum as that is our requirement.

Once this task Is completed, you can view the sum as well.

The other part we have to look into is the creation of presentation variables in the dashboard prompt.

Create variable prompts.

Repeat the process for region and product as well.

Once this is created, we can use this in the dashboard and pass prompts as well.

Cheers,

 Gautam