Use Data Model value as variable in an analysis

Calin Pavel

Context: I use WCFS (Web Chat For Service) with Oracle Digital Assistant and Oracle Fusion. We have auto-opening created on the chat widget, and the value for '# of Chats' in the 'CRM/Help Desk - Omni Channel Events Real Time' subject area is not the correct one.

I retrieved the number of chats with activity from the customer (Message Type = CEE_ENDUSER_POST) from the SVC_CHAT_MESSAGES table. I would like to use this number in my dashboard, where I can have filters for Chat Reason and Time.

Please let me know if you see this approach wrong.

Nathan CCC

Hi Calin,

View Log on page Manage Sessions /analytics/saw.dll?sessions of page Issue SQL /analytics/saw.dll?issuerawsql for your analysis logical SQL to see the actual physical SQL that was generated to understand which public view object physical table or view in the application database. This will tell you how "# of Chats" is calculated. NOTE to do this your BI Administrator must have used page Manage Pirvileges /analytics/saw.dll?PrivilegeAdmin to grant your user or role privileges Issue SQL Directly and Manage Sessions.

To find an alternative metric then review the Data Lineage to see what presentation table column in your subject area is from which public view object physical table or view in the application database.

https://docs.oracle.com/en/cloud/saas/otbi/fa-index.html

Calin Pavel

Hi Nathan,

Thank you for your input. I will use this feature of OTBI in the future to test SQL queries. I'm sorry my request wasn't clear, so let me clarify: I want to use a data model in an analysis or somehow reference a data model in a column formula to determine the correct number of conversations where we have activity from the end-users.

The Fact, "# of Chats," shows the correct number of conversations; every time our chat was opened because of auto-opening, the system counts it as a chat. But my stakeholders are interested in seeing conversations where we actually have some interaction with the end-user, from their perspective, this is what a chat means.

I was able to filter this number by querying the table SVC_CHAT_MESSAGES because other subject areas don't provide this data of the chat messages, and trust me, I have checked all the subject areas that had a column that contained the word "message."

So, in my opinion, the only option is to use the value returned from the data model inside the analysis so we can use it afterward in a dashboard. And it needs to be referenced inside an analysis because we're using filters and dashboard prompts.

Hopefully, everything is clear now.

Nathan CCC

Hi Calin,

You cannot use a data model in an analysis.

But you can do it the other way around.

You can put an analysis in a data model.

Then put the report you build on that data model on a dashboard passing parameters from dashboard prompt to report data model using presentation variables with same name as data model parameters.

So in a data model you can have multiple data sets. A data set can be type SQL query with one of the 3 data sources HCM/ERP/CRM. But a data set can also be either 1) type SQL query with data source Oracle BI EE with the logical SQL from your analysis advanced tab cut paste or 2) type analysis with the path to the analysis you want to use to execute a logical SQL.

Using this method you can get everything you need using subject areas but for any gaps link to another data set on some common column using physical SQL from the application database tables and views.

NOTE As a one off configuration you will need to ask your BI Administrator to use page Administration /analytics/saw.dll?Admin to Manage Publisher to grant this out of the box JDBC data source "Oracle BI EE" to a role such as BI Consumer Role.

Calin Pavel

Thank you, Nathan! For sure, in this way, I can achieve the goal of this request.

Just to double check: isn't it possible to store the value of the data model in a presentation variable, right?

Nathan CCC

Hi, You are storing the value entered or selected from a calendar or a list of values by a user on a dashboard prompt into a presentation variable, for example, p_project_number. Then that is being passed as a parameter of the same in your data model to be referenced in the where clauses in your queries in the data sets in that data model to filter data.

in logical SQL (data model data set data source Oracle BI EE)

and ((case when ('null' in (:p_project_number)) then 1 end = 1) or ("Project"."Project Number" in (:p_project_number)))

in physical SQL (data model data set data source ApplicationDB_CRM/FSCM/HCM)

and (coalesce(null,:p_project_number) is null or pjf_projects_all_b.segment1 in (:p_project_number))