BIDA0061 – Dimensional Models Over Business Central

0

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.
Please note this video is repeated from Blog Post 60 just in case you did not see it there.

We Know SQL Server

Hello and welcome to our latest blog post.

In this blog post we just want to give you all the buttons for the new Business Central downloads and all the buttons to the prior posts.

We hope you like the Business Central 28 updates to put pseudo dimensional data models over the top of Business Central.

Please take note that as at the current date (2026-09-29) we have not completed the pseudo dimensional views over the top of the Business Central 28 database.

We have 400 out of 2000+ views that did not migrate “as is”.

One of our team is hard at work getting all those views to work.

We will release them as soon as possible.

They will also be upgraded once the 1.0 version are released.

So please feel free to come back in a week or so and they should be available at 1.0 level.

BC28 DQ Views

BC28 Direct Query DB

BC28 Demo DB

BIDA0060 Blog Post

BIDA0053 Blog Post

BIDA0050 Blog Post

BIDA BC Blog

 

And with that?

I would like to say thank you very much for reading our blog post today.

I really appreciate your time and attention.

We hope you found this blog post interesting and informative.

I wish you a great day.

Thank you.

Peter Jones

Professional Services Manager

BIDA

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

BIDA0060 – Dimensional Models Over Business Central

0

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.

We Know SQL Server

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 Peter.

I am really pleased to be able to bring you some news that I think will really interest you.

This blog post is an update on blog post number fifty of the same name.

We recommend that you also watch the video on blog post fifty before watching this video.

You can watch the video on blog post fifty using the button below.

BIDA0050 Blog Post

We have done a mountain of work on creating the fact table views over the top of Business Central twenty twenty three.

We have invented a new way to get most of the joins for a fact table done automatically.

This video is intended for all business people who are frustrated that it is so hard to get answers from your operational systems.

If you are a business person, and you are frustrated that your I T department is telling you it takes days, or weeks, to get answers to questions that are urgent for you?

Then you will want to watch this video.

We are using Business Central twenty twenty three as an example, but what we will present today applies to the entire world of getting questions answered from operational systems.

So, as ever, on with the demo!

Please note, because of their size the demonstration databases are stored on our public one drive. Anyone can download the two databases, but microsoft will require you to be logged into an outlook account or an office account for the link to work.

BC Direct Query DB

BC Demo Database

Direct Query Views

Demonstration

Here we are on our main BIDA development machine.

We are inside the Business Central twenty twenty three Direct Query Database.

You can see that there are three thousand and nine hundred and eighty five views in this Business Central Direct Query database.

That is a lot of views.

This is how many views are needed over the top of Business Central to get all the fact tables to work.

Just to be clear we have created a view for each dimension table needed.

For example there is a date dimension table for each different date that is used as a dimension table.

There are over one hundred and fifty date related views.

Most of these date related views are just views over the top of the one date table.

We create these views so that it is easier to use tools like Power B I over the top of these views.

Demonstrating Fact Tables

Now.

We will filter the views to show the fact tables.

You can see that there are five hundred and fourteen fact table views at the current time.

This will very likely increase as the views are improved.

Now we will scroll down to the sales lines views.

Now we will select the top one thousand rows from the demonstration database.

When we click down one page you can see that there are thirty eight dimension table lookup keys at the beginning of the fact table.

Obviously, you do not need to use every key in every query.

We will click on the column for shipment date and you can see that these are integer keys.

The first two digits are the customer number for this instance which is 10 to 99.

The third digit is the level of the date value.

For this sort of view this will always be one for the lowest level of date values which is the day level.

The next six digits are a two digit year, a two digit month, and a two digit day.

By making the key for the dates be in the format year, month, day it is possible to read the key and know what date the key is referring to.

The first three digits are in use for our data warehousing offering and we have kept them in the direct query version.

You are free to change these keys your self for your site.

Now we will click on the sell to customer key.

You can see that the integers are set correctly.

You can see keys for eighteen, thirty four, fourty four and so on.

As we scroll across you can see the keys are set for other dimensions like the location, the bill to customer, the general business posting group, the V A T business posting group, the V A T product posting group, the sale currency, the sale unit of measure, and so on.

The last key on this row is the key for the item sold itself.

After the keys you can see the actual data on the record.

Now we will scroll across to the far right hand side of the record and you can see for your self that all the data is exposed through the view.

Now we will scroll back across to the left.

Now we will script the view and explain how the view works.

You can see the create view statement.

You can see all the coalesce statements.

You can see that if a lookup is not found then the result will be zero.

In our data warehouse implementations we set zero as the key to say that there was no link from the fact record to the dimension record.

You can then see the view for the direct query from the business central sales line.

We have given all the underlying business central tables and columns better names that are easier to understand and to both reed and write.

You can see that the fields that form part of the primary key have a prefix to indicate that they are part of the primary key.

You will notice that all the dates have been translated to character fields.

This has been done to allow easier transfer to excel spreadsheets.

It is better to send dates and date times into spreadsheets as character strings rather than as dates or date time fields.

Now we will scroll down to the from clause.

You can see that the from clause is taking data from the business central demonstration database through a view that is renaming the table and the fields.

Then you can see a series of left joins.

There is a left join for each dimension table lookup from the fact record.

In this case there are thirty eight left joins.

The left join must include all the elements of the primary key for the table they are joining to.

The join field is defaulted to the dimension table character key field.

This has been a standard for more than thirty years now.

These left joins perform two very important functions.

