BIDA0039 – BC365 OData Query Using Meta5

21

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

I am your BIDA AI Assistant.

I will be your hostess for this demonstration today.

This video is intended for companies who have Business Central 365, either on premise, or in the cloud.

This is the video we promised you recently, and we are pleased to be able to bring it to you today.

This video is a follow up, to the prior video demonstrations, of Meta5 accessing the Navision 2018 demonstration database.

We also have a video demonstration in development, where we access Business Central 365 on premise tables directly, using Meta5.

In the prior demonstrations, we showed you how to query Navision 2018, using the Meta5 query tool, and how to access Navision 2018 tables via direct sql queries.

In today’s video we are going to show you how we can query Business Central 365, using the supported O Data interface.

This is done using the C Data ODBC Driver for Business Central 365.

So, as ever, on with the demo!

Demonstration

Here we are on the desktop of our Business Central 365 development machine.

Firstly, I would like to show you the demonstration system we are going to query.

You can see that it is running on a VM on the S Drive.

You can see that the machine is called BIDA B C DEV zero one.

We have the standard Business Central 210 demonstration system installed on this machine.

Now we will go to the web services page.

You can see on the web services page that there is object I D, number 16.

This is the Chart of Accounts Object.

Now we will go to the page showing the results of clicking on the Chart of Accounts O Data interface.

If you have been working with O Data you will recognise the format of the data that is being shown on the screen.

This is the Chart of Accounts rendered as a page of data.

This is what tools such as Power B I, and Excel, receive back from calls to Business Central.

Now we will move across to the Meta5 development machine.

Here we are on the desktop of one of our Meta5 development machines.

We have expanded the capsule, that you can see, so that it covers up other items that are on the desktop.

That is the reason for not showing you the actual Meta5 desktop.

In the top left hand corner, you can see this machine is called Meta5 DEV zero one, and it is running on the I drive.

I want to make sure that you understand these are two different machines.

They can see each other on the LAN created by the host V M ware workstation.

Now I would like to show you the ODBC connections.

You can see that we have used the 32 bit ODBC Driver for Business Central from C Data.

You can see that we called it D 3 6 5 3 2.

The last 32 being to denote this is a 32 bit ODBC driver.

In the C Data Configuration Tool, you can see that we have entered the address of the O Data interface.

You can see that we have set the Authentication Scheme to Negotiate.

Further down in the settings we have put the User ID and Password into the parameters, but there is no need to show you that.

I think you can take it on trust we have put the User ID and Password of the Administration user into the parameters for the ODBC connection.

This could be the User ID and Password of the user of Business Central.

Or it could be that a second instance of Business Central is created, so that the O Data Interfaces could be increased, to allow more data to be queried through Meta 5, than through the normal Business Central 365 instance.

In the end, I am sure you get the idea.

This is the 32 bit ODBC Driver from C Data, which can also be purchased from us here at BIDA.

It is very simple to configure.

You just have to give it the O Data address that you can readily find inside Business Central.

So.

Now we will go back to the Meta5 desktop by clicking on it.

Now we will show you the ODBC connection used in Query 1.

You can see it is D 3 6 5 3 2.

That is all you have to tell Meta5.

You can see the setting, use workstation tools data dictionary, is set to no.

This means there is no dictionary to help Meta5 understand the O Data interface.

Of course, it is possible to create such a dictionary, and to include the joins between tables in the O Data interface.

I wanted to show you, that you do not have to create the dictionary, in order to read the O Data interface.

Now we will close the options window.

Now we will open the query.

We click on show controls.

We see that the prior query created is there.

We will click on clear query to empty out the query icon.

We will click on show query catalog.

We will click on show DBA catalog.

You can see that all the tables in the DBA catalog are the O Data Interfaces.

Meta5 treats these as tables.

For the remainder of this presentation we will refer to these O Data interfaces as tables.

The Chart of Accounts is highlighted because it was in the query previously.

Now I will close the Query Catalog with just the Chart Of Accounts Highlighted.

Now we will copy the Chart of Accounts from the Query Catalog to the output area of the query.

Now we will close the query catalog.

Now we will open up a capture SQL document.

Now we will click on show data.

If you like you can stop the video, and read the sql, that was generated, in detail.

This sql was generated by Meta5, and sent to the C Data ODBC driver, without any manual creation of any dictionary of any kind.

Now we will close the query log and the query control.

We will scroll to the right, and then back to the left, so you can see the data that was returned.

In the query tool, we allow for the longest string to be shown, and so there is a lot of white space.

You can pause the video to read any specific piece of data.

Now we will close query 1.

Now we will just run query 1 and send the data into spreadsheet 1.

Now we will open spreadsheet 1.

Now we will scroll all the way to the right, then back to the left, so you can see the data.

The spreadsheet makes the data a little easier to read.

Now we will close this spreadsheet.

Now we are going to open query 2.

In query 2, you can see that, the Chart of Accounts is joined to the G L Entries.

When there is no meta5 dictionary being used this join must be created manually.

Now I will open a capture SQL Window.

Now I will run this query.

Now I will highlight the join that was performed in the running of the SQL.

Obviously, for business users, it is better for these joins to be pre-defined in the Meta5 dictionary.

This can be done by using the Meta5 dictionary to generate the SQL to query the data.

This very easy to use query tool can already have all the joins needed defined in it.

That is one of the big advantages of using Meta5.

Now we will close the query 2 icon.

Now we will run the query 2 icon so the data is sent into spreadsheet 2.

Now we will open spreadsheet 2.

You can see all the data in spreadsheet 2.

This data will make sense if you have ever used Business Central.

Now I will close spreadsheet 2.

So far in this demonstration we have explained each step as we did it.

What we are going to do now is to build this query in front of you in the demo.

There are no tricks here.

You can do exactly this for yourself, on your Business Central, if you want to.

So please feel free to watch along as we create this query in this demonstration.

We will go about building it while we make more comments.

The columns we are going to put on the output report are as follows.

  • Account Number.
  • Account Category.
  • General Business Posting Group.
  • General Posting Type.
  • General Product Posting Group.
  • Entry Number.
  • Document Date.
  • Document Number.
  • Document Type.

We are going to sum the following numeric fields.

  • Amount.
  • Credit Amount.
  • Debit Amount.

You can see that when we sum a field we highlight it, click create column, and Meta5 gives us a choice of operations to perform.

The default is sum.

But we could also set it to count, average, minimum, or maximum.

You can also insert quite complex formulas into the dialog box.

You can provide any name you want for the output field.

We generally provide the same name as the input field.

Ok!

Now you have seen how to build a simple query that will return GL Entries joined to the Chart of Accounts.

Now we will show you one more thing before we conclude this demonstration.

For query 4 we want to show you that you can set constraints on the data coming back from business central.

In this case we will set the following GL account numbers as constraints.

  • 1 1 1 0.
  • 1 1 2 0.
  • 1 1 3 0.
  • 1 1 4 0.

We will capture the SQL that is generated.

Now I will click show data.

You will see there are just 7 items found.

You can see in the generated SQL there is a where clause for the GL Account Number.

Obviously, we can set a constraint for any field on any table.

The reason this is very important is as follows.

