Break­through in Cloud BI? New Semantic Mod­el­ler in OAC (Oracle Ana­lyt­ics Cloud)

---

---

The new July 2022 Oracle Ana­lyt­ics Cloud (OAC) update comes with the highly sig­ni­fic­ant Semantic Mod­el­ler, push­ing Oracle in the right dir­ec­tion. In this blog post, we will run through Oracle’s next-gen­er­a­tion mod­el­ling tool and a mod­el­ling lan­guage to cre­ate semantic models.

What is a Semantic Model?

A semantic model is a metadata model that con­tains phys­ical data­base objects (metadata) that are abstrac­ted and mod­i­fied into logical dimen­sions. It acts like a trans­la­tion layer between the applic­a­tion and the under­ly­ing data structures.

The new OAC Semantic Mod­el­ler is a fully integ­rated Oracle Ana­lyt­ics Cloud (browser-based) mod­el­ling tool com­pon­ent. It boasts close integ­ra­tion with Git to provide a seam­less multi-user devel­op­ment exper­i­ence, and developers can cre­ate their mod­els using the Semantic Mod­el­ler UI, or they can cre­ate mod­els using the Semantic Model Markup Lan­guage (SMML).

Is this an altern­at­ive to the cur­rent Admin­is­tra­tion tool? Yes!

The Semantic Mod­el­ler can eas­ily be accessed from the Home page of the Oracle Ana­lyt­ics Cloud instance, as shown below:

From the Cre­ate menu, select Semantic model; let’s name it ‘Semantic­Mod­el­Demo’. On the next page, you will find four options to choose from, depend­ing on how you want to cre­ate the semantic model, and we will be dis­cuss­ing them in detail in this blog.

Let’s start with an empty model

Once the Semantic Mod­el­ler inter­face opens, these main tabs are available:

1. Con­nec­tions
2. Phys­ical Layer
3. Logical Layer
4. Present­a­tion Layer
5. Vari­ables
6. Invalid Files

The Con­nec­tions tab shows all the avail­able con­nec­tions from our OAC instance. Remem­ber, we do not define con­nec­tions here – instead, they are defined out­side the Semantic Mod­el­ler from Cre­ate – Con­nec­tions. Semantic mod­els can only use con­nec­tions with the Sys­tem Con­nec­tion field selected:

Semantic Model – Phys­ical Layer

In the phys­ical layer, you can cre­ate Data­bases con­sist­ing of the metadata of the phys­ical tables, schemas, table ali­ases, and joins between them. This is sim­ilar to the RPD phys­ical layer in an admin­is­tra­tion tool. We have cre­ated a sample data­base for the data mod­el­ling in this blog.

Now you have cre­ated a data­base in the phys­ical layer where you can select a Fact table (drag & drop in the Tables view) from the avail­able con­nec­tions in the left pane. The sys­tem auto­mat­ic­ally cre­ates joins if tables are added to the phys­ical layer with for­eign keys defined.

But here we have dis­abled it, and we manu­ally define each join:

Now that you have selec­ted all the tables for the phys­ical layer, let’s cre­ate a Table Alias for each object just like in the RPD. Right-click on the table and on Cre­ate Phys­ical Table Alias:

After cre­at­ing ali­ases for all the phys­ical tables, you should cre­ate phys­ical joins between the Facts and Dimen­sions. You can also add joins using Join Expres­sions and val­id­ate the condition:

After defin­ing the joins between the Fact and Dimen­sion tables, the phys­ical dia­gram should look like this:

It is much easier to define con­nec­tion pools, unlike in the RPD where you had to provide all the con­nec­tion inform­a­tion. Instead, the Semantic Mod­el­ler allows you to select the con­nec­tion from the drop-down menu.

Semantic Model – Logical Layer

The logical layer does a sim­ilar job to the RPD BMM layer. Here, the phys­ical tables are con­ver­ted into logical tables and logical joins defined between them. The dimen­sion hier­arch­ies are miss­ing from the busi­ness model layer because they are now part of the Dimen­sion tables, which makes more sense for users:

You should import the Fact table that was cre­ated in the phys­ical layer and then add the dimen­sions. As the phys­ical rela­tion­ship is already defined, the dimen­sions that join the Fact tables are pre-selec­ted in the BMM model. The lookup tables are handled in a dif­fer­ent tab:

After adding the Dimen­sion tables to the Busi­ness Model, you can cre­ate the hier­arch­ies for these tables; right-click on the table and choose the Edit > Hier­archy tab:

After cre­at­ing the hier­arch­ies, you can set the dimen­sional levels for the Fact source. Just right-click on the Fact table, select the Sources tab and set the levels in the Data Gran­u­lar­ity section:

From the Edit menu > Columns you can set the Aggreg­a­tion rule or cre­ate cal­cu­la­tions using expressions:

Semantic Model – Present­a­tion Layer

In the present­a­tion layer, you can cre­ate a Sub­ject Area and add tables:

That’s it! We have cre­ated a model, so now let’s Check Con­sist­ency:

Now our semantic model has been cre­ated from scratch. It also has a lin­eage viewer to show the map­pings of phys­ical, logical, and present­a­tion lay­ers, just like Query Related Objects in the RPD admin tool. We like this fea­ture as you can get the data lin­eage with just one click. Right-click on any object whose lin­eage you want to view and select Show Lin­eage or use the Ctrl + Shift + L short­cut on your key­board to open the lin­eage viewer:

Import a File to Cre­ate a Semantic Model

We saw at the begin­ning of the blog post that we can cre­ate a semantic model in four dif­fer­ent ways. The second option is to import an RPD file (expor­ted semantic model) or ZIP file (archived semantic model) from your com­puter and cre­ate or modify a semantic model in the Semantic Mod­el­ler environment.

After import­ing the RPD file, the Semantic Mod­el­ler editor opens, and you should:

• Review the model’s metadata, objects, and prop­er­ties to make sure they are pop­u­lated correctly.
• Con­firm the data source connection.
• Add any needed con­nec­tion pools or assign a con­nec­tion to each impor­ted con­nec­tion pool.

After import­ing the saved RPD file, enter the RPD pass­word just like in the admin tool:

Once the model has been impor­ted, you can see the exist­ing objects, phys­ical tables, con­nec­tions, etc., in the Semantic Mod­el­ler interface:

Load the Deployed Model to Cre­ate a Semantic Model

You can load the deployed semantic model from Oracle Ana­lyt­ics to cre­ate a semantic model using the new Semantic Mod­el­ler envir­on­ment. You can use this option when you do not have access to the deployed semantic model’s source files, but need them to per­form troubleshoot­ing work from the Semantic Mod­el­ler editor.

Click on Load from Ana­lyt­ics Server and wait a moment while the deployed RPD model is impor­ted to the Semantic Modeller.

Using Git Repos­it­or­ies with the Semantic Modeller

This is the most prom­ising cap­ab­il­ity of the Semantic Mod­el­ler in this new release. You can use Git repos­it­or­ies with Oracle Ana­lyt­ics to enable multi-user semantic model devel­op­ment. Integ­ra­tion with Git repos­it­or­ies means mak­ing semantic model JSON files avail­able to other developers to work on using HTTPS or an SSH con­nec­tion, so the devel­op­ment team can clone a model and work in branches to add, update, and com­mit the files on local and push the changes to the remote repos­it­ory. We will explain how to upload a semantic model to a Git repos­it­ory using SSH.

An SSH con­nec­tion uses a key that you gen­er­ate in Oracle Ana­lyt­ics and copy into the Git account. This key is then used to authen­tic­ate OAC to the Git repos­it­ory without need­ing to sup­ply a user­name and password.