Firstly, if a row does not exist in the dimension table for the key in the fact table then a zero is returned out of the view.

This links to the dimension table view that will say the value is unknown.

So, there is no loss of data when there is no proper row in the look up table.

This is a major problem when querying operational systems.

Lack of referential integrity results in the loss of rows which causes reports to be incorrect.

Secondly, because the views have been written and tested by us we have made sure that all fields in the primary key are used in the left join.

This means that you can not have accidental cartesian products by not supplying all the keys to the left join.

This is also a very common problem for writing SQL queries against an operational system data model.

Obviously, with five hundred and fourteen fact table views and one thousand five hundred and sixty five dimension table views the creation of all these views was a lot of work.

We have now perfected the method of creating dimension table views.

We do almost no by hand maintenance on the dimension table views now.

For fact tables we are at about ninety five percent automatically generated.

But as you can imagine there are a lot of joins to define.

Next.

We would like to show you that you can join headers and line data and send it all out as one row in a view.

Now.

We will go to the sales line two view and script it to a new window for you to read.

The first thing you will notice is that there are a lot more keys that are available.

This is because all the keys for both the sales header and the sales line are included in the view.

Where the same key is on both the header and the line we have created two keys because the values might be different.

For example, there is a header shipment date and a line shipment date.

There is a header sell to customer and a line sell to customer.

And so on.

Now we will scroll down.

Now you can see that each field that is coming from the header has the prefix header in the field name.

Now we will scroll down.

And now you can see that every field that is from the sales line has the prefix line in the field name.

In this way a field that is in both the header and the line can be easily distinguished on a report.

Now we will scroll down.

In the from clause you can see that the initial from clause is from the sales line and then there is an inner join to the sales header based on the document type and the document number of the sales header.

And after the join to the header you can see all the left joins that were created to join the fields on the sales header and line record to all the dimension table views that describe those fields in more detail.

This is the sort of base view that you can create and then copy and modify on a report by report basis.

I will talk more about that in just a minute.

Given this view you can see that it is possible to create any view you want to expose data to Power B I.

This gives you a great deal of freedom and flexibility in creating Power B I reports on top of your Business Central production database.

It also means that the costs of developing reports will be vastly reduced.

Indeed, we see that a market will evolve where you will be able to buy finished reliable Power B I reports for between five U S dollars and one hundred U S dollars depending on the value of the report.

We see a market where Power B I reports are sold as commodities and this will be to the enormous benefit of Business Central installed accounts.

Limitations Of These Views.

Because of the sheer number of joins and views that have been created we feel it is important to add a section in our blog post to warn potential users of these views of possible short comings in the early releases.

There are a lot of joins defined.

Thousands of them.

It is very possible that there are some errors in the joins in this first release.

Indeed, we would be amazed if we have made no mistakes at all given that we are working on a demonstration database and not on a production business central system.

We have very little test data to test these views on.

We recommend that you check the results these views are delivering to you to make sure that there are no errors in any specific query.

We would also like to make some recommendations on how we see these views might be best used by your company when using Power B I or a similar query tool.

It is obvious that querying your production Business Central database or replica will be very compute and input and output intensive.

This proposal of using pseudo dimensional views over the top of the business central tables is not a replacement for a proper data warehouse.

This proposal is a cheap and cheerful way of being able to perform reliable dimensional queries over your Business Central database without the cost of a proper data warehouse.

You can not expect to get all the benefits of a data warehouse at almost zero cost.

The main costs of running queries like this on your business central database are the processing and input and output costs.

If you run these queries on a replica of your business central database then you need to pay for the new server and licenses needed to run the replica database.

If you are a small business running business central then you will be able to run your reports directly from your production database in the middle of the night after the General Ledger processing is completed.

Smaller businesses with just a few hundred thousand sales items per year should have no problems running these views directly on top of their Business Central databases.

These smaller businesses who do not need a data warehouse, or can not justify a data warehouse, are the main prospective users of these free views.

Even so, there is no point performing all the joins for all the fact tables each time you want to query that fact table.

Similarly, there is no point in fetching all the fields for all the dimension tables for each query.

Our recommendation is to copy the base views we have provided for each report you want to write.

Then comment out all the joins and fields you do not want on this report and only expose those fields you want in each report from those views.

For example.

If you want to create a sales report just for the deliver to customer and the dates of those deliveries scheduled out for the next month?

Then you could copy the views that are available to you and then comment out all the joins and columns you do not need for this report.

You could only expose through the views the data you want on the report.

You could number them sequentially using three digit numbers to give you one thousand views that could be created over the top of one view.

Alternatively you could include the report number in the view name.

We think this might be a good idea because it means you could filter views in SQL server management studio by the report name and you would get all the views used by one report using that filter.

This format of creating views is going to lead to a massive library of views to be maintained so the easier they are to identify the lower the ongoing support costs will be.

We will not go into details on all the views here in this video because you can download this database and look at it your self.

Anyone who has experience in Business Central databases can see that presenting out all the data via dimensional models is a giant leap forward in making it easier to ask any question that you want.

To get data into Power B I you can simply create a set of views with the new report name as part of the name of the view.

You can filter the view names in Power B I so that you only get the new views with only the data you need for the report.

Then you need to add the joins for the views into the Power B I Semantic Model.

Once you have done that you are ready to go.

This is vastly easier than getting a new view exposed via the standard Business Central Interface to Power B I.

This sort of pseudo dimensional views makes every field in Business Central available to Power B I and the only extra effort on behalf of the report developer is the creation of some views and the setting up of joins in the semantic model.