Let us say, you are a retailer with 300 stores across the country.

Let us say, you have 20 region managers and 300 store managers.

Let us say, you want to send each region manager and each store manager a set of reports each morning.

These reports will show each person the things they need to know.

Let us say, the tool of choice is Excel.

Using Meta5 you could generate all those Excel workbooks, as separate workbooks.

Each set of reports, for each person, would contain only the data that person needs.

That means each report itself is quite small, and the Excel workbook will be fast and responsive.

Because the data is extracted from Business Central and put into the Excel spreadsheet, the person can read it while not connected to the network.

This ability to create separate, distinct workbooks, that only contain the data the individual needs, is one of the strongest features of Meta5.

The ability for business people to easily ask business questions, without having to know how that happens, is also one of the strongest features of Meta5.

This very easy to use query tool can be used to fetch data, and get it into the other desktop tools, of Meta5.

Meta5 has a large suite of analysis tools that can help business users find and understand causal correlations.

And finally.

Meta5 can send data into Excel workbooks so that the result is the region managers and store managers receive Excel workbooks.

Excel is something everyone in business is very familiar with.

All business users can read and understand Excel reports, with no additional training or software costs.

All you need to get started is one Meta5 workstation with one C Data ODBC Driver license.

With just that?

You are on your way, to being able to ask your Business Central system, any question the data inside it can answer.

Summary

So please allow me to summarise what I have shown you today.

We have shown you, we have a Business Central Demonstration Machine running on a VM.

We have shown you, it has the O Data Services turned on, and it has about 70 O Data interfaces published.

We showed you, the Chart of Accounts data is displayable from inside Business Central.

We showed you, we have another virtual machine running on the same server.

This is a meta5 development machine.

The two machines can see each other on the LAN.

We showed you, the Meta5 development machine has the C Data ODBC driver installed.

We showed you, the address of the O Data Interface on the Business Central Demonstration Machine was entered into the configuration parameters.

We showed you, the Query tool in the meta5 desktop used the name of the C Data ODBC Driver as it’s database name.

We showed you, this can all be done with no Meta5 dictionary.

We showed you, without the Meta5 dictionary joins have to be manually added in the query tool.

Prior demonstrations show that if you use the Meta5 dictionary, then the joins can be pre-added so that business users do not need to add these joins themselves.

We showed you, a series of queries that demonstrated you can get data directly from the O Data interface, via the C Data driver.

We showed you, a detailed demonstration of exactly how to build a pretty simple query, to do something like read the GL Entries from Business Central.

And lastly, we showed you that these queries can be constrained with parameters like the G L Account number.

This shows that Excel reports can be created that will contain just the data that is required for the person reading the report.

I believe that is a reasonable summary of what we have shown you today.

This concludes our demonstration today.

You can see from our demonstration that it is very simple, and very easy to query Business Central directly using the Meta5 Graphical Query tool.

You can see from previous demonstrations that it is possible to create custom Excel reports from Meta5, and send them to the individual business user who needs the reports.

Most importantly, Meta5 allows you to answer any questions from Business Central quickly and easily.

If you would like to ask Mihai questions about how to try this out for yourself?

Please click the Contact Us button below.

You can contact Mihai directly, or you can use the “Ask Us Anything” form on our Contact Us page.

And with that?

I would like to say thank you very much for watching our demonstration today.

I really appreciate your time and attention.

We hope you found this video 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.

BIDA0038 – Meta5 Navision 2018 Direct SQL Demo

0

Update: 2023-09-22.
We have now tested 60K character SQL Statements and can confirm this is working.

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 really pleased you have come back to see our latest news!

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

I am Sofi.

I am your BIDA AI Assistant.

I will be your hostess for this demonstration today.

This video is intended for companies who still have Navision 2018 installed on premise.

We are also working on a similar demonstration for Business Central on premise, and in the cloud.

This video is a follow up to the prior video demonstration of Meta5 accessing the Navision 2018 demonstration database.

In the prior demonstration, we showed you how to query Navision 2018 using the Meta5 query tool.

The query tool reads the Meta5 dictionary to know how to query the tables.

Of course, if you know Navision, you know that sometimes the queries are very complicated.

You would know that you can’t expect a simple, easy to use, graphical query tool, to build the sort of complex SQL that is sometimes required to ask Navision a question.

The obvious question that arises from the prior demonstration is this.

“If we have a query that is too complicated to be created in the Meta5 query tool?

Then how do we run that query against Navision and get an answer?”

Well, I am glad you asked me that question, because that is what our video is about today.

So, as ever, on with the demo!

Demonstration

Here we are on the desktop of our Meta5 development machine.

This demo has not been put onto our evaluation machine yet.

At the top center of the screen you can see a capsule called “Test Nav DQs”.

This means, Test Navision 2018 Direct Queries.

This is what we will demonstrate today.

I will just open it up so that you can see the contents of the capsule.

I will open up the file SQL001.

Here you can see the SQL that we plan to run against the Navision 2018 demonstration database.

We are going to show you how this SQL gets into this text file.

We are going to show you how the SQL is then run, and the results are placed into an Excel workbook.

The first thing I will show you, is that we have a folder on this development server, that has the SQL statement in it.

We have the statement visible in notepad.

I will show you that now.

You can see in the SQL there are some comments.

You can see the SQL is well formatted to be readable.

You can use any editor you like to create the SQL to be sent to Navision.

Now I am going to show you that there is a spreadsheet open.

We will look at the variable substitution sheet.

In the variable substitution worksheet, you can see that there is a variable called, Financial Management.

This has the value of the directory the SQL statement is stored in.

Next, I will take you to the SQL Statement Load worksheet.

You can see that the load from directory and run time directory are both set to the variable called Financial Management.

You can see the File Name is set to the name of the file the demonstration SQL is in.

You can see the Load Flag is set to yes.

What this means, is that when this spreadsheet is processed, it will load the SQL statement into the BIDA dictionary.

From there, Meta5 can retrieve the SQL statement, and execute it, against the Navision database.

Next, I will take you to the processing worksheet.

Here you can see two processing directives.

One processing directive is, Load SQL Statement Comments.

The next processing directive is, Load SQL Statements With Pipe As Newline.

We are going to set both to “Yes”, the first time we load the statement as part of the demonstration.

Now we are going to go to the commands folder, and double click on the command, to process the spreadsheet.

This will happen in a flash, because the spreadsheet is very small.

Here we go.

Now we will go to SQL Server Management Studio, and show you the statement was loaded into the dictionary table ok.

Now I will copy the SQL statement, from the statements table, into notepad.

We can change the pipe characters to new lines.

We can recreate the statement pretty much as it is in the file.

That is just to show you that the statement really is loaded.

Now we will set the two parameters mentioned to, no, and we will re-run the command to process the workbook.

Now I will copy the sql statement to a new window in notepad.

This time you can see that the statement has no comments in it, and the pipes for newlines, are replaced with blanks.

This is done because Meta5 passes the SQL, to the ODBC connected database, “as is”.

There can be no comments, and no newlines, in the SQL statement sent to the ODBC connected database.

So.

Now we have the statement as we want it in the BIDA Dictionary.

