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 Sofie 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?

If you would like to get your Business Central data into world class Power BI dashboards so that you can improve the profit of your business?

Then you will want to watch this video.

We are using Business Central twenty twenty three in our demo.

This video is a follow up on the blog post BIDA Fifty Three and BIDA Fifty Four.

You can review those two blog posts on the buttons below if you would like.

BIDA0053 Blog Post

BIDA0054 Blog Post

Download Dashboard

BC Direct Query DB

BC Demo Database

BC023_DM03_01

So, as ever, on with the demo!

Demonstration

Here we are on one of our development machines.

In this demo we want to show you the end result of creating a Power BI dashboard from Business Central On Premise data.

You can now create any dashboard you want from the source data in your Business Central System.

If you can query the production database you can do all this for free.

The only thing extra you need to pay for are your Power BI licenses.

If your production Business Central System can not tolerate the query load being generated by these Power BI dashboards then you will have to create a replica database on premise or in the cloud.

Of course. That will require a server to run the database on and the SQL Server Standard Edition License.

You will not need the Enterprise Edition license for the replica as it is just a query database.

Here we are inside the GL Beginning of Time Balances Dashboard.

You can see I am inside the web page of the Power BI Service.

I am not browsing a local server in our office.

Now. I will just set the report to full screen.

You can see that the default is for 5 years of GL Account Balances.

Where there are no transactions as is the case in the demo database you will have dashes to represent a zero balance.

You can see from the dashboard that the first year there are transactions is twenty twenty three.

Now I will drill down on twenty twenty three.

When I drill down on twenty twenty three you can see that the first month there are GL transactions is December twenty twenty three.

So the beginning of time balance in December twenty twenty three is zero for all accounts.

Some accounts have transactions for twenty twenty three.

Now I will drill up from looking at twenty twenty three.

Now I will drill down from twenty twenty four.

Now I will go to the line for account one three nine zero vehicles total.

You can see that the beginning balance is forty nine thousand three hundred and forty dollars.

There are transactions to the amount of twenty nine thousand dollars.

And you can see the beginning of time value for February is seventy eight thousand three hundred and forty dollars.

Then in February there are transactions to the amount of negative one thousand dollars and you can see that the beginning of time amount for March is seventy seven thousand three hundred and forty dollars.

If you would like to pause the video you can inspect the page more closely.

You can see that for each GL Account there is a beginning of time balance for each month.

There are the transactions for each month.

And the beginning of time balance for the next month is the addition of the beginning of time balance plus the transactions for the current month.

Everyone who works in accounting will be familiar with this type of dashboard.

It is one of the most common dashboards asked for in data warehousing projects for finance.

People who are familiar with Business Central will know that GL Account Balances are flow fields and they are calculated at run time on the reports inside Business Central.

So inside Business Central there is no report that goes back across all accounts and calculates the beginning of time balances for every account for every month across the last five years.

Or at least we don’t know of one.

We have made the font quite small because accounting people like to pack numbers on to the page and we wanted to give a realistic demonstration for our accounting colleagues.

Now I am going to show you that you can drill on all columns at once.

So first I will drill back up to the year level.

Now I will click on the button with the help text expand all down one level in the hierarchy.

You can see that the first month is December twenty twenty one.

Now I will scroll to the right of the dashboard and you will see the twenty twenty three numbers come in to view.

Now I will scroll down and you can see all the other GL Account codes are available.

Now I will click on the plus sign to drill down next to GL Account code one three nine zero Vehicles total.

You can see the transaction types are not defined for the zero transaction type.

You can see the transaction type one is set to Sale order or sale invoice.

This text can be set by your programming staff when this dashboard is implemented.

Now I will click on the minus sign and close the detailed rows.

Now I will leave the full page mode.

This dashboard was developed on one of our development machines and then published.

The technical details of how this dashboard is created are available to your programming staff.

Your programming staff can download everything they need to install this test dashboard at your company to learn from it.

Everything you see here is free.

If you would like us to install this demonstration at your company so you can play around with it yourself we would be only too happy to do so.

We want as many people as possible to know that this is now possible.

If you would like us to create some dashboards for your company?

We will create a number of free dashboards for qualified companies on the agreement that we can resell the dashboards we create for free.

So that’s a very good deal for those who take it up.

In Summary

Today’s summary is very simple.

What we have shown you is that it’s possible to develop Power BI Dashboards that retrieve data from your on premise Business Central database directly.