That is a small price to pay for open access to every field in the Business Central database.

Of course, once there are many users of this twenty twenty three version we will bring out updates.

We would also like to add this offer to installed Business Central accounts.

If you have a Power B I dashboard that you would really like us to develop using this method?

Then we will offer to create it for free as long as we have your agreement that we can create a “standard report” out of it and we can sell it for a small amount in the Business Central marketplace.

If you want to keep such a report as confidential to your company?

Then we would ask to be paid properly to develop such a company confidential report using these techniques.

In any case, we will be much cheaper than the normal cost of report development by Business Central service providers.

We believe this mechanism may reduce the cost of report development right across the Business Central installed base.

In Summary

In summary what we have published and shown you today is that we have now developed and published for free five hundred and fourteen fact table views and one thousand five hundred and sixty five dimension table views over the top of Business Central twenty twenty three.

These views will allow you to use Power B I to query any field in your Business Central production database or a replica of it.

These views will vastly reduce the issues of accidental data loss or accidental cross products in hand written queries as well as queries that are generated by Power B I or other reporting tools.

These views will also vastly reduce the problems of getting A I to read the Business Central database.

These views bring the power of Power B I and dimensional modeling to your Business Central database.

The same idea can be used for any other large operational system database as well.

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.

I wish you a great day.

Thank you.

Sofie.

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.

HD0017 – How Do I Execute Multiple Statements In SQL Entry

0

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 back to our latest, how do I, blog post.

We are happy that you’ve come back to see our latest blog post.

My name is Sophie, and I’m your BIDA AI assistant.

I will be your host for this blog post today.

Today’s blog post is something really important that we wanted to share with you.

It is about a discovery we just made yesterday about something Meta five can do that we were not aware of.

And it’s a big deal so please allow me to first explain what it is we are going to show you today.

In the area of Direct Query we all know that to get useful and valuable results there is a lot of processing that has to happen on raw operational systems data.

We invented the idea of storing CTE queries in a central location and having Meta five read that SQL, then apply parameter resolution, and then execute it against the large operational system.

This was a huge step forward in being able to run direct query against large operational systems and then get the results into Excel.

Of course, there are limitations on CTE queries just like there are limitations on everything.

Yesterday, by accident, we discovered that it’s possible to execute multiple SQL statements in the SQL Entry Tool.

The accident was we opened up the actual tool that creates the workstation tool data dictionary rather than just run it.

And wouldn’t you know.

It has multiple SQL Statements in the text document to create the workstation tools data dictionary.

We just never looked inside it before to see how it does what it does.

So, having explained what we are about to show you?

On with the demonstration.

Here we are on our BIDA development oh one server.

This is our main development server.

You can see the SQL on the screen is three drop statements and three select insert statements.

These are obviously very simple statements just for the demonstration.

Now we will go across to the SQL Server.

You can see the tables in the database BIDA Host Services, Business Central twenty twenty three, Direct Query.

You can see that these temporary tables are not in the database.

Now.

We will go to the spreadsheet that is going to load the SQL statements in the test file into the SQL Server.

You can see the variable for the testing directory and you can see the file name and you can see that the load flag is set to yes.

Now.

We will go to the processing tab.

You can see that the variable convert semicolons to newlines is set to yes.

I don’t think that needs explaining.

Now.

We will go to the master database where the loaded SQL will be stored.

You can see from the load timestamps on the previously loaded versions of this file that we were working late last night in testing out what we are going to show you today.

Now.

We will go to the windows command line to run the command that is going to load the SQL file into the master database in the bida data warehouse sql statements table.

We just need to press the up arrow to get the command back.

Then we press enter.

And it only takes an instant.

Now we can go back to the master database and SQL statements table.

We can re-run the query.

We can see the newest load timestamp.

And we will scroll to the right.

We will copy the SQL statements.

And we will paste them into a text document for you to see.

Just so you know, we do have word wrap turned on so that it looks like there are lines of statements.

But each statement is one full statement on one line and then there are new lines between the statements.

Of course, previously there would be the semicolon at the end of each statement.

Now.

The SQL Server ODBC Driver can accept multiple statements separated by a semi colon on one line.

But you only get the return code for the last statement if you do that.

If another statement fails you can’t tell that from the return code.

So we did not want to do that.

Now.

We will go to our Meta five development oh one server.

You can see on the screen there is a get sql icon.

We will run the three icons so the finished SQL is in the SQL zero, zero, one file.

We will open up the file.

You will see the drop statements at the top.

You will see the three insert statements below.

Now we will close this file.

We will open up the SQL Entry tool that reads that file and sends the statements to SQL Server.

The SQL Entry Tool itself will split the statements by the new lines and then log the results.

You can see the statements on the screen.

Now I will close the SQL Entry Icon.

Now I will press run and the icon will run the SQL against the SQL Server database.

It will only take a second.

Now.

I will open up the SQL Entry Logs.

I have asked the tool to log all SQL Statements including the successful ones.

You can see in the top entry of the SQL Entry logs that each of the statements has been successfully executed.

Now.

We will go back to the SQL Server Management Studio.

We will refresh the tables in the direct query database.

And we will see the three new tables there.

Just to prove it all worked we will select the top thousand rows from each of the three new tables.

And there you have it.

So please allow me to summarize what you just saw demonstrated.

We wrote a piece of SQL in a file that could have comments and semicolons in it.

We loaded that SQL to a master database using a windows command.