We want to take that statement, run it against Navision, and get back the results.

Then we want to send the results into an Excel table, so that it is possible to load the data, into the Excel PowerPivot model.

Now we will go back to the Meta5 desktop.

We will click, once, on the query icon called “Get SQL”.

Then we will select the options for “Get SQL”.

Then we will click on “other data”.

Here you can see the name of the ODBC connection.

We will go over to the ODBC Administration Application.

We will click on the ODBC connection and click on configure.

You can see the server name is META5DEV01, which is this machine.

But, of course, that can be any server that is visible to this desktop.

Now we will close the icon options.

Now we will open the query icon called “Get SQL”.

We will click show data in the query icon, to retrieve the SQL statement from the dictionary.

You can see from the demo, that we have the name of the directory, and the name of the file, to retrieve the SQL.

We also have the current flag set to 1, so that only the current SQL Statement is retrieved.

This SQL statement can be up to 64K in total.

You can see there are 8 segments of SQL statements allowed and each segment can be up to 8K.

Please note.

Meta5 currently has a much smaller limitation of the length of a SQL Statement.

This will be increased in the future.

Now we will close the query icon.

We will click on the query icon, and run the steps to put the SQL in to the text document, called SQL001.

We will open the text icon SQL001.

In the text document, you can see that the SQL statement has made it through to be stored, in the text document safely.

You can see there are lots of blanks, and tabs, because these are not removed in any way.

Now we will close the text icon SQL001.

Now we will click once on the icon called Run SQL.

We will option it and then click on “other data”.

You can see the name of the ODBC connection, in the Database Name field.

Now we will go over to the ODBC Administration Application again.

We will select the ODBC connection and click on the configure button.

Again, you can see that the sql server that this ODBC connection connects to is META5DEV01.

However, it could be any server visible to this desktop.

Now we will click cancel on the DSN configuration panel.

Now we will close the icon options.

Now we will click on the icon called Run SQL to open it.

You can see the start of the SQL statement in the icon.

We will click on show controls, to show you, that the text file it is getting the SQL from is SQL001.

You can see that the Run SQL icon has the setting, “Obtain Program Source From”, set to SQLQ001.

This name only needs to be unique inside this capsule.

You can see there are some other options for the controls of this icon.

Now we will close the controls for this icon.

Now we will click run on the Run SQL icon, just to show you that it runs the SQL, against the Navision database.

We will scroll to the right to show you the data returned.

Now we will scroll back to the left.

Now we will close the Run SQL icon.

Now we will run just the template icon.

This will overwrite the target spreadsheet.

It happens very quickly so keep a close eye on it.

We will open the target spreadsheet, to show you that there is only one row of data in it.

Now we will close the target spreadsheet.

Now we will run the whole capsule.

It will fetch the SQL statement again.

It will then run the SQL statement, and send the results, into the Excel workbook.

So here we go.

Keep a close eye on the icons that are running.

You can see it only took a second or two.

Now we will open the spreadsheet again.

Now in the spreadsheet you can see there are many rows of data.

These are the rows returned by the query.

We can also have parameters in the query, such as the date range for transactions.

We can put a parameter on any value in the where clause if we want to.

For example, if you are a retailer and you have 10 region managers who get reports.

You can run the same capsule 10 times over, and produce the customized report, for each region manager.

You can also retrieve any data from any ODBC compliant data source, and bring it into your spreadsheet, including using parameters.

Now we will close the spreadsheet and close the capsule.

This concludes our demonstration today.

Summary

I would like to summarize what we have demonstrated to you today.

What we have shown you, is that you can write any SQL statement you like, to get data from Navision.

Indeed, you can write any SQL statement, that can be executed against any ODBC compliant database.

You just have to make sure, that if a column has a strange data type, you cast it into a simple data type, that Meta5 can read.

That SQL statement can be loaded into the BIDA dictionary, using a simple excel workbook, and command file, in windows.

You can remove the comment lines, and the new lines, using options in the excel workbook.

This means your SQL statement can be quite human readable in the file, and then the loaded statement is database readable.

You can then run a capsule to read the SQL statement, from the dictionary, execute it against the ODBC compliant database, and send the results into an Excel table inside Excel.

From there, the data can be used like any other data inside an Excel table.

You can provide parameters in the SQL query so that you can repeatedly run the query and get different results.

You can send the different results to different spreadsheets, and then on to different business users, using one drive as the delivery mechanism.

These features have all been demonstrated in prior videos.

In short.

What we have shown you today is a way to write complex SQL statements, against the Navision database, to ask the most complex questions.

You can do all this without downloading reports from Navision, and cutting and pasting data, from the Navision reports into Excel spreadsheets.

This can all be fully automated, and run on a daily basis, so that all your reports can be produced every morning, and delivered to the relevant business users via their one drives.

We believe this extension of our direct query capability, using the Meta5 Query tool, solves all the most common problems in trying to create new reports on data held in Navision.

These new reports can be written at a fraction of the time, and cost, of asking your IT department to write the report, and integrate it into the Navision system.

What we have shown you today, is a way to save a great deal of time, and money, in creating custom Excel reports, and getting data from your Navision system.

We hope you liked this demo.

We hope you are interested in what we have shown you.

And with that?

I would like to say thank you very much for watching our demonstration today.

I really appreciate your time and attention.

We hope you found this video interesting and informative.

We would love to hear your comments so please feel free to use the comments section below.

If you would like to get in touch with Mihai, his contact details are on our contact page.

I wish you a great day.

Thank you.

Sofi.

Your BIDA AI Assistant.

HD0013 – How Do I Query Multi Level Dimensional Models

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 demonstration today.

In today’s demonstration we are going to show you how to navigate multi level summary data in a dimensional model.

We will show you how to do this in the product that Ralph Kimball invented, called Meta five.

This sort of multi level data model was first invented by Ralph Kimball way back in the early days of metaphor.

As our readers know, Ralph Kimball became quite famous for the dimensional model.

What you’re going to see in today’s demonstration is pretty much exactly what Ralph Kimball invented all those years ago.

We have made one small update.

And with that?

It is on with the demonstration!

Here we are on a Meta five desktop.

This is one of our development machines, so we’re only going to show you the portion of the desktop that we would like you to see.

We have a query icon which returns actual sales for last 12 months.

This is from one of our applications.

It is a real query icon for a real query.

The query icon is getting data from a development server of our development partner.

This server sits in France.

We will open up the query icon.

You can see it is going to produce a period key, a currency key, a retail outlet key, and a product category key.

It will also produce the sum of the sales amount and the sum of sales units.

This query returns sales value, and volume, by time, currency, outlet and product category.

This is as simple a query as you’re going to build in any dashboard.

The Query Control window shows you what the query looks like.

The concept that Ralph invented many years ago, was to put a level column onto each dimension table, that could be multi level.

In this query the sales period can be multi-level, and the product category can be multi-level.

But if we go down to currency.

Currency, of course, can’t be multi level.

It makes no sense to add different currencies together.

In this particular query we are saying we want the query at the outlet level, so we don’t have a level description in the outlet table.

Now we will option the level column description in the sales measures period table.

We will click show all choices.