This uses the Microsoft Gateway product to link the Power BI Service in the cloud to your Business Central on premise database.

This means you have access to all the data in Business Central directly from Power BI running in the cloud.

Your programming staff can write any dashboard you want.

And we at BIDA can also write any dashboard you want.

This is a huge breakthrough for Power BI because until now it has not been feasible to report on all the Business Central data in Power BI.

It has only really been feasible to create Power BI Dashboards using the O Data interface.

And that interface is quite limited.

This new way brings the full power of Power BI right to your Business Central On Premise Data.

We are sure you are interested in that.

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.

Sofie.

Your BIDA AI Assistant.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

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.

BIDA0053 Blog Post

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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

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?

The really simple message for you is this.

We have invented a new and better way for you to get access to all your Business Central, On Premise data, through Power B I, in the cloud.

This new and better way also allows you to integrate any other data that Power B I can read, which is pretty much any data.

How we did this is a bit technical.

So, if you want faster, cheaper, easier access to all your data in Power B I to improve your companies profits?

All you really need to do is to pass this blog post along to your Business Central Technical Support people.

You can ask them if they can take a look at this and use it in your company.

By the way, this is all free.

The only bit you have to pay for is your Power B I Cloud Licenses if you don’t have them already.

Many Business Central On Premise customers want to use Power B I Cloud but it’s kind of hard to use.

We have solved the problem of, “it is kind of hard to use”.

Now, moving right along.

Details of Our Solution

This video is intended for more technical people who work with Business Central.

This is for those technical people whose business community want to use Power B I to access Business Central On Premise.

So.

Please allow me to start with a statement of what the problems are with using Power B I in the Cloud to access Business Central On Premise data.

The presumption is that the business community see value in having Power B I Cloud over having Power B I for Report Services.

The presumption is that the business users want to use Power B I as one of the tools for Business Central Reporting, and very likely, for reporting on other data not held in Business Central.

There are a number of issues with querying Business Central On Premise data.

Issue One.

The Business Central data model is very complicated.

To be able to answer any question you have to have access to all the data.

But there are over one thousand, eight hundred tables in Business Central. And that number is growing rapidly.

There are many cases where joins are complex.

By this I mean that the same field can be used to join to many different tables depending on the value of another field.

It is simply not possible to give anyone a copy of Power B I, the Business Central Data Model, and say, off you go, you have access to all the tables, go and write whatever report you want.

There are too many tables to manage in Power B I.

The joins between the tables are too complex.

This is why Business Central Programmers have to write code to produce even simple reports out of Business Central.

To even get the simplest of new reports out of Business Central, you need a programmer, not a Power B I developer.

Programmer time is expensive, not to mention you have to wait in the queue to get their time.

Issue Two.

Even if you could deal with issue number one, and some how find a way to simplify the joins, there are just too many tables to deal with using Power B I.

Even with the O Data interface, it quickly becomes obvious that there will be thousands of O data sources needed to query Business Central in the Cloud, or via the O Data Interface for On Premise.

That there are one thousand eight hundred tables, the vast majority of which are not related to each other, means that the number of tables presented to Power B I is overwhelming.

Not even the best Power B I developers, even with the best intentions and efforts, are going to be able to deal with that number of tables.

For a new report?

Until now, all roads have led back to the Business Central programming staff, their high expense, and their work backlog queue.

We have promised that we have solved these problems.

So, please allow me to explain.

Solving Issue One.

We have previously released a blog post talking about the idea of putting pseudo dimensional models over the top of the Business Central database.

We have been doing further work on this and we are finding that it works fine.

Sure, it’s going to consume CPU to run queries over such views, but it will be much easier to get the correct answer.

Of course, it will take a lot of time to build up the suite of views for direct query against Business Central.

Also, we have added the numbers zero one to the end of these views.

This is so that each company is able to create their own views for tables to meet their own needs for custom developed reports.

So, a company could add, for example, five one, five two, five three, as the suffixes for their own custom developed views.

Anything that SQL Server supports can be done in the view.

All that has to be presented out to Power B I is the view itself.

You can download the latest version of the dimensional model views on the button below.

BC Direct Query DB

BC Demo Database

BC023_DM03_01

You can review the prior blog post on the button below.

BIDA0050 Blog Post

By creating these pseudo dimensional views we make it an order of magnitude easier to perform the queries against the business central data.

But we still have the problem that there are just too many views to sensibly manage.