The window command removed the comments and the newlines and the semicolons.

It replaced the semicolons with two newlines.

We then went to Meta five and we fetched the sql from that master database.

Then we sent the SQL into SQL Server and executed it.

This is a one step improvement on using CTE statements because you can persist results in tables with this new form of multiple statement processing.

And that, ladies and gentlemen, concludes our demonstration today.

We hope you liked this demonstration today.

With that I’d like to say thank you very much for watching.

We really appreciate your time and attention.

I wish you a nice day.

Sophie.

Your BIDA AI Assistant.

HD0016 – How Do I Improve The Font Rendering In Meta5 Desktop

0

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 back to our latest, how do I, blog post.

We are happy that you’ve come back to see our latest blog post.

My name is Sofi, and I’m your BIDA AI assistant.

I will be your host for this blog post today.

Todays blog post is very short.

We just wanted to show you that it is possible to improve the font rendering of the Meta five desktop by increasing the screen resolution and then setting the scale to 175%.

So let’s go with this short demonstration.

Here is Peters Windows eleven desktop display settings.

You can see that it is three eight four zero by two one six zero.

You can see the scaling to be one hundred and seventy five percent.

Now.

Here is a screen snippet of Meta five desktop objects at the normal resolution of a Meta five desktop.

And here is the same screen snippet using the high resolution desktop.

You can see that there is a marked improvement in how the fonts look.

This is simply because the display software in windows eleven is increasing the scale of the fonts and in doing so it is artificially inserting pixels where it believes they would improve the look of the fonts.

By the time that windows eleven inserts pixels and then renders the font onto an ultra high definition screen the actual quality of the font is significantly improved.

For those of us who wear glasses like Peter the font looks very reasonable on the new ultra high definition screens.

I hope you liked this short blog post today.

With that I’d like to say thank you very much for listening.

We really appreciate your time and attention.

I wish you a nice day.

Sofi.

BIDA0059 – BIDA Multi Level Data Model Example in PowerBI

0

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 Power BI user you will be interested in this Blog Post.

In our prior blog post we showed you how to get Power BI to read a multi level data model.

Of course, because of the limited screen space we needed to limit what we put on the video and into the blog post.

Today we are pleased to announce the release of an actual Power BI report file that contains a very good example of a multi level data model being read in Power BI.

You can download the example report on the button below.

Power BI Report

Of course you do not have access to our test database.

So, when you open the Power BI Report you will get a message saying it can not find the database.

However, you will still be able to open and study the data model.

In this example we have set all tables to be read from the database as we do not want to pass out any of the test data.

This data model is built over the top of our Business Central data warehouse that remains under development.

We are going to release Business Central direct query Power BI reports prior to releasing the updated Business Central data warehouse.

So, as we say.

On with the demonstration.

Demonstration

On the video at the moment you can see the top left hand portion of the data model.

You can see that to get the table names onto the screen we have collapsed all the tables.

Obviously, you can open them and look at the tables.

Unfortunately, the columns are in alphabetical order and not the order they appear in the tables themselves.

However, that is a small limitation.

To give you an idea how this model has been created we will explain briefly.

One. We created a second test database from our development data warehouse.

Two. We copied limited data from our development data warehouse based on views to the test database.

The views were replicated to the target test database as tables.

Three. We then removed columns that were not wanted in various tables.

This includes removing columns from tables that are from a different level.

For example, we removed the week, month, quarter and year data from the day table.

We removed the product group and product category data from the product table.

We removed the city, state, country and continent data from the geography table.

If you have been involved in any level of data modeling at any time you will immediately understand that this is commonly known as a snowflake schema.

But remember, the actual data warehouse that it queries is a star schema, not a snowflake schema.

So the actual data warehouse retains the performance of a star schema all be it just slightly degraded.

Four. We added all the extra rows of dummy data needed to each of these new dimension tables.

Five. We added all the referential integrity constraints.

SQL Server checks for the enforcement of the referential integrity constraints when the constraint is being created.

This meant we had to use realistic test data and not just generated data.

Six. We went through the model turning off the defaulted summarization option on fields that should not be summarized.

Seven. We went through and hid the fields in the model that we want hidden.

This then creates the sort of data model you can see on the screen now.

Because you have the actual report and you can open this data model for yourself we will just give a brief overview here.

This sort of data model is very standard across Microstrategy accounts.

Anyone who has used Microstrategy will immediately recognize this sort of model.

The only difference between how we implement our data warehouses and how Microstrategy implements their data warehouses is that we use Ralph Kimballs idea of multi level tables.

We do not claim to have invented this idea ourselves since it is widely known Ralph Kimball invented it in the early nineteen eighties.

We have other demonstrations on how to navigate multi level data models in Ralph Kimballs Meta five software.

We had believed it is not possible to navigate these models in Power BI.

But you never know until you try something.

And we are very pleased we have come up with this way of putting Power BI right on top of our multi level data models.

In the top left hand corner you can see the time hierarchy.

The only table that might not be obvious to you is the total table.

This is a common feature in Microstrategy models to give the starting point for drilling.

It contains one field with the text total in it.

It is not needed for Power BI but someone might find a way to use it.

You can see the product hierarchy.

You can see the geography hierarchy.

And then, as we scroll down, you can see many dimension tables that you would expect to see for sales transactions.

The currency, the account, the status, the units of measure and similar.

The bottom line of the model is the campaign hierarchy.

Mostly campaigns are used for marketing campaigns, but they can be used for other things.