We see the levels of day, week, month, quarter, year and total.

The total level has a special meaning.

We will explain that in another video.

We will option the product table.

We will click on show all choices.

We see we have product, product group, product category and total levels.

This query is going to ask for results, at the month level, and the product group level.

In the days of Ralph Kimball, this is what they used to do.

They used to set the level for each dimension.

Then they would join the dimension tables to the multi level fact table and get the right answers.

The database that Ralph Kimball invented, called Red Brick, did this multi-level data navigation automatically.

We have created a slight improvement on this idea.

We have introduced the idea of a fact table level dimension table.

We can look at the fact table name, in the fact table dimension table, and we can option it.

We can click on show all choices.

We see we have 7 fact tables from which we can choose.

The fact table we want to query is called, retail outlet sales measures.

But we want to know what levels are available to us.

We option the fact table name.

We click show all choices.

We select Retail Outles Sales Measures.

We click apply.

We have constrained this query to only show us levels available for the Retail Outles Sales Measures table.

For this table we can ask, what are the number of fields in the summary levels?

There are only three.

We can ask for the first one.

We click on show constrained choices.

We see we have day and month levels.

If we click on show all choices, then we will see we have day, week, month, quarter and year levels.

This fact table is only available at day and month levels.

We want month.

So, we will click on month and click on apply.

For the second dimension level description column the only option is Retail Outlet.

So, we will select that and click apply.

For the third dimension level we click on show constrained choices.

We see that we only have the Product Group Level available at the same level as the month and retail outlet levels.

We have to select Product Group Level even though we want the parent level of Product Category.

We know that this table is telling us there is a fact table summary for month, outlet, product group.

And that’s what we want.

The next query we ask is we go to the fact table level column in the fact table level table.

We option it.

We click on show constrained choices.

The number returned is 509.

So, the unique number of the summary level we want is 509.

Now, we could leave this in the query, right?

There is no harm in leaving this in the query.

It will query the right level of the fact table.

But what we can do is this.

We can go to the fact table.

Let me remove the existing constraint of 509 just for demonstration purposes.

We can go to the fact tale level column.

We can click on options.

We can type 509 in value.

Now we know we are going to go to the right fact table level.

Further, because we know we want the levels of month, retail outlet and product group, we can set the levels on these columns in the dimension tables.

The reason we do this is as follows.

If we set the levels on the dimension tables that are multi level, then we get better performance.

Now that we’ve looked at the fact table level table, and we’ve looked at what levels are available, and we’ve set our levels on the dimension tables, and we’ve set our summary level number on the fact table.

We can actually remove the fact table level table from the query.

Then we click show query.

Meta5 will redraw the query.

Once we’ve got the right number for the fact table level, we can remove the fact table level dimension table.

That’s pretty cool.

Now this is just a SQL Server development machine we’re going to query.

It’s pretty small.

I will click on show data.

We will not pause the video.

You will see data start to come back.

It will take a little while to come back because the SQL development server is in France and the Meta five development server is in Romania.

You can see 43,221 rows came back.

That is the number of rows for the last 12 months sales for all the outlets, for all the currencies, for all the product categories.

In Meta five you can capture the SQL of a query.

We have run this query previously and we captured the SQL.

Then I formatted it properly so you can read it easily.

I will put it up on the video for you.

You can hit pause and read the SQL in detail.

But I will show you the highlights.

Obviously, we’ve got a select statement getting data for the period key, the currency key, the retail outlet key, the product category key.

We also have the sum of sale extended amount, and the sum of sale units.

When we captured this, we put the fact table level in just so you could see it.

Then you have the where clause, and all the joins for the star join.

Then we have a feature which we will cover in another video.

This is where the report indicator one is set to one.

You can see that the sales measures multi level column description is month.

You can see the V M product multi level column description is product group.

Most importantly you can see the V F retail outlet sales measures multi level detail key, dot V M fact table level, equals 509.

In BIDA data models every fact table level has just one number.

So right across your data warehouse system, every summary in the data warehouse system has its own unique summary level number.

You put that fact table level number into the query, and you are guaranteed not to get any double counting.

Of course, we then have the GROUP BY clause.

You then have the ORDER BY clause.

That is what the SQL generated looks like.

It’s quite easy to see how the Query icon has its contents generated into SQL.

To summarize.

That is what I wanted to show you today.

I wanted to show you that there was a way of identifying the fact table level, uniquely identifying number, for every fact table summary level.

I wanted to show you how to then put it into a query icon.

As you can see.

It is very easy to find this number and put into the query.

You just look up the fact table level dimension table.

Look up the fact table.

Look up the levels that are available.

And then use the levels that are available.

The reason for the introduction of the dimension table is, if you don’t know this number, you can spend some time going around the dimension tables trying to find the right combination of levels.

Or you can get incorrect results, with double counting, if you don’t set your query this way.

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.

BIDA0037 – Who Needs A Data Model Anyway?

0

Hello and welcome to our latest blog post.

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

Today we are going to bring you a post from Bill Inmon, the father of data warehousing.

This is a direct quote and fully attributed to Bill.

We make no claim to having said this ourselves.

We are selling Bills “Textual ETL” here in Romania and we have trained a number of people on it’s use.

We believe “Textual ETL” will be a very valuable set of tools that will generate a great deal of new profits for large companies, especially retailers.

We are very pleased to represent Bills Textual ETL here in Romania.

We hope that we will make a number of sales.

So, on with the direct quote from Bill.

If you would like to read the blog post directly you can click on the button below:

Go To Post

WHO NEEDS A DATA MODEL ANYWAY?

By W H Inmon

There is much conversation about the necessity of the data model. The data model has been around for several decades now and can be classified as an artifact of an earlier day and age. But is the data model really out of date?

And exactly why do we need a data model, anyway?

There are actually several reasons why a data model is MORE important today than it has ever been. Todays world is really complex. We have AI. ML. ChatGPT. Transaction systems. Spreadsheets. Dashboards. KI’s. Siloed systems. Catalogs. The list of the different type of technologies just never ends. So how does the data analyst start to make sense of all of this?

The data model provides the blueprint for making all of these moving parts fit together.

Consider construction workers building a 50 story skyscraper. Would a construction worker even begin to build the skyscraper without a blueprint? The answer is – of course not. So why would a data analyst try to glue together the many pieces of technology without a similar blueprint? The answer is – the data analyst would not dream of building a complex and expensive structure without understanding what the blueprint – the data model – looks like.

But there are other very valid reasons why a data model is needed. Many organizations still have silos of data. In one place the customer is known as CUST and represents all customers over 21. In another system customers are known as ACCOUNT and represent all people who have made purchases from Texas. In yet another place the customer is represented as CUSTOMER and represents all people who have made purchases greater than $100. In short there is great confusion as to who a customer really is. The data model serves as a beacon to resolve the differences in naming and definition of data.

The third reason why a data model is invaluable is that the data model serves as a lighthouse for data. In many cases the data model represents the way data should be but currently is not. The data model in this case represents the goal of the way that data should look like in the future. Without a well defined goal, it is difficult to make any progress is bringing the data of the corporation in alignment.

