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