Now I will scroll up and to the right.

At the top left you can see the sales transaction table.

This is a very normal sales transaction record that you would find in any data warehouse for any retailer.

It is linked to everything we can manage to link it to.

It contains the name of the product, the timestamp of the sale, the unit and extended amounts, the number of units and all the usual fields for a retail sales record.

We have included five summary level tables that are all at different levels of summary.

On the right hand side we have put all the demographic information tables.

This includes demographic information for companies in the case where it is a company buying the retail product and not an individual person.

There are many retailers who will issue loyalty cards to a company.

In these cases any authorized person from the company can come into the retailer and use the company loyalty card when making purchases.

I do not believe it is necessary to explain to you what all the demographic analysis tables are.

If you have been in retail data analysis for any length of time you have seen that retailers who have loyalty cards want to analyze sales by as many personal attributes of the customer as possible.

In the middle of the screen you can see the all demographic table.

You can see the keys on it.

They have obvious names.

In the middle at the top you can also see that the individual customer record is available as well.

When we scroll down a little you can see the tables further down.

You can see that we have five summary level fact tables over the top of the sales transactions.

In this particular demonstration we have not created partitions and incremental updates of the summary fact tables.

It is obvious that this could be done from the prior blog post.

I will just open up the manage aggregations window on the number three summary table.

It is obvious that they are all set up in a similar way.

You can see that the aggregate navigation has been created for such fields as cost extended amount and discount extended amount.

We will scroll down.

You can see that the aggregate navigation has been created for sale extended amount, sale units and taxes.

Now I will close the manage aggregations window.

Where possible all joins were defined to have integrity guaranteed.

This means that Power BI will generate the joins as inner joins.

The precedence of the fact tables has also been set.

We would suggest that the precedence be the same as the view numbers.

We have tested this.

Power BI really does generate the SQL to get the data from the highest precedence summary table that satisfies the query.

So as you drill down in the data and need more detailed data, Power BI will go to the level of summary required.

One thing we wanted to note for you is this.

In our experiments we found that either all dimension tables needed to be dual or database only.

We found that if we had some dimension tables as database only and some as dual that we got invalid results.

Now we are not saying that this is a bug in Power BI.

There could very well have been some mistake we were making.

Of course you do not want to load your whole customer table and all demographics table into Power BI.

These two tables are very large in large retailers.

You want them to be in the database only.

You can see in the summary table number four that the marital status field is in this table.

So if you wanted to do analysis by marital status you can use that summary table.

You will notice that in the sales transaction table that fields like marital status or age band are not in it.

This is because on the sales transaction table we have a field called customer demographics detailed key.

This customer demographics detailed key links to the all demographics table.

The all demographics table does not contain one row for each customer.

The all demographics table contains one row for each combination of demographics that exist in the customer database.

In the past we used to generate things like the all demographics tables to be all possible combinations of all attributes regardless of whether anyone in the customer database matched that combination.

One of the many features of how we write ETL is that we only generate customer analysis dimensions based on what combinations actually exist.

We then check those combinations every day to detect if a new combination has come into existence.

Obviously that is a very compute intensive operation which is why it was not done years ago.

One las thing we would like to show you in this demonstration is the report view.

So we will just go to report view now.

As you can see in the top right hand corner there is only one table for sales transactions.

The other five tables are hidden.

When I open up the sales transactions you can see that the fields from the detailed level sales transactions are listed.

The aggregation manager will manage the querying of fields that are in the summary level tables.

So if the field sale extended amount is selected then it can go to the summary table.

But if the sale unit amount is selected then it must go to the detailed level transaction records.

The business user has no way to know this as far as we know.

He or she will just see varying response times.

Next.

I would like to make just a few more closing comments.

Our BIDA ETL software manages these multi level summary tables with pretty much zero extra work.

Adding a new summary into production takes less than an hours work.

The summaries are all kept in the same table and so there are no extra objects to create.

We just like to use hand written SQL for summaries and that needs to be updated.

The time to create this ETL is so little there is no real sense in trying to automate it.

There is a control table to update as well.

In these days of SQL Server having data compression and running on SSDs there is no real limit to the number of summaries you could have for a detailed transaction table.

This leaves us with the question of how much data should be put into Power BI data sets and how much should be left in the data warehouse.

You will have to decide that for yourself.

However, Microsoft is pushing Fabric and Power BI can talk directly to Fabric.

Fabric will be so fast that if multi level tables like this are stored in fabric, and not also moved to Power BI datasets, then performance difference will be very small.

This will be especially true where the report is set to open up at a high summary level and then the drill down is through user interactions.

In the past the issue has been that most people have had only one level of summary in the Power BI dataset.

So the report had to be opened and refreshed to the high level it is opened at.

And this refresh was from data stored at the lowest level the report would require the data to be at.

This makes for slow opening times of Power BI dashboards.

By having multiple levels of summaries the highest level of summary can be the level that the report opens at.

In this case the reading of data from Fabric, or even from SQL Server, may be the same, or even faster than opening the same dashboard from a low level summary inside the Power BI dataset.

The obvious advantage is that if Power BI is reading data from the data warehouse directly then there is no need for the Power BI dataset to be refreshed and stored in yet another location.

This level of performance analysis has to be investigated on an individual basis for each company.

The developers of each dashboard will ultimately decide what data will be loaded into the Power BI datasets and how that refresh will occur.

From our perspective it looks more and more like the longer term future is for Power BI to read data directly from both column based databases as well as SQL Server data warehouses with column store indexes.

