Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.
Hello and welcome to our latest blog post.
I am Sofi, your BIDA AI Assistant.
I will be reading this blog post for you today, on behalf of Mihai.
I am really pleased to be able to bring you some news that I think will really interest you.
If you are a business person using Business Central On Premise?
We have created a new way to easily query any data in your Business Central database, using the Power B I Service in the cloud.
This video is designed for technical people.
However, as a business person, you might also want to watch this video, just to see what is now possible.
This video demonstration is a follow up on our previous blog post.
If you are a technical Power B I developer?
You can read the previous blog post on the button below.
And now?
On with the demonstration.
Demonstration
Here we are on the desktop of one of our Azure Virtual Machines.
This is in Europe West.
The first thing I want to show you is the Direct Query Database for Business Central.
We will just list the views so that you can see there are over three thousand, five hundred views.
These views expose the Business Central tables with improved column names.
They also expose the Business Central tables using a pseudo dimensional data model.
The pseudo dimensional data model reduces the chances of incorrect answers being generated by queries.
This pseudo dimensional data model is work in progress.
Anyone is welcome to create new views and send us a copy.
All these views are free and published for anyone to use.
The obvious disclaimer being that you accept all liability in using these views.
What I want you to understand is that the Direct Query database sits on the same machine as the production database, or a production replica database, if you want to do that.
So, you could turn on replication from your business central server, and you could replicate all that data to another machine on premise, or to a virtual machine in Azure.
We are proposing that the Direct Query database sit on the same machine as the production database for better performance.
Now I want to show you the data mart data model data base.
This database only has thirty one tables in it.
These tables were created just using select statements from the views.
When we open up the general ledger entry table, you can see that the referential integrity constraints have been added to the table.
When we open up any of the dimension tables, you can see that the primary key constraint has been added.
As a technical SQL Server person, you will know that it is very easy to create a small test database like this.
There is also test data in this test database.
The main thing to point out is that this test database can be created very quickly and very simply.
It can contain just the tables and columns that are required for the Power B I dashboard that is being developed.
So.
With that background.
Let us move over to Power B I desktop on this machine.
Here we are in Power B I on the desktop of the azure machine.
We have done the demonstration all on just one azure machine for simplicity.
Obviously, the usual case would be that the Power B I Desktop being used is not on the same machine as the business central database.
In this demonstration we are going to show you how we can develop a Power B I report based on the data mart data model data base.
In this case that database has thirty one tables.
So it creates a quite small and manageable Power B I Semantic Model.
And then we will repoint that report to the Direct Query database which has over three thousand, five hundred views in it.
Also, there are no joins defined between the views.
Three thousand, five hundred views, with no joins defined, is completely unusable as a source to create a Power B I Semantic Model.
Now I will click on the SQL Server button to connect the report to SQL Server.
I will enter the I P address of the machine.
We will use direct query.
I will click on ok.
In the next step we have to provide credentials.
Just for the sake of a simple demonstration I will use Raul Romans credentials.
I will click on connect.
We will ignore the message about encryption.
We will be connected to the SQL Server through the Power B I Navigator.
Now we will select the data mart data model database.
When we open up the data mart data model database, we see the general ledger entry table.
I will click on the general ledger entry table.
I will click on select related tables.
You can see all the related tables are selected.
This is because the referential integrity constraints are defined in the test data mart.
Now I will click on load.
You will see that Power B I now reads the information schema of the database and creates the Power B I Semantic model based on the test data mart.
It will just take a little while, and we think it’s worth you seeing exactly what it does.
Now that Power B I is connected to the database you can see all the tables are presented on the right hand side of the Power B I pallet.
Just to prove what has happened we will go into the data model.
I will just scroll around the data model a little bit, and you can see the joins were all defined from the database.
You can download this database and put it on a local machine and try this for yourself.
The two databases you need are on the previous blog post linked above.
Now I will create a visualization using the table icon in Power B I. .
I will use the posting date for the report.
The first field is going to be the year number.
The second field is going to be month in year.
The third field is going to be the month short description.
Now we will select some fields from the general ledger entry fact table.
We will select credit amount and debit amount.
As you can see the visualization is updated as I add the fields.
Now I will increase the size of the fonts being used just to make it a little easier for you to read.
Ok.
Now we have our very simple report.
Power B I created the semantic model from the test database.
It is getting the data from the test database with just thirty one tables in it.
Now I will save the report.
I will call it report eleven, just to differentiate it from the first test report I created.
Now I will publish the report to the Power B I Service in the cloud.
I will put it into a demonstration workspace.
The publish operation was successful.
Now we will go to the Power B I service in the web browser.
I will refresh the page.
I will show you the gateway for the semantic model that was just created.
I have previously created the gateway and pointed the gateway to the underlying database.
This was done when we created version one of the report in testing.
As you can see.
We have a gateway connection to the semantic model.
It is pointing to the data mart data model database.
This is because it already existed from report one.
When you create this for the very first time you will have to create the gateway.
If you do not know how to do that?
Your technical support staff will know.
Now I will go back to the workspace.
Now I will open the report in Power B I Cloud.
You can see the report is the same as it was on the desktop.
Now we will edit the report.
So, I will click on edit.
Now I will select a field from the general product posting group.
I will get the description for the posting group and put it onto the report.
I will put it between the month name and the credit amount.
Power B I creates the SQL necessary to refresh the report.
It sends it to the gateway.
The gateway sends it to SQL Server.
And then the results are returned.
So, this is a demonstration of a report in the Power B I Service in the cloud, accessing data in SQL Server on a virtual machine, in Europe West.
Now I will save this report.
Now I will go back to Power B I desktop.
Now I will create a second report using, save as, for the report that we just created.
I will call this report number twelve.
So, now we have the exact same report, it is just a copy, and it is called report twelve.
Now, I am going to change the data source for this second report to the direct query database, that has more than three thousand, five hundred views in it.
Also, there are no referential integrity constraints defined in the direct query database.
Now I will click on the transform data icon in the queries tab.
Now I will click on data source settings.
You can see the source database is the data mart data model data base.
Now I will click on change source.
Now I will change the database name to be the direct query database.
Now I will click on advanced options.
Now I will click on, include relationship column, to turn the selection off.
Now I will click on OK.
Now I will click on close.
I will get a message to say that there are changes pending.
Now I will click on apply changes.
Now Power B I will connect to the new direct query database.
It will validate that the tables and columns exist in this database.
So, clearly, the data mart data model database must be a subset of what is in the Direct Query database.
You can not create new fields or tables in the data mart test database, and then repoint that data model back to the Direct Query database.
As you will see, Power B I takes a little while to validate everything.
Now I will save this report.
Now I will publish this report to the Power B I Cloud Service.
I will publish the report to the same workspace as the prior report.
Now I will go back to the web browser.
I will close the previous report.
I will refresh the page.
Now you can see that the report twelve report, and semantic model, has been created.
Now I will go to the settings for the semantic model for the twelve report.
I will go to the gateway.
You can see that this new report, the number twelve report, is pointing to the direct query database.
As I mentioned, this happens automatically for the second and subsequent reports you create.
For the very first report you create like this, you will have to create the gateway entry.
If you do not know how to do this?
You should contact your technical support people.
These gateway entries only have to be set up once per database.
Now I will go back to the workspace.
I will open the twelve report.
Now I will click on edit to edit the report.
Now I will add the general product posting group short description again to the report.
I will put it between the month name short description and the credit amount.
So, here we go.
And Whola!
Power B I Generates the SQL, it sends it to the direct query database with all the joins defined correctly.
This is because the joins have been inherited from the data mart data model database.
And there you have it.
How to develop Power B I Reports using a data mart data model test database.
And then how to deploy them to a direct query database.
In Summary
I would like to summarize what I have shown you today.
You have seen how to create a Power B I Semantic model from scratch.
This was by using a data mart data model test database specifically created to support a specific report.
These test databases are very quick and easy to create.
Much of the work can be made automatic.
And after a while you will have a library of tables in different databases that you can copy.
You saw me create a very simple report, send it to the cloud, set the gateway, and make an update to the report.
This proved that the report in the cloud was able to talk directly to the database via the gateway.
Then, you saw me make a copy of this report.
You saw me change the connection of that Semantic Model, to point to the direct query database.
You saw Power B I validate the semantic model against the new database.
You saw me save this report as report twelve, and publish this new report to the test workspace.
Then you saw me change the report the same way as I changed the eleven report.
And you saw that Power B I Cloud updated the report properly.
On our prior post you can get the SQL trace for our testing.
This shows you the process inside SQL Server, for all this reading and semantic model updating that Power B I performed, during our testing.
It is actually very interesting.
All in all?
What you have seen is a new, and better, way of using Power B I to directly query Business Central on premise databases.
And given that the vast majority of Business Central implementations are on premise?
This will be of interest to all those customers.
You can get the full power of Power B I Service in the Cloud, to directly access your Business Central production database, or replica.
Everything you have seen here is free, the exception being the Power B I licenses if you do not already have them.
BIDA is providing all these views for free as part of our commitment to adding value to the Business Central community.
And with that?
I would like to say thank you very much for watching our video today.
I really appreciate your time and attention.
We hope you found this blog post interesting, and informative.
We would love to hear your comments, so please feel free to use the comments section below.
I wish you a great day.
Thank you.
Sofi.
Your BIDA AI Assistant.
We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.