These reasons for needing a data model are probably more relevant today than in the day and age when data models first started appearing.

Creating a stack of technologies does not abnegate the need for a data model. And building systems using an agile methodology hardly qualifies as an excuse for not building a data model.

And there are undoubtedly even more reasons why a data model is needed.

So if you want to beat your head against the brick wall of many systems in the corporation without a data model, be my guest. Just don’t complain when your 50 story skyscraper falls over.

Bill Inmon lives in Colorado with his wife and his two Scotty dogs – Jeb and Lena. Today is Jeb’s birthday. Today he gets a pawbender from the local ice cream shop. Once he gets started on his ice cream treat, he will be finished in less than 60 seconds. The ice cream will never have a chance to melt.

Why Would You Be Interested in Bills Opinion?

In 2004, two of our co-founders, Adrian Nagy and Dan Marina, had the good fortune to be involved in the first implementation of the Sean Kelly Data Models at Orange Romania.

By 2004 Sean Kelly had sold his data models to Sybase and Sybase was selling these data models all over the world.

The telco data models were the most sophisticated and evolved of the Sybase data models for the reason Sean Kelly got his start at the Irish government telco.

So, two of our co-founders have direct experience at implementing what many called the best data warehousing data models available on the marketplace in their space.

Later, in 2006, SAP bought Sybase. SAP gave Sean Kelly permission to bring another suite of data models to the marketplace so long as he did not compete with SAP BW or any other SAP related products.

Sean Kelly went right back to work to create a “next generation” of telco data models.

Both Adrian Nagy, and Dan Marina, were directly involved in the development of these new telco models.

In 2009 Sean Kelly and Associates implemented the first installation of this new version of models in Talk Talk in the UK. This implementation was on Netezza.

In 2010 Sean Kelly and Associates implemented the second installation of this new versions of the models in Sky Talk in the UK. This implementation was also on Netezza.

So, with two gold references, Sean Kelly was read to “take on the world” with the new generation of Telco data models.

Then some bad luck struck.

IBM bought Netezza.

IBM insisted that Netezza sell the IBM Telco models and use DataStage.

Not long after, in 2011, Sean Kelly became ill with cancer and he passed away in 2012.

His is very much missed by those who knew him.

The data models were “mothballed” for want of someone capable of selling them.

Today, we at BIDA still have copies of these data models.

We are not selling them as a product.

But we are using the ideas from these data models as the foundation for the products we are developing.

So we have deep knowledge and understanding of the Sean Kelly data models.

We agree that they are the best data models in their niche.

We do have blog posts about the models we are developing here on our site.

So we are firm believers in the idea that a data model is the starting point of building a large and complex data warehouse.

We are firm believers that you would not start the development of a data warehouse without such a suite of models to select from, any more than you would start building a 50 story building without exact blue prints of what you were going to do.

So our thanks to Bill Inmon for making this blog post.

Thank you Bill!

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.

We would love to hear your comments so please feel free to use the comments section below.

I wish you a good day.

Thank you.

Mihai Neacsu

Business Development Manager

BIDA

BIDA0036 – Navision Excel Dictionary 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 really pleased you have come back to see our latest news!

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

This video is intended for companies who still have Navision 2018 installed on premise.

We are also working on a similar demonstration for Business Central in the cloud.

Download Dictionary

1. Why Would You Watch This Video?

This video is a follow up to the prior video demonstration of Meta5 accessing the Navision 2018 demonstration database.

The obvious question that arises from the prior demonstration is this.

“If we have access to so many tables and fields in Navision, how do we make it easier to find those fields because we can’t possibly remember them all.”

And that is where today’s video comes in.

If you are wondering how, you might be able to more easily search all the fields available in Navision, then you should watch today’s video.

So, as ever, on with the demo!

Download Dictionary

2. On With The Demonstration

Here we are in a simple Excel spreadsheet.

This spreadsheet has two sides to it, the left and the right.

You can download this spreadsheet using the button on the blog post.

On the left-hand side is a table of categories.

These at the same categories you saw on the prior demonstration.

They are drillable so you can immediately understand that I can click on a drill, and it will open up with the tables.

So, let’s drill on Financial Management.

Please note that the very first time you click Excel will load the power query model so it will take a second or two to respond.

Now you can see all the tables that are listed in the Financial Management Category.

We will scroll down to the GL Account table.

As you would expect, when we click on the GL Account table, we will get all the fields in the GL Account.

As an Excel user the rest would be obvious to you.

Since we would love you to download the workbook and play around with it yourself, we won’t go into too much detail of this simple drill down.

All the categories, all the tables, all the columns are there.

If we ever get good help text and descriptions?

Then we will include them in this workbook.

Now we will scroll back up to the top of the workbook.

We will close the Financial Management Category.

Now we will show you something we think is really pretty cool.

Excel drills and searches are implemented in this workbook.

So, next to Table there is a drill.

I will click on this drill.

Now we can see there is a search option for all the tables in the dictionary.

I will enter GL and it will search for all the tables with GL in the name.

I will click off all “select all search results” to have no tables selected.

Then I will scroll down and select the GL Account table again.

When we click on the GL Account and click OK, we will be returned to the report.

The GL Account table is now selected but it may not be visible in the table.

You might have to click on Financial Management again to have GL Account visible.

Once you do that then you can click on GL Account to make the fields in the table visible.

Please note we have also numbered the columns in the order in which they appear in the tables.

Just like any other Excel report you might create; the dictionary is fully searchable in this table.

Now, we will clear those selections.

Now I would like to draw your attention to the right-hand side of the report.

You can see there is a bubble saying, “search a table”.

This provides a second way of searching for a table that you might like more.

You can see GL Account is selected.

What I will do is go to the search text and enter just GL.

When I press enter the information to the right will disappear and the mouse will go to the next line.

So, I need to click my mouse over GL again to select the cell.

When I select the cell, a drill will appear next to the cell.

When I click on the drill all the tables containing GL will appear in the drop-down box.

I will again select GL Account.

Now it will show me what category GL Account is in and it will show me all the fields in GL Account.

In this way you have a second way to search for tables using this filter and drop down.

We thought that in this first release of this workbook we would give you both options.

Please feel free to let us know which one you prefer in the comments section at the bottom of this blog post.

Of course, the value of this spreadsheet is this.

As you are creating queries on the Navision database using the Meta5 query tool, you can refer to this spreadsheet.

This will help you to easily find the tables and fields that you are looking for.

Then, when you search the spreadsheet and know exactly what fields in what tables you want, you can more easily locate them in the query tool.

In our own development work we have this spreadsheet on our left screen and Meta5 on our right screen.

You might want to try that out for yourself.

If you would like to try this out for yourself on your PC?

Then please contact me to book a time on the evaluation machine.

You can also download this spreadsheet from the button on the blog post.

You can try for yourself how easy it is to query data directly from Navision using Meta5 while also having this dictionary available to you.

Further, if you would like one of these spreadsheets for your company to help you query your Navision system?

We will prepare this spreadsheet for qualified Navision installed companies.

So please feel free to contact me to see if you meet our qualification criteria.

This report takes a little while to create.

So, we will only create a limited number of these reports as “freebies”.

Download Dictionary