Also, there is the serious problem that you can not define referential integrity constraints between views.

This means the joins in the Power B I Models would have to be added manually.

Solving Issue Two.

So how do we solve issue two?

What we have tested, and found to work, is this.

We created what we have currently termed a “data mart data model data base”.

The “data mart data model data base” contains a set of tables that are commonly used together.

For the sake of demonstration purposes, we have chosen the General Ledger Entry table, and all the tables associated with it.

Now, it is important to note that there can be many “data mart data model data bases”, and their function is to make the Power B I Models simple to use.

So, you only need to put into one of these databases the tables that you need for a specific Power B I report.

If you are only going to use data from 10 tables, then you can create a separate “data mart data model data base” with just those 10 tables in it if you want to.

Next.

Once you have created the tables in the “data mart data model data base”, you can remove columns that are empty or never needed by your company.

You can remove the system only columns.

Or you can not create them in the tables in the first place.

Then, with the drop and create tables commands, you must alter the primary key of each dimension table to be big integer and not null.

You must alter the columns for all the lookup keys in the fact table to also be big integer and not null.

This is so that the referential integrity constraints will be able to be created.

All the data types for the fields that will be joined have to be the same, and they all have to be not null.

Once you have the columns you want in each table you can add the primary key constraints to all the dimension tables.

The following is an example.

Please see the blog post for the typed example.

alter table dbo.vm_vendor_01 add constraint pk_vm_vendor primary key ( pk_vm_vendor );

Once you have added all the primary key constraints you can add the referential integrity constraints.

The following is an example.

Please see the blog post for the typed example.

ALTER TABLE [dbo].[vf_g_l_entry_01] WITH CHECK ADD CONSTRAINT [vf_g_l_entry_01_c04] FOREIGN KEY([dk_vm_gl_account]) REFERENCES [dbo].[vm_gl_account_01] ([pk_vm_gl_account]);

We have done this work in a demonstration database for you.

You can click on the button below and download a SQL server backup of the sample database.

This backup was created in SQL Server two thousand and nineteen.

(Update 2024-09-25: The database was updated to chance the collation sequence to be the same as Business Central Demos. It was accidentally created with the default collation sequence of the host server it was created on)

BC023_DM03_01

We will create a video demonstration of all this soon.

If you restore the backup database, you will see that it is just a small database with some test data in it.

You will see that the name of the tables is the same as the name of the views in the direct query database.

More importantly, if you check the tables, you will see the primary keys are defined.

If you check the general ledger entries table, you will see that the referential integrity constraints have been defined.

What this means is that when you develop your dashboard, you can use this database as the test data source for development.

You can put indexes on tables as you like.

You can even try writing your own Power B I report, on this sample database, just to practice how this works.

Now comes the magic part.

You create your dashboard on your desktop using Power B I desktop.

When you connect to a data source you connect to this test database where ever it may be.

It has to be visible via a fixed I P address, or server name, from your desktop.

You say you want direct query to the database, when you create the connection.

This database may be on the production business central machine. It may be on a development server.

But you create the Power B I Semantic Model for this dataset using this test database.

You then create the dashboard using this test database, and any other test database that you need.

You test the report and make sure it appears to be working properly, as best as is possible, with the test data you have.

Then, before you publish the report to Power B I Cloud, you repoint the data source to the I P address of your on premise server and the direct query database.

This database must be visible from your desktop using a fixed IP address or server name.

When changing the connection, it is important to go to “advanced options” and turn off select related tables.

You just want to repoint the desktop report, from the test database where there are just those tables you want in the Power B I Semantic Model, to the direct query database where those tables will be presented as views.

Power B I can not tell the difference between a table and a view in this repointing process.

And because you have told it not to bring in related tables, it will not look for the referential integrity constraints, which will not be there anyway.

You then publish the report to Power B I Cloud.

It will give you a message saying that it does not know where to connect the semantic model to.

This is because you have used the I P address of the business central database inside your organization.

On your Business Central On Premise server, or a server with linked tables, or replica, however you have decided to do this.

You will run the Power B I Gateway.

You can run just one Gateway per machine.

So, your published Semantic Model, or models, for the report will now be in the cloud in Power B I.

But they will not be able to see your on premise business central database.

You go into the semantic model, or models, for your Power B I Dashboard, and you repoint the source of the data from the test database to the direct query database, which will be defined in your gate way.

And then?