The reason we think SQL Server will still be very widely used is that we expect standard edition to remain much cheaper than the same volumes and workload on Fabric.

However, we are not experts in that area so please consult Microsoft or better informed partners on the relative costs of SQL Server and Fabric performing the same workload.

Just in case you have not heard.

In SQL Server 2025 we are promised that SQL Server can replicate to Fabric.

We don’t know what editions of SQL Server will have this feature.

But with this feature it makes a great deal of sense to keep ETL on SQL server and to just replicate the finished data to Fabric.

This keeps your entire ETL load on SQL Server and provides you an automatic backup of your fabric data warehouse.

Given your SQL server can then be on premise or in Azure, you have the best range of options to choose from.

In Summary

What we have given you today is an actual Power BI report that contains a fully working example of multi level data model navigation by Power BI.

It is a simple example of sales transactions with five levels of summaries defined for sales transactions.

It is taken from our work on our Business Central Data Warehouse.

You are welcome to learn from this example.

You are very welcome to share it with all your friends.

The more people who see this demonstration the better.

Over the years we have found these sorts of models to be quite a lot easier and cheaper to manage than the equivalent models created by MicroStrategy.

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 send us comments on our contact page.

I wish you a great day.

Thank you.

Sofie.

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.

BIDA0058 – Multi Level Data Model Navigation With Power BI

0

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 Power B I user you will be interested in this Blog Post.

Please allow me to start with the problem definition that this blog post addresses.

When using Power B I you want your dashboard to be as responsive as possible.

You also want to be able to drill down to the detailed records in the data warehouse.

You want to be able to investigate all the way down to individual transactions.

To get this type of performance it is best to have highly summarized data, lightly summarized data, and detailed data.

In this way, when you are looking at the highly summarized data, Power B I is accessing the minimum number of rows to present that highly summarized data.

As you drill down you want to read the minimum number of rows for each level of data you want to query.

You want all this data accessible from the Power B I report without having to do anything to navigate the multi level data your self.

You want it to all be automatic to you as the user of Power B I.

That is the problem statement for this blog post.

Present a Power B I model that provides the best possible performance for the user of the dashboard.

You can click on the diagram below and blow it up to the full screen while I describe to you what you are seeing on the diagram.

I will also include zoomed in portions of this overall picture in the blog post.

Demonstration

At the top left of the diagram you can see there is a hierarchy for time.

You can see there is a time total table.

You can see there is a year table.

You can see there is a quarter table.

You can see there is a month table

And you can see there is a sale date table.

You can see the sale date table is joined to the sale transaction table.

You can see from the solid blue line that the sale transaction table is only available in the database.

Across the top of the picture you can see the hierarchy for products.

You can see there is a product total table.

You can see there is a product category table.

You can see there is a product group table.

And you can see there is a product table which is linked to the sale transaction table.

With the broken blue lines you can see that all these dimension tables are available in dual mode.

You can also see that in this demonstration some dimension tables have data for the higher levels in it.

This is because this test was early in our experiments.

We have subsequently found that each dimension table attribute should only be presented out of one table.

And that table should be the level that the attribute is valid for.

Next, I would like to draw your attention to the sale transaction table.

You can see that it has all the normal fields on it like sale extended amount.

In the sales transaction table, you can see there is a detailed key for the sale date, and another detailed key for the product.

Most other keys have been dropped just to make the diagram easier to read.

So, you can clearly understand that if you ask a question to list sales at the day and product level, you will cause Power B I to read the sales transaction table.

Now I would like to bring you attention to the sales transaction summary one table.

You can see that it has detailed keys for month and product group.

You can also see that it is joined to the month dimension and the product group dimension as you would expect.

So, you can clearly understand that if you ask a question at the month and product group level, you will cause Power B I to read this table.

This table is in the Power B I service, so the read of this table will be much faster than a round trip to the sales transaction table.

However, you might want to open your report at the quarter level and product category level.

And then you might want to drill down to the months where the quarter sales are down.

You can summarize the monthly product group to the quarter product category level.

And if you have relatively small volumes, that summarization might be only slightly slower than reading data at that summary level.

But, if you have relatively large volumes, there may be a significant difference between the response time if this summary has to be done at report open time.

So, your report developer might create a summary for you, at the quarter product category level.

You can see that the sales transaction summary six table is at the quarter and product category level.

You can see from the lines on the picture, that it is joined to the quarter table, and the product category table.

Clearly, in this diagram you can see that if you open the report at quarter and product category level, Power B I will only need to read the sales transaction summary six table for the facts.

Then, when you drill down to monthly product group level, Power BI will drill down to the sales transaction summary one table.

And finally, when you drill down to daily sales at product level, Power B I will go to the large fact table in the database, and get the sales transactions for you.

This is called multi level aggregate navigation.

In relational databases, it was originally invented by Ralph Kimball, in the early nineteen eighties.

Now I would like to draw your attention to the manage aggregations window.

You have to tell Power B I how the fields on the summary level tables relate to the fields on the detailed transactions table.

For simplicity we have kept the same names across all levels of summaries.

You can see the aggregate table named is the sales transactions summary six table.

You can see that the sales extended amount on this table is related to the sale extended amount on the sales transaction table.

This is how drill down is implemented.

If you have sales extended amount on your report, and you drill down to the detailed sales transactions, it knows to use the same column name.

It is probably a good idea to name the summary level metrics fields the same names as the detailed transaction metrics fields, but it is not mandatory.