Summary

So, to summarize what I have shown you today?

In our prior video we showed you that it is now possible to perform direct query to all data in your Navision system using the Meta5 query tool.

However, because there are so many tables and so many columns it’s really handy to have a searchable dictionary right there on your left screen.

We created this report to be a searchable dictionary for any customized Navision system.

It provides two ways to query what tables and columns you have available.

On the left-hand side, it allows you to drill down or to use the excel search and drill functions.

On the right-hand side, it allows you to search for tables containing the search string.

We are finding this very useful in our own development work.

We think you will find it useful too, even if you don’t have Meta5.

And with that?

I would like to say thank you very much for watching our demonstration today.

I really appreciate your time and attention.

We hope you found this video interesting and informative.

We would love to hear your comments so please feel free to use the comments section below.

I wish you a good day.

Thank you.

Mihai Neacsu

Business Development Manager

BIDA

Download Dictionary

BIDA0035 – Meta5 Navision 2018 Direct Query 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 really pleased you have come back to see our latest news!

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

This video is intended for companies who still have Navision 2018 installed on premise.

We are also working on a similar demonstration for Business Central in the cloud.

1. Why Would You Watch This Video?

So, the questions for you to be interested in this video are as follows.

Does it take you too long, and does it cost too much, to get one off questions answered from Navision?

Or worse?

Is it so hard and so expensive to get questions answered from Navision, that you actually gave up?

Do you have questions you would like the answers to, but you don’t even bother asking IT because it takes too long?

If you answered yes to any of these questions, then you will want to watch this video today.

Over the last 30 years of being in Business Intelligence, we have found that some of the hardest questions to answer are such questions as.

  1. How do I optimize my marketing dollar spend so that I am getting the best results from my marketing investment?
  2. How can I introduce differentiated customer service levels such that my investment in customer service and support will encourage an “up or out” process among our customers?
  3. How can I tell which of my customers are more likely to grow into better customers?
  4. By what mechanisms can we encourage customers to be better customers?

Getting the answers to these questions is not a matter of asking a one off question and getting a one off answer.

Getting the answers to these questions takes cycles of asking questions, getting answers, asking more questions, getting more answers. And on it goes.

If you have to go to IT every time you want to ask a question, that can get pretty tedious.

What we have found to be one of the most useful tools to answer questions like this is Meta5.

Today, we are going to show you that you can ask any question you want from your Navision 2018 system.

Of course, if your staff do not know the Navision database in detail, we can answer your questions for you a whole lot faster than anyone else can.

Or at least we think so.

2. On With The Demonstration

Here we are on our Meta5 evaluation machine.

You can get access to this machine by contacting me and scheduling time on the machine.

We will give you a Remote Access user id.

Then you will have access to this machine, as you see it today, from your desktop.

You will want to use a large screen as Meta5 is intended to be used on large screens.

We will enter the user id and password for Meta5.

Meta5 single user edition has user id and password required to gain access to the desktop.

Now that we are on the desktop you can see there are filing cabinets and folders.

These are the sorts of things you will find in a regular office space.

We keep this desktop very clean for those people who want to evaluate Meta5.

Now, you can see there is a folder called Nav 2018.

So I will open up this folder by double clicking on it.

You can see inside the folder is a demonstration capsule.

So, I will open the demonstration capsule by double clicking on it.

Inside the demonstration capsule I can see a whole series of query icons.

These have names like.

  1. Activity
  2. Account Schedule
  3. GL Entry.

And so on.

We also see an icon called Nav 2018 WTDD.

WTDD stands for Workstation Tools Data Dictionary.

This is a small and simple dictionary Meta5 uses to describe the database you want to query.

Meta5 uses this dictionary in its query tool to give you a simple graphical way of writing SQL that is sent into the Navision database.

So, what we are going to do is open this dictionary tool.

Now you can see the dictionary tool opened.

You can see there is a logical database.

In this logical database there are a set of categories.

The categories are set up in alphabetical order.

These categories are copied from the excellent Navision documentation site called Dynamics Docs dot com.

We want to provide full acknowledgement to their great site which was very helpful.

If you have Navision or Business Central, you might want to go and have a look at Dynamics Docs dot com and have a look around.

In the list of categories, you can see all the categories you would expect to see.

  1. Fixed Assets.
  2. Financial Management
  3. Purchasing
  4. Sales
  5. Service

These are all core components of Navision, and you would recognize them.

We will click on the category of Financial Management.

You can see that the tables inside the category are numbered by alphabetical order.

This is to make them easier to find.

If we scroll down you will see there are 222 tables in the Financial Management category.

And yes, we do mean that you can query any table in Navision directly from this Meta5 desktop.

So, let’s go to Sales now.

In sales you can see such tables as Customer, Customer Account, Customer Ledger Entry.

All the tables in Navision are available to you.

Now, if we click again on the Sales Category.

And then we click on Show Joins.

We scroll across.

And then we can see that the Customer table is joined to a lot of different tables.

We have analyzed the Navision database and we have added over 5,000 joins to our dictionary.

We have saved you and your colleagues weeks and weeks of work by defining all these joins.

Of course, we believe there will be a few mistakes in what we have done, but we have done a lot of the work of setting up the joins for these tables for you.

We will scroll down the sales category and you can see just how many joins and how many tables there are.

Now we will scroll back to the top.

And we will scroll back to the left.

You can see at the current state of development there are some tables that do not have joins defined yet.

When you are creating a query, you can actually add your own joins just for that query.

So if we have missed a join you can add it yourself for the query you are writing.

Also, you can create template queries that can be put into a folder in a filing cabinet for re-use by others for common questions.

So let us close the sales category.

And let us close the dictionary tool.

Now we will double click on the icon called Activity.

As we double click on it, you will see it open up.

It connects to the database server when you open the icon.

Now you can see the open query icon.

Each query icon opens up and has available up to three windows.

There is the query window itself.

Then the Query Control

And lastly the Query Catalog.

The query catalog gives you access to all the tables you are authorized to.

You could deploy a Meta5 workstation in each area of your business.

For example, Finance might want one of their own.

Sales might want one too.

In this sort of situation we would set up your Meta5 desktops to only have access to the data that you are authorized to have access to.

This very simple and easy to use query icon is your “window to your data world”.

We will click on Show DBA Catalog.

And we will make the window larger.

Now you can see the categories and the tables.

They are all available inside the query catalog.

By pointing and clicking on any table you can gain access to the data in that table.

The joins will be read in and you will not have to worry about them.

We will make the query catalog small again.

In this query we are just querying the Activity table because it is the first table.

I will record the SQL being generated.

To do this I will click on capture SQL.

A query log window will open up.

And I will make it larger.

Now I will click show data.

You can see that 9 rows were found.

This data is coming directly from the Navision 2018 demonstration database.

You will see in the query log window that we are using the CRONUS UK demonstration database.

You can see the SQL that was generated.

So, this very simple point and click query interface is available to you.

Through this simple query interface, you can gain access to all your data in Navision 2018.

Indeed, you can gain access to just about any data anywhere as long as you have the authority to access that data.

This includes data from web-based applications.

This does require another piece of licensed software.