• First, cre­ate a Git account at https://github.com. You can find more details here.
• After cre­at­ing an account with Git­Hub, cre­ate an empty repository:
•  Copy this repos­it­ory URL to ini­tial­ise Git from OAC:

If you are new to Git and want to learn more about Git repos­it­or­ies and Git basics, such as remote repos­it­or­ies, clon­ing, com­mits, pushes and branches, read the Git doc­u­ment­a­tion at https://git-scm.com/book/ and https://git-scm.com/doc.

Let’s use an exist­ing semantic model which we have already cre­ated in this blog post to ini­tial­ise with Git. Once you have opened the semantic model, you need to select the Git Panel on the bot­tom right:

After click­ing on this, the Git panel opens, and you can con­fig­ure the Git and ini­tial­ise what you cre­ated in the pre­vi­ous step to our Git repository:

After click­ing Start you will see the screen below, where you have to enter the SSH URL you copied while cre­at­ing the Git repository:

Make sure that the branch you are spe­cify­ing in this step does not exist, oth­er­wise you’ll get an error say­ing that it already exists.

In the next step, you can choose an exist­ing pro­file or cre­ate a new pro­file to ini­tial­ise with Git. Select a new pro­file and gen­er­ate an SSH key. Leave the algorithm selec­tion as default (ECDSA). The other algorithm (RSA) is deprec­ated in Git­Hub, so it won’t work.

Click on Gen­er­ate Key to gen­er­ate an SSH key and add the key to the Git pro­file to authen­tic­ate from OAC to Git­Hub. Below is a sample key:

ecdsa-sha2-nist­p521 == username@domain.com

Once the key has been gen­er­ated, copy and add it to the Git­Hub pro­file set­tings (Set­tings > SSH and GPG Keys > New SSH Key):

Once authen­tic­ated, the semantic model can be viewed in the empty Git­Hub repos­it­ory that you cre­ated at the begin­ning of this section:

By default, a semantic model Git repos­it­ory has one default main branch. You can add more branches for devel­op­ment pur­poses for dif­fer­ent developers. Make sure no developer works on the main branch. It will be your pro­duc­tion envir­on­ment, so each user should develop in dif­fer­ent branches and then merge them into the main one.

In the OAC Git panel, you can see the main Git build­ing blocks:

These tabs are explained in this Oracle doc­u­ment.

Now let’s cre­ate a local branch in the Git panel to work on the changes in the cur­rent semantic model. Nav­ig­ate to Cre­ate Local Branch. This is to add the changes to, and then to push them to the remote repository.

The cur­rent branch is dis­played in the panel.

You can select Switch Branch to switch between branches. Once we cre­ate the local branch, it auto­mat­ic­ally switches to the new one:

Delete the exist­ing join between Fact and Dimen­sion from the cur­rent model, then save it. You can see the changes in the Status pane under Unstaged Changes:

After com­plet­ing the changes in the semantic model, check in all the changes (Stage All) and com­mit the changes, which means the cur­rent branch is ready to be pushed to the remote repos­it­ory in Git. You can see that once we click on Stage All, the changes that you per­formed in the pre­vi­ous step will auto­mat­ic­ally be dis­played under the Staged Changes tab. Next, com­mit the changes by enter­ing a con­cise descrip­tion to final­ise the changes in the model:

Then go to the Push tab which dis­plays the changes com­mit­ted to the local branch. This tab is used to push the staged and com­mit­ted changes to the remote branch in Git, avail­able to other developers using the branch:

Note that in the Git­Hub applic­a­tion the local branch that you pushed from OAC to the remote repos­it­ory is vis­ible, sep­ar­ate from the main branch:

Sim­il­arly, you can pull the branches from the remote repos­it­ory to the OAC Semantic Mod­el­ler to add fur­ther changes. In the screen below you have pulled a branch that you mod­i­fied earlier to the Semantic Mod­el­ler from the remote repository:

You can see that the join we removed between Fact and Dimen­sion is not vis­ible in the cur­rent model.

