Use Data Model value as variable in an analysis

Question

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 · Answer

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))

Calin Pavel · Answer

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 · Answer

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.