We are not going to discuss that here.

Now we will close the Activity Query Icon.

Next, we will open the Account Schedule query icon.

In this query icon you can see that there are two joins between the tables already created when you open the query icon.

These joins are in the dictionary and were automatically drawn by the query tool when fields from both tables were requested on the output line.

Again I will click on capture SQL and then run the query.

You can pause the video to read the SQL generated in more detail.

Now I will close the Account Schedule Query Icon.

Now I will open the GL Entry query icon.

In this query icon you can see that it’s possible to request many fields from the GL Account and the GL Entry.

You can see that any field you want is there and available.

In this demonstration we just want you to see for yourself that Meta5 provides a simple query tool that can generate SQL and retrieve data from any table in your Navision system.

I will close the query catalog and you can see how many fields are available from the GL.

Again, I will click on capture SQL and then run the query.

You can pause the video to read the SQL generated in more detail.

Now I will close the GL Entry Query Icon.

Now I will open the Fixed Assets Ledger Entry query icon.

I will scroll down the Query Catalog, and you can see how many fields are available.

Again, I will click on capture SQL and then run the query.

You can pause the video to read the SQL generated in more detail.

Now I will close the Fixed Assets Ledger Entry Query Icon.

Now I will open the Sales Lines query icon.

I will scroll down the Query Catalog, and you can see how many fields are available.

Again, I will click on capture SQL and then run the query.

You can pause the video to read the SQL generated in more detail.

Now I will close the Sales Lines Query Icon.

Now I will open the Purchase Inventory query icon.

I will scroll down the Query Catalog and you can see how many fields are available.

Again, I will click on capture SQL and then run the query.

You can pause the video to read the SQL generated in more detail.

Now I will close the Purchase Inventory Query Icon.

Next to the DBA tool you can see an Excel Spreadsheet.

This is an Excel dictionary for Navision 2018.

It is helpful to be able to search for tables and field names.

It is the subject of our next video demonstration.

Now I will open up the DBA Tool again.

As you can see from the 14 categories, all the tables that you want to query from Navision are already in the dictionary.

Of course, if you have customized Navision you will need to customize the dictionary.

We would be pleased to do that for you too.

There are some tables that are not in any category.

There are many more tools in Meta5, around 150.

You can send any data you get into Meta5 into Excel for further reporting.

Those features are covered in separate videos.

Summary

To summarize what we have shown you today.

We have captured the entire dictionary for Navision 2018.

We have put all the CRONUS standard tables in to the Meta5 dictionary.

We have added more than 5,000 joins to that dictionary.

We have grouped tables into categories.

We have numbered all the tables to make them easier to find.

We have made all this available through the very simple and easy to use Meta5 query tool.

And we have shown you a series of 6 very simple queries just to show you how the query tool generates SQL, sends it to your Navision system, and retrieves the results to your desktop.

The data returned to your desktop can then be further analyzed or sent into Excel.

We believe this “direct query” to Navision 2018 is the simplest and easiest to use on the marketplace.

We would be very happy to use Meta5 to answer any and all questions you have that can be answered from your Navision system.

We would be very happy to build the Excel Dashboards you want to run your business.

Of course, if you would like to play with this evaluation machine yourself or have one of your colleagues play around with it.

Please just contact me to schedule availability of the machine.

If you would like us to set up a Meta5 desktop on your premises so you can try this out on your own data, please just contact me.

And with that?

I would like to say thank you for watching our demonstration today.

I really appreciate your time and attention.

We hope you found this video interesting and informative.

It has never been this easy to ask Navision a question.

We would love to hear your comments so please feel free to use the comments section below.

I wish you a good day.

Thank you.

Mihai Neacsu

Business Development Manager

BIDA

BIDA0034 – The Window To Your Data World – Meta5

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 pleased you have come back to see our latest news!

I am pleased to be able to bring you some news that we think will be interesting to you.

As you know we are promoting Meta5 as the best tool to give to the “5 smartest business analysts” in your company.

There have been two recent feature developments in Meta5 that makes it even more useful.

1. Meta5 Dictionary Separation

Like all query tools Meta5 has a dictionary that helps the tools understand the databases you want to query.

In the past this dictionary has been required to sit in, or near, the database you want to query.

For example, in SQL Server you can put the dictionary in it’s own database.

Then you can have views in that database to point to the tables you actually want to query in another database in the same sql server.

These tables would usually be on the same server because remote query on SQL Server like this does not work so well.

Effective immediately, you can now keep your Meta5 dictionary in one database, accessed via ODBC, and query another database on another ODBC link.

Because the dictionary is very small, you can even keep it in MS Access rather than in SQL Server.

The functionality that this dictionary separation makes possible is very valuable.

For example, if you are a Business Central user, you can have a small dictionary of the tables you need to query in MS Access on your Windows desktop.

Then, your IT staff can give you read access to those tables.

Then you will be able to query all the underlying Business Central tables that you need to directly from your desktop.

You can create capsules to run and create your reports in Excel while you sleep comfortably in your bed!

Quite literally, as long as your IT staff will give you read access to the tables you need, you can create any query you want and get the data you need without going back to IT.

Of course, you have to be careful.

And it is better to query on a copy of your production ERP.

But, if you have no copy of your production database, and you have to query production data?

You now have a way of doing that very easily, and very securely.

2. Meta5 CData Partnership

There is a product available called Cdata.

You can visit their web site on the button below.

Visit CData

CData makes it possible to connect to over 200 data sources as if they were ODBC connections.

You can see the list of data sources you can connect to by clicking on the button below.

CData Drivers

CData now provides single user licenses for their connectors that can be integrated into Meta5.

Simply put.

If Meta5 can not read the data source you want to read via a regular ODBC connection?

Then CData will very likely be able to read that data for you.

This means Meta5 becomes your “Window To Your Data World”.

You will be able to read pretty much any data you could ever want to read, and get it to your desktop to work on.

You will be able to create capsules to fetch your data, and run your reports, whenever you want, by scheduling the reports in capsule services.

You can then distribute those reports to your business colleagues via one drive, shared folders, or even email.

All without getting stuck in the “IT Backlog”.

Summary

For all the installed Business Central (including Navision) customers in Romania, we can summarize this announcement like this.

If you are frustrated that it takes “forever” to get data out of Business Central and in to your reports that you needed YESTERDAY?!

You can get Meta5 and a little training on how to use it and do that job yourself.

If you want? Of course, you can hire one of our consultants.

We would be happy to build the reports that you need, even if it needs to be directly from your production Navision.

But trust us, you no longer need to wait for “IT Support” to develop a new report that you need.

You can get your data, from your production Business Central system, now, with Meta5.

And you can get almost any data from anywhere on the web as well via CData.

So if you are frustrated with how long it takes to get new reports created by your IT Support?

Just go to our contact page.

You can type in your question into our “Ask Us Anything” form.

My phone number is also there and please feel free to call me.

I would love to hear from you.

Thank you very much for your time and attention.

I really appreciate you reading our blog post.

I wish you a great day!

Best Regards

Mihai Neacsu

Business Devevlopment Manager

BIDA

BIDA0033 – New Design Techniques For Excel Power Pivot

0