You can also see the field precedence.

The higher this number, the higher the level of summary of the data in the table, and Power B I will use the higher precedence table when it can.

Of course, you can easily understand that there could be a yearly sales table at product total level.

You can easily understand that you can have many levels of summary available to you in such a model, so that every query, at every level, reads the minimum number of rows to perform the query, and to present the data.

The report designer then has to choose how many levels he or she will put into the report, depending on the usage that is expected of the report.

Now I would like to explain how the incremental updates work.

Obviously, you do not want to reload two to five years of summary data every time you refresh your report.

What you want to do is load only those months or quarters where some change has happened for some limited period of history.

For example, your I T people might define a rule that transactions more than six months old may not be changed.

If this rule is implemented, then you only need to check the summary data for the last six months.

But if no change is found in a quarter or a month, then you don’t want that data refreshed to save refresh time, and processing power, for that report.

In the sales transaction summary table, I would like to bring your attention to the field, first day of month.

This field is used to determine which monthly partition this data will be loaded into, in the Power B I service.

The second field I would like to draw your attention to is the audit time stamp one.

If a transaction from some months ago is updated with a new sales extended amount, then the E T L system will update the required summary records in both summary one and summary six.

As part of that update, the E T L must also update the audit time stamp one field for the summary rows affected.

When the full history for these tables is loaded for the first time, Power B I will look for the maximum audit time stamp field one in each partition, and then it will save it in the Power B I service.

When you then ask for a refresh, Power B I will scan the partitions for the maximum audit time stamp, and it will compare the new maximum time stamp to the prior maximum time stamp.

If Power BI finds a later maximum time stamp, it will reload the whole partition for that summary table.

This is automatic, but you do have to tell Power B I how many periods it should go back to check for changes, from the current system date.

This selection of data is done via Power B I reserved parameters, Range Start Date, and Range End Date.

By implementing these two fields into the E T L you get the best of all worlds.

You get the ability to have multi level summary tables.

You get the ability to navigate up and down the table hierarchy.

You get the ability to update only those partitions of data in the Power B I service, where at least one row has changed.

In Summary

Just for completeness so that you understand properly what we are doing.

For BIDA data models all these tables are actually views in our data warehouses.

Because it is very handy to have small amounts of test data in real tables when creating Power B I reports, we create such test databases.

These databases contain all the referential integrity constraints as well.

Then, when the report is ready to be deployed to production, we change the server name and database name to point to the production database, and we deploy the report to the Power B I service.

We have provided a SQL Server Profile trace file of the initial load of this data model.

You can download it by clicking on the button below.

SQL Profile Trace

Anyone who is familiar with SQL and Power B I can read the trace file.

They will easily understand exactly what is happening in this model.

We would like to make one last comment on our research in this area.

We are specialists in MicroStrategy.

We have used MicroStrategy as early as the late nineteen nineties.

Everyone who knows MicroStrategy knows they have a slightly different way to build their data models.

MicroStrategy puts every level of every dimension in it’s own lookup table.

MicroStrategy puts every level of every fact table into its own fact table.

Our multi level data models provide the same functionality as MicroStrategy, just with a lot fewer tables.

It turns out that in doing this research, we have proven that Power B I is able to read MicroStrategy data models directly.

The only two changes needed to a standard MicroStrategy model are the two fields we have had to add, these are described above.

Because of this, we presume Microsoft will now tell all the MicroStrategy accounts that Power B I can read their data warehouses.

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.

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

BIDA0057 – Business Central Beginning of Time GL Balances in PowerBI

0

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.

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

BIDA0056 – Business Central Dimensional Queries Sending Results To Excel

0

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?

If you are frustrated that your I T department is telling you it takes days, or weeks, to get answers to questions that are urgent for you?

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 One.

You can review that blog post on the button below.

BIDA0051 Blog Post

So, as ever, on with the demo!

Demonstration

In this demo we want to show you the end result of creating an Excel report from Business Central on premise.

Of course, this is a demo.

You can create any report you want from Business Central on premise now.

Please note that this is not a freebie. The Meta five license does have to be paid for.

So, here we are on our demo machine.

We are inside an Excel report that is pretty simple.

Please note that you can download this Excel workbook using the button below.

Download Report

Anyone who uses Business Central will recognize the data in this report.

We are presenting sales lines for Sell to, Bill To, and Ship To.

This is a very simple report to make it easy to understand the demonstration.

So, I will click on the global slicers for Bill to countries for Great Britain and Germany.

Please note that the N A currency is actually the default currency for the Business Central implementation.

This is a known problem that we have not solved yet just because we wanted to get the demo done more quickly.

When we have clicked on Great Britain and Germany we can see the customers for sell to and bill to.

We can drill through to the detailed reports using the arrows to the top right, or just by clicking on the tabs at the bottom of the Excel workbook.

So, now I will drill through to the, sell to, customers.

You can see sales lines for such customers as the Guildford Water Department and the Auto House in Mielberg.

As I said, this is a very simple report, and it is intended to be this way.

You can see that we have put such fields as the item number, the item descriptions, and the numeric details like unit price, V A T, total amount, and the number of items sold.

We can drill back up to the global slices using the arrow at the top left of the report.

Now, I will click on that arrow.

I will also click on the Bill To, and Ship To, work sheets just so that you can see them.

Now, I will open up the power pivot model.

Now, I will just click through the tables so that you can see that there is data in the tables.

This data should be very familiar to you.

Now, I will open the diagram view.