Whola!

When you refresh your report it will send the generated SQL into the gate way, and query your direct query database, which will have at least one thousand eight hundred views in it. And possibly many more.

So, what we have figured out is that you can create a small “data mart data model data base”, to create the Power B I Semantic model, that contains just the tables you need.

Then you can repoint that semantic model to your on premise Business Central direct query database, which contains all the possible views for the whole company.

In this way any single dashboard will contain just the semantic models it needs, while all data in Business Central is visible to any Power B I report developer, who is authorized to access that data.

You would implement your security however it is you want to implement your security.

This idea of creating these small test databases, to serve as the input to the creation of the Power B I Semantic Models, means that you can limit the number of tables and number of columns in any given semantic model, to be viable and easy to use for developers.

And then, by repointing the semantic model to the direct query database, the report will have access to all the production data. You can answer any question that the data can support.

Another factor to take into consideration is this.

Because the views in the direct query database can contain common table expressions, these views can be as complex as you like.

You can literally create very sophisticated, and complex views, and present them out as simple views.

You can also put time constraints on some views to say, show last 90 days, last 180 days, last 365 days and perhaps last 2 years.

In this way, a dashboard that only needs the last 90 days of data does not need to have “drill down” to do that. The view for the fact table data could have that constraint in it. You just add another suffix number to the fact table view.

Just for completeness.

The professional level of Power B I has a limit of 1 million rows being returned for any single query.

If you need to return more than one million rows, you have to buy the higher level licensing for Power B I.

Also, because of the way that the SQL is generated by Power B I, we would encourage you to create views that only display the columns needed in each report.

And if a report is only needed at summary levels, such as weekly, monthly, quarterly and similar, it is better to do the summarization in the database in another view, and not let Power B I do the summarization from the full fact table itself.

This is because it will consume far more temporary space if this is allowed to happen.

To show you what the SQL looks like for all the work that goes into creating such a report?

We have included a trace of our testing.

If you are familiar with Power B I, and how it creates its semantic models, and how it performs direct query, the trace will make complete sense to you.

When we read the trace, we were very pleased with how easy it was to read and understand.

Please click on the button below, if you want to read the trace created, when we set this up.

Gateway Trace

Simply put.

The Power B I Semantic model is created from the “data mart data model data base”.

Then when the switch is made to the direct query database, and the referenced tables option is turned off, it revalidates the model against the direct query database.

If the revalidation works you can then publish your report.

Just for extra information.

From our testing it appears that the semantic model knows the name of the SQL Server database at publishing time.

We were not able to alter the database name inside Power B I cloud services.

This is why we recommend changing the name of the database prior to publishing.

Once published, you can simply repoint the semantic model to get it’s data from the On Premise gateway using the correct database name.

Your database gateway support people will know how to set that up.

In Summary

Power B I Cloud offers a very wide range of features to help business managers improve the profit of their businesses.

Power B I Cloud is far in advance of Excel and Power B I on premise.

Excel is great and is better than Power B I for many uses.

Power B I On Premise is also great and very useful.

But it lacks many of the features of Power B I Cloud.

So, if you have Business Central On Premise, and you want to use Power B I Cloud?

There was no really good solution to being able to take advantage of Power B I Cloud, at least that we are aware of.

By inventing the following two ideas?

We have made it viable to use Power B I Cloud with Business Central On Premise.

Those two ideas being.

One.

Placing pseudo dimensional models over the top of the direct query database.

Two.

Creating separate dedicated “data mart data model data bases”, which will be used to create the Power B I Semantic Model, for each report that needs to use that set of tables.

Just as further information.

Although we have used Business Central in this example, it should be noted that what we describe works for any SQL Server production system.

This is a fast, simple, cheap and easy way to get started using Power B I against SQL Server production databases, or replicas.

Lastly, we really must add some caveats.

Obviously, direct query against a production database, or replica, has it’s limitations.

This is not like having a proper staging area.

This is not like having a proper data warehouse.

But this idea is very cheap and easy to get started with.

For thousands and thousands of small companies?

This will be enough.

If you use this idea and you come to understand why separate staging areas and data warehouses offer greater value?

Then you will have more arguments available to put into your business case, to ask for the budget for your data warehouse project.

We see this idea of using Power B I, without any other software purchase necessary, as a good way to get started on your journey of learning how to use data to improve the profitability of your company.

And with that?

I would like to say thank you very much for listening to 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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.