Integ­ra­tion with Git provides a seam­less, effi­cient multi-user devel­op­ment envir­on­ment and source con­trol, and it also allows the developers to per­form the most com­mon Git oper­a­tions from within the Semantic Mod­el­ler inter­face. As it is a multi-user devel­op­ment envir­on­ment, each developer works with a sep­ar­ate branch while work­ing on the semantic model parallelly.

Once a developer fin­ishes work­ing on a branch, the changes are pushed to the remote repos­it­ory from the Semantic Mod­el­ler inter­face, which will be reflec­ted in the Git inter­face, as explained earlier. To merge the changes to the main branch, the developer should cre­ate a Pull request from the Git interface:

Then choose the branch that you want to com­pare with the main branch:

In the New Pull Request screen there’s the option to add mem­bers to ‘Review/Approve’ the changes. Once approved, you can merge the changes to the main branch, which is our pro­duc­tion ver­sion of the semantic model. After mer­ging, you must go to the pro­duc­tion envir­on­ment and “pull” from the main branch to bring the changes to pro­duc­tion. Hope­fully, Oracle will release a CI/CD pro­cess that avoids this last step and auto­mat­ic­ally brings the changes to pro­duc­tion after merging.

Semantic Mod­el­ler Markup Lan­guage (SMML)

In this release you may also notice an inter­est­ing new fea­ture, the ‘Semantic Mod­el­ler Markup Lan­guage (SMML)’. SMML is a mod­el­ling lan­guage based on the JSON format to add busi­ness semantics to data. Developers can now use either the Semantic Mod­el­ler user inter­face or the SMML (external text editor) to cre­ate or modify the model’s source code.

Now let’s see how the SMML editor can be used to model the data. Open the semantic model and simply right-click on the Fact table from the phys­ical layer. You can see the option Open in SMML Editor and the SMML editor win­dow opens with the source code of the phys­ical dia­gram. Sim­il­arly, you can open any object in a semantic model in the SMML editor to make changes:

The image below shows the SMML editor of the phys­ical dia­gram from the semantic model:

We can make a remark­ably simple change using the SMML editor, chan­ging the column name using the source code of a Dimen­sion table. Right-click on a Dimen­sion table and open it in the SMML editor, as shown below:

Now we will change the column name from MIN_SALARY to MINIMUM_SALARY and save the model. You can observe that the column name has changed to MINIMUM_SALARY after sav­ing the changes:

Con­clu­sion

After read­ing this blog post, OAC and OAS developers might think that this is a break­through in the Cloud BI uni­verse. The new OAC Semantic Mod­el­ler is a huge step, not only for Oracle Ana­lyt­ics Cloud but for the entire BI com­munity, and this goes to show that Oracle has a clear roadmap for a future where it thrives ahead of other mod­ern BI tools.

The Semantic Mod­el­ler feels amaz­ing and is very user-friendly, and it has the “mod­ern look” that so appeals to the user. The trans­ition from the Oracle Admin Tool with three lay­ers to the Semantic Mod­el­ler is seam­less, and the user exper­i­ence is much more fluid. For the exper­i­enced OAC/OAS developer, the trans­ition will be easy.

The most excit­ing fea­ture, for us, is the integ­ra­tion with Git. Multi-usage in RPD has always been a weak­ness in the Oracle Admin Tool, and the new integ­ra­tion with Git offers a real multi-user exper­i­ence where dozens of dif­fer­ent users can work on the semantic model sim­ul­tan­eously, without the risk of their changes being over­writ­ten by someone else when pub­lished. This is the real game-changer for the entire Oracle Ana­lyt­ics com­munity, mak­ing the devel­op­ment of new mod­els faster, more effi­cient, and more fun! We can’t wait to see what’s next for the Oracle Semantic Modeller.

If you have any quer­ies your­self about what you’ve read or about how Oracle Ana­lyt­ics can grow your own busi­ness, simply con­tact us and our team of Oracle experts will get straight back to you!