You can see that we have the sales invoice lines in the center of the diagram.

You can see that there are detailed keys in the mini fact table.

You can see these detailed keys are linked to the primary keys of the mini dimension tables.

This data came from Business Central, and was put into this Excel Power Pivot Model.

We have added default rows automatically to the mini dimension tables, so it is not possible to lose rows when data is not correct in business central.

When a join fails you get the Not Applicable value.

You do not lose the row.

As I said, this is a very simple report, so that it is easy for you to understand.

It could also contain orders, shipments and returns.

You can put any data you want into these excel reports from your Business Central system.

Now, I will close the power pivot model.

Now, I will close the spreadsheet.

Now, I will delete the spreadsheet from the folder.

Now, we will go to the Meta5 desktop and open the Applications File Draw, to get to the capsule that creates this report.

Now, I will copy the capsule onto the Capsule Service Prompt.

Meta5 will ask me when I want to run the Capsule.

I can run it now or I can run it repeatedly under a specified alias.

You can see we have created default start times, every half hour from midnight to nine A M in the morning.

If we select one of those this capsule will run every day at that time.

In this demonstration, that would include weekends.

Please note, when this capsule runs, it will actually read data from a demonstration Business Central database, on another virtual machine.

Now, I am going to start the capsule.

Now, I will go to the capsule server and show you that it is running.

Now, I will show you the task manager.

In the task manager you will see mostly Excel processing.

Meta5 fetches the data from the Business Central demonstration database.

It then sends it to Excel regions.

It then loads the data into the Excel Power Pivot Model.

This transfer of data to the Excel Regions, and then load into the Power Pivot Model, takes the greatest amount of elapsed time.

The temporary workbooks used in the Excel workbook are then deleted.

Finally, the finished workbook is sent to the output folder, which is actually a one drive folder.

We will not pause the video, or alter the speed in any way.

We would like you to see how long this takes.

This is so much faster than trying to get data into Excel any other way.

These capsules can be run using this Meta five scheduler.

They can also be run using a windows command.

The windows commands can be run on demand, or it can be put into the windows task scheduler.

Now, the job is complete, and it disappears from the queue.

Now I will go back to the reports folder.

You can see the new report is there.

Now, we will just log off the Meta five desktop.

Just as an extra point.

The report start date and end date for sales lines are stored in a parameter database.

Your I T people can easily set these up, and make sure the dates are moved forward each day automatically.

In Summary

I would like to just summarize what we have shown you.

It is now possible for you to buy, or have your I T people create, a capsule that generates an Excel report.

That Excel report can contain any data from Business Central.

It can also contain any data from just about anywhere, that the server for the capsule has access to.

This can include data from web pages and other sources.

It is not limited to data that is stored in databases.

Meta five can then put that data into Excel automatically.

You, as a business user, get the finished report.

You can get it on your one drive.

You can run it on a schedule.

You can run it whenever you like, using a windows command.

And the best thing about all this is that the SQL that is used to get data can be as complex as required.

The only limitations on this solution are.

One. The processing power needed on your business central database.

Two. Excel and the limitations inherent with Excel.

If you want to be able to get Business Central data into Excel reports?

This is a very good way to do that.

And it will be cheaper than any other way you can solve this problem.

Of course, we are also doing this in Power B I, but Power B I uses a different approach to Excel.

If you are interested in trying out Meta five at your company?

We have a training machine available on Azure, and a weeks free training, for qualified prospective customers.

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.

BIDA0055 – Business Central Tables And Columns Rename Script

0

Please note the script of the presentation is below so you can read along with the video if you like.


Please note. This is a re-release of BIDA0042 – Business Central Tables And Columns Rename Script so that the post will be sent to the Business Central Community.

Hello and welcome to our latest blog post.

I am really pleased you have come back to see our latest news!

I am pleased to bring you some news that I hope will interest you.

I am Mihai Neacsu and I am the business development manager here at BIDA.

Today we have some good news for all those people who use Business Central on premise.

If you have Business Central on Premise and find it hard to write SQL against the underlying database?

We have some good news for you.

As you know, Business Central contains GUIDs in table names.

It also has field names that require brackets around the field names in almost all cases.

This makes writing SQL against the underlying database more difficult than it needs to be.

Today we are pleased to announce the release of an SQL Script that renames all tables and columns in the Business Central 2023 Release Wave 2 demonstration database.

You can run this script, start to finish, against the Demonstration 23 database of Business Central.

Just so you know, we used the Cronus UK database to create this script.

But it is easily edited to run against any other version of Business Central 2023.

We have tested it against that database and it works without error.

You can download this script from the button below and try it out for yourself.

Download Script

Of course, if you are on a different level of Business Central, or you have customized your Business Central, you have two options.

1. You can alter the script by hand.
2. You can send us your SQL Server dictionary tables and we can quickly run the process on a server here.

To make it more interesting for you?

We will do this script generation work for free for the first 20 companies who contact us and ask us to do that.

After the first 20?

We might ask to be paid a small fee just for our time.

We have implemented this script as part of developing our data warehousing product for Business Central on Premise.

This table and column renaming will make it a lot easier for us to build our data warehouse.

It will also make it a lot easier for us to develop SQL Direct Query Dashboards against Business Central.

We hope you will download and try out this script and consider using it in your company.

And with that?

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

Mihai Neacsu

BIDA Business Development Manager.

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

BIDA0054 – BC Direct Query Using PowerBI Cloud Demo

0

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.

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