Hello and welcome to our latest blog post.

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

I am pleased to be able to bring you some news that we think will be interesting to you.

In this blog post we just wanted to mention upgrades we have made to the development methods we are using for our Excel Dashboards.

During this year we have released detailed training material on how we are building Excel Dashboards.

You can watch these videos on our web site.

Over the last few weeks we have put in some time and effort to see if we can find even better ways to design and build these Excel Dashboards.

We have managed to make two substantial technical upgrades.

These two upgrades do not make any difference to the users experience of the Excel Dashboard.

They make Excel Dashboards easier to design, easier to support, and faster to produce, on a daily basis.

The two upgrades are as follows.

One

We have converted the “ExcelTables” workbook to DAX.

The ExcelTables workbook was created to be able to update charts based on slicer clicks even if the number of bars or data points on the charts changed.

So, for example.

If you clicked on a slicer for a product category and that product category was not purchased by all age groups, then the charts for age groups would adjust the number of bars that are displayed.

The ExcelTables worksheet is quite complicated and we have long thought that DAX might be the answer to those complications.

We have now developed DAX calculations to replace this worksheet.

The DAX calculations make the worksheets easier to create.

Two

One of the features we wanted to embed in our workbooks was to only have one copy of the data inside the power pivot model.

In our first release of the dashboards we achieved this by having a separate “data” workbook.

This separate data workbook was linked to the dashboard workbook.

In preparing the dashboard workbook in Meta5 the data was read from the data workbook into the power pivot model in the dashboard workbook.

This is quite a slow running process.

It also had the disadvantage that the location of the data workbook must be hard coded in the dashboard workbook.

You can see in the demos we had to create a folder on the C drive, and we had to give it a standard name so the dashboard workbooks could know where to look for their data.

We have now developed a way of sending the data into the Dashboard Workbook in Excel Tables.

These Excel tables are then read into the power pivot model.

All the charts and reports are updated from the power pivot model.

The data is then deleted from the Excel Tables.

This then emulates our current feature that the data is only in the workbook once, and only in the power pivot model.

The good news is that we do not need a second “data” workbook to achieve this result any more.

Obviously, this means our training materials are a little out of date with our new best practice.

We wanted to mention this on our web site publicly.

Just so you know.

It took more than 100 hours of work to create our internal training materials and our public training materials.

Because these videos take so long to create, it will be a little while before we release updates to our training materials.

In thinking about Meta5 + Excel the bottom line to keep in mind is this.

If you love Excel, and you want to use Excel for your reporting and your dashboards?

You can get any data, from anywhere, into Excel, using Meta5.

You can put that data into Excel regions, tables, or the power pivot models.

You can parameterize the Excel Dashboard production, so that each person receives spreadsheets containing only the data they need for their reports.

You can deliver the Excel Dashboards to any device by any mechanism you have available to you.

You can even render portions of the reports as PDFs.

We love Excel.

We use it every day.

Microsoft has announced there are 300 million in force subscriptions for Office 365.

That means there are 300 million people out there who have Excel available to them, already paid for, and many of them would be happy to get their Dashboards in Excel.

If you love Excel, and you want your Dashboards in Excel?

Meta5 will make your Excel Dashboard development, support, and production running, faster, cheaper and better.

We can guarantee this.

You can put the data you need, into your Excel Dashboards, to create the best Dashboards possible in Excel.

And if you would like our colleagues to build your Dashboards for you?

We are training them via such well known teachers as Minda Treacy and Other Levels Dashboards.

So, with that?

I would like to say thank you very much for your time and attention!

I really appreciate you reading our blog post!

I wish you a great day!

Best Regards

Mihai Neacsu

Business Development Manager

BIDA

BIDA0032 – Free Meta5 Evaluation Service

0

Questions?

Are you one of the smartest business people in your company?

Do you use Microsoft’s Business Central ERP?

Do you use SQL Server for your Data Warehouse?

Does it take too long to get answers to new questions?

Does it take too long to get new data into your data warehouse?

Or do you not even have a data warehouse for Business Central?

Do you have questions you just can’t get answers to?

Do you think there is business value in getting answers to new questions faster?

Do you constantly cut and paste data into Excel try to answer your questions?

Do you have to keep asking “IT” for help to answer your questions?

Answers!

If you answered yes to four or more of these questions?

We would like to talk with you.

We are world class BI experts on SQL Server.

We represent Meta5 here in Romania.

With Meta5 you can easily answer your “just thought of” questions.

You can build dashboards in Excel faster, cheaper, better than any other way.

You can query any data you can lay your hands on!

You have seen the demos on our web site.

You have to use Meta5 to really “get it”

So we invite you to try out Meta5 for your self!

On our web site we have provided a great deal of Meta5 demonstration materials that you can download for your own, on premise, evaluation of Meta5.

To take this one step further we have made available a Meta5 Evaluation server on Azure.

This means we are able to give you remote desktop access to a Meta5 Pro desktop so that you can more easily perform your evaluation of Meta5.

You are now able to evaluate Meta5 from the comfort of your office, or home.

All you need is the standard Remote Desktop software that comes with Windows.

The evaluation machine is “bookable” seven days a week for those of you who would like to perform your evaluations on the weekends.

Slots will be 4 hour slots as follows: 10am-2pm, 2pm-6pm, 6pm-10pm.

We can also provide you “teams” & phone support with one of our trained Meta5 consultants to help you with your evaluation. (Business Hours Only)

You can arrange your exclusive access to the Meta5 Evaluation machine by contacting me (Mihai Neacsu) by clicking on the “Contact Us” button below.

I look forward to helping you discover the massive business value that Meta5 brings to you!

Please note: Our free offer is for Business Central ERP Installed Accounts. If you do not have Business Central installed, please contact us to discuss how we can help you.

BIDA0031 – Ralph Kimballs Demo Databases – Warehouse

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.

Firstly, I’d like to say thank you very much for coming along and watching our latest video today.

I really appreciate your time and attention, so thank you very much for that.

Today, we are releasing the next video in the series on the demonstration databases Ralph Kimball released in his book.

Of course, to be fair to Ralph, on each post we will put a button you can click to buy his book.

In this blog post we are going to give you a demonstration of the Warehouse database.

We would suggest you get an evaluation copy of Meta5, and query Ralphs databases as you read his excellent book.

We would also recommend you watch the first and second videos in this series.

Now.

Here we go!

We welcome you to the video demonstration of the Warehouse sample database from Ralphs excellent book.

We hope you like it!

Now!

This concludes our demonstration of the Warehouse database from Ralph Kimballs book.

You can download all these databases, and the Meta5 folder, to be able to query the databases, from the button below.

We sincerely hope you will learn more about dimensional modeling, using the databases and software, Ralph Kimball played such a large part in bringing to the world.

We hope you liked this short demonstration video.

If you did like it, please hit the like button and please subscribe, so that you will receive notifications when we release more videos.

If you are on our web site, please feel free to subscribe to our newsletter, to be notified when we release new blog posts.

Thank you very much for coming by our channel and watching our video, we really appreciate you.

I really appreciate your time and attention.

I wish you a good day!

Until the next blog post.

Goodbye for now.