BIDA0043 – Business Central Staging Area Evaluation Database Available

0

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


(Note. We have swapped my photo for the cartoon character. I hope you like it!)

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, again, we have some good news for all those people who use Business Central on premise.

Today we are making available an Evaluation Database for our Business Central Staging Area.

This Evaluation Database will be for qualified prospective customers.

So, if you are wondering if having a Business Central Staging Area is good idea for your company?

Please listen on or read on.

The Argument For Staging Areas

Way back in 2019 we released an initial white paper on how to build Staging Areas for Navision.

You can get the white paper for your IT colleagues on the button below.

White Paper

To deliver a data warehousing solution you have to have a Staging Area.

This is as true of Business Central as it is with any other large operational system.

We have just released the tables and columns renaming script for Business Central 2023 Wave 2.

Today, your IT colleagues can write queries against the underlying Business Central database to answer your business questions.

With our renaming script your company can save time and money in development and support of such queries and reports.

You can also have your IT colleagues replicate your Business Central database on to another server and query the data in the replica.

This querying a copy of the production, including easier to remember names for tables and columns, will be sufficient for tens of thousands of Business Central installed accounts.

But many tens of thousands of Business Central installed accounts will want more than just a replica of their operational system.

Tens of thousands of Business Central installed accounts will want a Staging Area.

So, as an installed Business Central Business User you should have the question on your mind:

“Why would I want a Staging Area for my company?”

So please allow me to explain the six main benefits a Staging Area has over a replica of your Business Central operational database.


Benefit Number One – Retain Deleted Data

A staging area allows you to retain historical data that has been deleted from Business Central.

Over time, some tables in Business Central become very large, such as the GL Entries.

They impact operational system performance for inserts, updates, backups and general reporting as well.

This old data also adds to the cost of running Business Central.

By retaining old data in the Staging Area you can delete data from the production system knowing you have an online copy of this old data.

This will make your Business Central system run faster.

It will save you time and money over a simple replica.

Our staging areas carry a flag on every row to say whether it has been deleted from the operational system.


Benefit Number Two – Integrating Other Data

You don’t only have data in your Business Central system.

You have data from many other systems internally in your company.

You also have data coming to you from outside your company.

A staging area allows you to put non Business Central data into the same database and easily combine it with your Business Central data.

A very good example of this is competitive information.

How much are your competitors selling their similar products for?

There is a very wide range of data available to business analysts in any company.

Putting it all into one place like a staging area is one step better than trying to combine that data some other way, such as cutting and pasting in Excel.


Benefit Number Three – Data Validation

You can validate data and mark invalid data as invalid.

Our staging areas carry a flag to indicate the validity of every row of data.

It defaults to valid and then your IT colleagues can write routines against the data to search for invalid data and mark that data as invalid.

Then those rows can be omitted from any reporting or analysis that is being done.

These validity checks can also access data from outside Business Central that is also in the staging area.

This means that you have the chance to mark data as invalid using a wide range of data and complex routines.

One of the biggest problems when reporting from operational systems, and copies of them, is that invalid data creeps into the reporting and analysis being done.

Being able to mark that data as invalid and exclude it from reporting is a very important feature to have.


Benefit Number Four – Fraud Prevention

We would all like to believe that every person working for our company is an honest person.

Sadly, this is not the case.

In many companies employees have committed fraud using the computer systems they have access to.

Such cases of fraud have become much more prevalent as computer systems became more complex and electronic business interactions became more common.

Today it is easier than ever for someone who wishes to defraud their company to do so through complex computer systems.

One of the most common ways of defrauding a company is for people to enter documents and then later delete them to try and “cover their tracks”.

This is similar to what Nic Leeson did in the very famous case of Barings Bank.

Because a staging area retains deleted rows this mechanism of fraud is reduced.

In fact, when a company wide announcement is made that all data entered into computer systems is retained in a separate staging area, this is often enough to discourage would be fraudsters.

This is because such an announcement makes clear that s/he is very likely to be caught.

One of the most powerful deterrents in discouraging people from committing fraud is to explain to them that they are very likely to be caught.


Benefit Number Five – Fraud Detection

Closely related to Benefit Number Four is the detection of fraud if an employee decides to try his/her luck.

Algorithms can be placed to run on the staging area every day looking for possible fraudulent transactions.

These possible fraudulent transactions can then be brought to the notice of the appropriate employees to investigate.

These algorithms generally have to store their results in the staging area as well.

They also do better with more history.


Benefit Number Six – Getting Ready For A Data Warehouse

As discussed above, a staging area provides a number of benefits over and above operational systems replicas.

They can be very beneficial.

This has also been seen in a similar concept called a “Data Lake”.

However, no matter how much benefit can be derived from a staging area, it can not be compared to a proper data warehouse.

A proper data warehouse will contribute much more profit to your company than just a Data Lake or Staging Area.

A staging area is a pre-requisite for a data warehouse.

One of the best reasons you might want to implement a staging area over just a replica of Business Central is to get ready for implementing a proper data warehouse.

There is no better return on investment a company can make than to implement data warehousing properly.

Summary

In summary.

We have released a script to rename Business Central tables and columns to make it cheaper and easier for your IT colleagues to write direct queries against your Business Central database.

You may use that script on your current production database or a replica.

It’s free and it will save your company money.

However, you might want to give consideration to taking the next step up to a staging area.

We have listed the six main benefits of upgrading to a staging area.

For the very minor extra investment we believe these benefits are well worth it.

If you would like to have your IT colleagues review how our staging areas are implemented?

We are willing to make an Evaluation Database, and all the code, available to your IT department for them to evaluate.

There is so much there it’s just easier to give it to your IT Colleagues than to explain it.

Once your IT department gives you the green light that they can support such a staging area.

Then you and your colleagues can make your decision as to whether you wish to gain the six listed benefits above for the small investment required.

If you would like to contact me?

Please just use the “Contact Us” button below.

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.

BIDA0042 – 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.


(Note. We have swapped my photo for the cartoon character. I hope you like it!)

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.

BIDA0041 – Meta5 As A Black Box

0

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


(Note. We have swapped my photo for the cartoon character. I hope you like it!)

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 really big news for former Metaphor employees and customers.

We have been very busy on a number of fronts.

One of those fronts is this.

We have been trying to find a way to create “Meta5 as a black box”.

I am very pleased to report that we have been successful.

We are, literally, putting the finishing touches on how this works.

We wanted to let the former Metaphor community know about this sooner, rather than later.

So, if you are a former Metaphor employee, or customer?

Please read on or listen on.

Introducing Meta5 As A Black Box

Those people who have used Metaphor back in the 1990s know and understand that it empowered business users to make decisions to improve the profitability of their company.

People who used it know how good it is and the difference it can make.

But Metaphor kind of got lost in the world of IBM.

We would like to “Make Meta5 Great Again”, if we can use that term in the US where so many people are so polarized about politics.

One of the areas where we have been investigating is the idea of making Meta5 available to the mass market.

Today, Meta5 has added many features that make it a great complementary tool to Excel.

So our investigation work has centered around making Meta5 a mass market product of great value to companies who like to do their reporting in Excel.

So please allow me to explain what is now possible.

Please let me apologize for the formatting of the ordered list on blog post.

It seems WordPress does not allow good spacing inside an ordered list.

These are the top 10 features of what is now possible:

  1. The SQL that can be sent to the data source can be up to 64K per statement.
  2. The SQL can be hand written and as complex as the database can support.
    This is very important because with CTEs supported in most databases today it is possible to create very complex statements that ordinary users can not write for themselves.
    These are SQL statements so complex that that Excel itself can not send them into databases.
  3. The SQL statements can contain placeholder parameters which can be set to real parameters before the SQL is sent to the data source.
    This is very important. For example, if a retailer has 20 region managers and each region manager wants his/her own suite of reports containing just his/her data?
    There would be one set of SQL.
    Each SQL statement would contain a variable for “region-manager” and then that can be set at run time.
    Meta5 will deliver, say, 10 reports to each region manager, for a total of 200 reports.
    Most likely these will be delivered by one drive.
  4. PowerShell commands can be executed by Meta5.
    This means that pretty much any function that needs to be executed on Azure, or involve Azure, can be executed from Meta5 or the BIDA scheduler.
    For example reports can be sent to one drive using PowerShell without the server itself being logged into one drive.
  5. Meta5 Capsules can be entirely self contained.
    This is very important.
    Each Meta5 capsule has 208 variables that can be set.
    Usually these are set by a site and vary from customer to customer.
    Historically, capsules were never moved from customer to customer.
    Historically there was never an effort to create Meta5 capsules and sell them on the market.
    We have changed that.
    Now we have the ability to set a “master parameters” database at the desktop level.
    All 208 parameters are retrieved from the master parameters database.
    This means that one capsule can be written that can run on many companies data because the variables will be retrieved from each companies own master variables database.
    If a company runs out of variables at 208 they can simply create a second set of 208 variables and start using them.
    But any single capsule is restricted to 208 variables.
  6. Each capsule can have it’s own variables that over-ride the company global variables.
    Most capsules have their own internal variables that they need to use that over-ride the global company variables.
    Or they may over-ride reserved variables.
    For example there is a now a variable called capsule-name.
    This is a reserved global variable that is set by each individual capsule.
    The result of 5/6 is that we can write one capsule for an ERP like Business Central.
    We can then put it into the business central store and sell it to ANY installed BC account.
    Other members of the former Metaphor community might wish to do the same.
  7. Meta5 can schedule capsules on at timer.
    Meta5 has a feature to schedule capsules to run at a certain time.
    Meta5 also has a feature to start a capsule based on a windows command.
    So a user can run a capsule simply by double clicking the command to run it.
  8. A capsule can wait for a “signal file” from the data source to tell it that it has finished processing.
    Say an ERP is running late in processing and the capsule is scheduled for, say, 4am, but the ERP has not completed processing.
    What is available is a feature for the ERP to ftp a signal file to somewhere the Meta5 server can see it when data is ready.
    The Meta5 capsule can wait until that file has arrived before running.
    This use of signal files is standard across most ERP implementations to let downstream systems know they are finished processing certain data.
  9. The signal file deletion is optional.
    If the customer wants a capsule to run on it’s schedule every day there is a feature to turn off the deletion off the signal file.
    The signal file would be created at capsule installation time and never deleted.
  10. Removal of temporary workbooks from Excel.
    Having retrieved data from any ODBC source, including any CDATA ODBC source, we can send that into Excel workbooks.
    We can then load it from the excel workbooks into the power pivot models.
    We can then refresh all reports/dashboards with the new data in the power pivot model.
    We can then delete the workbooks where the data was sent into Excel so they do not need to be “hidden” from the user.
    The user then gets a workbook with his/her data ONLY in the power pivot model and no redundant worksheets in the workbook.

Given all of the above?

We, or you, can write a capsule once, and then sell that one capsule many times over to many customers.

When you put all this together it means that we now know how to create much more valuable dashboards in Excel, sourcing data from common operational systems.

The “barrier to entry” is deep knowledge of the operational system that you are writing the dashboards against.

But this will work for ANY system, and that includes ANY large system that is widely used.

All we (or you) have to do is develop dashboards better than the vendor has delivered, and better than other people doing the same have delivered.

Of course, if you are a former Metaphor employee or customer, then you might very well have an interest in having your company investigate this whole idea of “Meta5 as a black box”.

There are a number of angles to investigate this from.

Firstly, in using Meta5 inside your company to generate Excel dashboards that you have not been able to get to work any other way…yet.

Or Secondly, we are sure there are some other Meta5 literate people out there who would like to create capsules and sell them to customers on the open market.

We will be doing Business Central and the InfoSys Finacle banking system.

So we would prefer no competition in those spaces for a while.

But there are a lot of large common operational systems (as well as installed data warehouses) where “Meta5 as a black box” would be a very sellable item.

At least we hope so!

I hope that this blog post has been interesting to the former Metaphor community.

Please pass this blog post along to those you know who used to be a part of the Metaphor community.

The idea of “Meta5 as a black box” means that Meta5 could be widely used again to the great benefit of Meta5 customers.

We hope all those people who were once part of the Meta5 family will have an interest in promoting this idea.

If you are interested and would like to be notified when we bring out the demo?

You can contact me on our “Contact Us” button below.

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.

BIDA0040 – InfoSys Finacle Version 7 Column Table Renames Available

0

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


(Note. We have swapped my photo for the cartoon character. I hope you like it!)

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.

We have some really big news for our readers.

We have been very busy on a number of fronts.

And one of those fronts is this.

We are building an end to end data warehouse for a client based on the Finacle Version 7 Core Banking System by Infosys / EdgeVerve.

This has been a major undertaking and will go along side our end to end data warehouse solution for Business Central.

To the best of our knowledge this is the largest ever product oriented data warehouse development undertaking, if you measure the number of fields being put into the data warehouse.

Today we are making a significant announcement for the installed users of Finacle Version 7.

If you have Finacle Version 7 installed?

This is very good news for you and your company.

So if you have Finacle Version 7 installed?

Please read on or listen on.

Introducing Renaming of Finacle Tables and Columns

I think it’s safe to say that no one will criticize me for saying that the naming standards of fields in Finacle Version 7 could stand some improvement.

It’s quite an old system that was created in the days of the 30 character name limit in Oracle.

But who would ever be crazy enough to attempt to provide better alias names for all the columns in Finacle?

Well?

That would be us.

We have now completed a process where we have provided aliases for all 56,000 fields in Finacle Version 7.

Of course, now that we have spent months and months doing this renaming of Finacle columns we have the capability to give any Finacle Version 7 installed account a create view script that will alias all your tables and columns.

This create view script will provide you with a significant cost reduction in development, maintenance and support of all internally written reports / queries against your Finacle operational system data models.

This is because the column names are more intuitive, easier to read, easier to remember, and easier to use.

By simply installing this suite of create view statements you can start to reduce the cost of all reporting development done directly against your Finacle Version 7 system.

But wait…there’s more.

One of the things we did was scan all the Finacle PDF Documentation available for the data models and put them into our customers dictionary.

The scanning was not perfect, but it’s much better than reading PDFs.

This means that we can give you Excel based, searchable, documentation for Finacle Version 7.

I can assure you this is much better than reading the PDF manuals.

If you will excuse the obvious marketing line…

But wait…there’s more!

We are offering this create view script, plus the documentation that we have created ourselves from the PDF documentation, to any proven installed Finacle Version 7 account…

FOR FREE!!!

Yes, you heard that correctly.

We will give you the benefit of the hundreds of hours of effort of our team in renaming 56,000 fields inside Finacle Version 7, for free.

In turn, this create view script will save you hundreds, if not thousands, of hours of developer time over the coming years.

Of course, we have done all this work under a Non Disclosure Agreement.

A part of our purpose is to make life easier and better for Finacle installed accounts.

We are working with InfoSys and we are co-operating with InfoSys.

So to take advantage of this great offer?

All we need from you is a scanned copy of the Non Disclosure Agreement your bank signed with Infosys for Finacle Documentation, and a copy of your personal Non Disclosure Agreement that binds you to the Infosys Non Disclosure Agreement.

If that is included in your employment contract then an email to that effect from HR would be fine too.

Once we have confirmed that you are covered by a legally binding Non Disclosure Agreement we can send you the Oracle script and the associated documentation.

Below is a picture showing an example of the documentation we have generated based on our work.

You can click on the image to get the full size to be more readable.

This is the loan account master table.

Everyone who has Finacle installed knows this table.

The columns are self explanatory for the most part.

You can see Column Name and New Column Name.

You can see that the Finacle Column Name has an alias that is more readable and more understandable.

And yes, we really do mean we renamed all the fields that did not have good names in our opinion. All 55,000 of them. About 1,000 fields we left without a new name.

On the left hand side of the report you see two columns that I would like to explain.

The Stage Table column is the name of the table in our data warehouse staging area.

This name can not be changed.

The next column is of interest to you.

This is the Stage View name.

If you want to create new view names for your Finacle renaming process, then you can just fill in new names in this field of an excel input workbook and that will be the new name of the view over the top of your Finacle tables.

So you have the ability to not only use the default view names we provide in our script.

You have the option of creating a new set of view names to make your set of views that much easier to use for all your developers.

Below we have provided you with a couple of examples of the create view scripts just so you can see we are serious about this free offer.

You can see that the default is to simply prefix the table in question with a “v” for “view”.

You can create any view name alias you want.

We sincerely hope that you will take advantage of this free offer.

The guys who did the whole renaming process would love to see their work put to good use!

You can contact me on our “Contact Us” button below.

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.

Example Create View Scripts

Please note that wordpress removes the multiple blanks between the column name and the alias for the view.

The real code is a little more readable.

Of course it is generated from our dictionary.

These examples make it clear that we have done our best to give better names to every Finacle column.

Of course, if you do not like our names you can always start with our renaming and update the aliases in any way you like!


create view tbaadm.vacct_entry_table as select
sol_id pk_service_outlet_num
,bank_id pk_bank_unique_num
,bill_id pk_bill_unique_num
,tran_id pk_transaction_num
,part_tran_srl_num pk_part_transaction_serial_unique_num
,entity_cre_flg row_verified_flag
,del_flg row_deleted_flag
,ref_code bill_reference_code
,tran_date transaction_date
,bill_func bill_function_code
,dtd_ind mass_bill_consolidation_flag
,tran_type bill_transaction_type_code
,tran_sub_type bill_transaction_sub_type_code
,part_tran_type part_transaction_type_code
,acid general_bank_acct_num
,tran_amt bill_transaction_amt
,value_date transaction_value_date
,rpt_code bill_report_code
,ref_num part_transaction_reference_unique_num
,instrmnt_type bill_transaction_instrument_type_code
,instrmnt_date bill_transaction_instrument_date
,instrmnt_num bill_transaction_instrument_unique_num
,instrmnt_alpha instrument_alphabetical_identification_type_code
,tran_particular bill_transaction_comments_ldesc_01
,tran_rmks bill_transaction_comments_ldesc_02
,prnt_advc_ind transaction_print_advice_code
,restrict_modify_ind transaction_values_modification_restriction_code
,voucher_prnt_flg bill_transaction_voucher_print_flag
,module_id banking_system_module_code
,br_code branch_num
,lchg_user_id last_change_userid
,lchg_time last_change_tstamp
,rcre_user_id create_userid
,rcre_time create_tstamp
,consolidation_ind bill_transaction_consolidation_code
,ptran_ref_ind bill_transaction_partial_reference_code
,ref_tran_id reference_transaction_num
,ref_tran_date reference_transaction_date
,ref_part_tran_srl_num reference_partial_transaction_serial_unique_num
,ref_oclg_zone_code reference_outward_clearing_zone_code
,ref_oclg_zone_date reference_outward_clearing_zone_date
,ref_oclg_set_num reference_outward_clearing_set_unique_num
,ref_oclg_part_tran_srl_num reference_outward_clearing_serial_unique_num
,navigation_flg originating_responding_navigation_code
,bank_code bank_num
,ts_cnt row_modified_counter
,ref_oclg_sol_id reference_outward_clearing_service_outlet_num
,gl_date bill_transaction_general_ledger_date
from tbaadm.acct_entry_table
;


create view tbaadm.vcollection_gen_maint_tbl as select
sol_id pk_service_outlet_num
,bank_id pk_bank_unique_num
,collection_id pk_collection_serial_unique_num
,collection_code collection_code
,collection_crncy collection_iso_currency_code
,collection_b2k_id collection_b2k_event_num
,cif_id customer_unique_num
,oper_acid operative_bank_acct_num
,oper_charge_acid operative_charge_bank_acct_num
,collection_amt collection_amt
,collection_cntry_code collection_country_code
,grace_perd collection_grace_period_days_num
,lodg_date collection_lodgement_date
,due_date collection_due_date
,delink_date collection_delink_date
,corr_coll_bank_code correspondent_collection_bank_num
,corr_coll_br_code correspondent_collection_branch_num
,corr_bank_name correspondent_bank_name
,corr_bank_addr1 correspondent_bank_swift_address_line_01
,corr_bank_addr2 correspondent_bank_swift_address_line_02
,corr_bank_addr3 correspondent_bank_swift_address_line_03
,corr_bank_cntry_code correspondent_bank_country_code
,collection_status collection_status_code
,tran_sol_id transaction_service_outlet_num
,current_func_code current_bill_function_code
,collection_liab collection_liability_amt
,collection_liab_crncy collection_liability_iso_currency_code
,cls_flg collection_closure_flag
,cls_date collection_closure_date
,free_code_1 user_defined_code_01
,free_code_2 user_defined_code_02
,free_code_3 user_defined_code_03
,cont_cr_liab_acid contingent_credit_liability_bank_acct_num
,cont_dr_liab_acid contingent_debit_liability_bank_acct_num
,cont_rate_code contingent_rate_code
,cont_rate contingent_rate_percentage
,cont_crncy contingent_transactions_exchange_iso_currency_code
,cont_liab_amt contingent_liability_amt
,reminder_num_sent reminders_sent_num
,next_reminder_date next_reminder_scheduled_date
,last_reminder_sent_date last_reminder_sent_date
,date_of_remit commission_remitance_date
,bef_rpt_srl_num bef_report_serial_unique_num
,bef_rpt_date bef_report_date
,remit_amt_in_hc home_currency_remittance_amt
,entity_cre_flg row_verified_flag
,del_flg row_deleted_flag
,rcre_user_id create_userid
,rcre_time create_tstamp
,lchg_user_id last_change_userid
,lchg_time last_change_tstamp
,ts_cnt row_modified_counter
,party_name remittance_lodged_party_name
,party_addr1 remittance_lodged_party_address_line_01
,party_addr2 remittance_lodged_party_address_line_02
,party_addr3 remittance_lodged_party_address_line_03
,party_city_code remittance_lodged_party_address_city_code
,party_state_code remittance_lodged_party_address_state_code
,party_cntry_code remittance_lodged_party_country_code
,party_pin_code remittance_lodged_party_address_pin_code
,purpose_of_rem purpose_of_remittance_code
,passport_det party_passport_details_ldesc
,tc_amt travelers_checks_issued_amt
,crncy_amt issued_currency_amt
,cntry_of_visit visit_country_code
,corr_bank_city_code correspondent_bank_address_city_code
,corr_bank_state_code correspondent_address_state_code
,corr_bank_pin_code correspondent_address_pin_code
,nostro_acct_num nostro_bank_acct_num
,other_cif_id other_party_customer_unique_num
,other_party_name other_party_name
,other_party_addr_1 other_party_address_line_01
,other_party_addr_2 other_party_address_line_02
,other_party_addr_3 other_party_address_line_03
,other_party_city_code other_party_address_city_code
,other_party_state_code other_party_address_state_code
,other_party_cntry_code other_party_country_code
,other_party_pin_code other_party_address_pin_code
,ps_ind purchase_sale_flag
,transit_perd collection_transit_period
,encash_cert_det encashment_certificate_ldesc
,cdf_details currency_declaration_form_ldesc
,dclrd_eefc_amt declared_export_earnings_foreign_currency_amt
,eefc_acid export_earnings_foreign_currency_bank_acct_num
,eefc_amt export_earnings_foreign_currency_amt
,cum_eefc_amt cumulative_export_earnings_foreign_currency_amt
,ref_collection_id reference_remittance_collection_serial_unique_num
,bp_liab remittance_purchase_liability_amt
,bp_liab_crncy remittance_purchase_liability_iso_currency_code
,collection_liab_hc_eq remittance_purchase_liability_home_currency_amt
,bp_limit_b2kid remittance_purchase_limit_b2k_event_num
,bp_dlnk_limit_b2kid remittance_purchase_delink_limit_b2k_event_num
,invt_class inventory_location_class_code
,fc_int_conv_rate bill_liability_to_interest_conversion_rate_percentage
,fc_int_conv_rate_code bill_liability_to_interest_exchange_rate_code
,int_on_liab_flg calculate_interest_on_liability_code
,int_calc_crncy_code interest_calculation_iso_currency_code
,rear_end_int_flg calculate_interest_after_purchase_flag
,int_peg_flg peg_calculated_interest_flag
,peg_frequency_in_months peg_frequency_interest_months_num
,fb_ref_num foreign_bank_serial_unique_num
,ref_date foreign_bank_serial_unique_num_assigned_date
,peg_frequency_in_days peg_frequency_interest_days_num
,paysys_id payment_system_unique_num
,benef_coll_bank_code beneficiary_collection_bank_num
,benef_coll_br_code beneficiary_collection_br_code
,benef_bank_name beneficiary_bank_name
,benef_bank_addr1 beneficiary_bank_address_line_01
,benef_bank_addr2 beneficiary_bank_address_line_02
,benef_bank_addr3 beneficiary_bank_address_line_03
,benef_bank_cntry_code beneficiary_bank_country_code
,benef_bank_city_code beneficiary_bank_address_city_code
,benef_bank_state_code beneficiary_bank_address_state_code
,benef_bank_pin_code beneficiary_bank_address_pin_code
,dd_print_count demand_draft_printed_num
,last_dd_printed_date last_demand_draft_printed_date
,against_import_export inward_remittance_advance_against_export_flag
,intermediary_trade intermediary_trade_country_flag
,rbi_permit_det rbi_permit_comments_ldesc
,drawee_centre_code drawee_centre_code
,drawee_code drawee_code
,notl_conv_rate notional_conversion_rate_percentage
,notl_conv_rate_code notional_conversion_rate_code
,fircr_print_cnt foreign_inward_remittance_certificate_printed_num
,first_fircr_print_date first_foreign_inward_remittance_certificate_print_date
,last_fircr_print_date last_foreign_inward_remittance_certificate_print_date
,free_field user_defined_ldesc_01
,acct_num general_maintenance_bank_acct_num
,corr_coll_bic correspondent_collection_bank_identifer_code
,benef_coll_bic beneficiary_collection_bank_identifer_code
from tbaadm.collection_gen_maint_tbl
;

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

HD0015 – How Do I Add A New Data Disk Pod

0


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.

In this blog post we are going to show you how to add a new data disk pod.

Let us presume you want to add a new 10GB disk pod.

1. Go to the directory: C:\Program Files (x86)\Meta5\exe\

Enter the command: fsinit "C:\ProgramData\Meta5\Pods" 10000 data01

The fsinit utility will create a subdirectory called “data01” in the above named directory and put the pod data files in that directory.

You should see something like this on your PC.

 

2. With Meta5 running on your desktop click on the Meta5 icon in the tray and select “Meta5 Starter Edition Configuration”

You should see this window.

3. In the text box “Server Drive Paths” the value set is "C:\ProgramData\Meta5\Pods\System".

We need to add the new pod to that list:

C:\ProgramData\Meta5\Pods\System,C:\ProgramData\Meta5\Pods\data01

If you have an existing pod that you want to add then the process is the same at this step by adding the path of the pod directory to this text box (comma separated) at which point there is nothing else you need to do as the pod should come online when you start Meta5. For a new pod, more work is required.

4. Start Meta5 normally.

Then start a new command prompt and go to the directory: "C:\Program Files (x86)\Meta5\exe\

Run the following command:

m5cmd fscnsl

This command will open a File Service Console window.

Login as “system” with a password of “original” (it might prompt you to set the password the first time and then login).

Then issue the following commands.

Each pod has a unique “number” assigned to it with the “/#” switch.

The demo has “10” but you can use any legal number between 2 and (I think) 16.

The actual commands are in red and bold so that you can see the response you should expect.

Meta5 File Service Console
Version 4.3.5
Licensed Materials - Property of Meta5, Inc.
Copyright (C) 2000-2023 by Meta5, Inc. - All Rights Reserved


Username: system
Password: ********
Connecting to 'system' console...
system>det
Slot A1: Disk module "System, #1" is online
Size 11065MB: 10936 file descriptors (0%), 249257 data blocks (2%)
Slot A2: Disk module "data1, #10" is in undefined state
Slot A3: blank
Slot A4: blank
system>erase a2 /#:10
ERASING drive A2.
Drive number: 10.
Drive name: unchanged or set to default if new drive.
File descriptors share: unchanged or set to default if new drive.
Are you sure?
(Y/N)? y

[09:22:24] Slot A2: Erasing.
Disk space to be allocated to file descriptors: 25%

[09:22:24] Slot A2: remote operation is in progress.
system>
[09:22:31] Slot A2: Offline.
online all

[09:22:37] Slot A2: Online.

[09:22:37] Slot A1: Already online.

[09:22:37] Slot A2: done.
system>det
Slot A1: Disk module "System, #1" is online
Size 11065MB: 10936 file descriptors (0%), 249259data blocks (2%)
Slot A2: Disk module "data1, #10" is online
Size 10245MB: 4 file descriptors (0%), 11 data blocks (0%)
Slot A3: blank
Slot A4: blank
system>exit

5. You should now have an system desktop that looks like this.

We hope you found this How do I Presentation useful and informative.

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

We really appreciate your time and attention.

I wish you a nice day.

Mihai.

HD0014 – How Do I Run PowerShell Commands In 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 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 the demonstration today.

In today’s demonstration, we are going to show you how to run Power Shell commands in Meta 5.

This is very useful because you can do such things as send e-mails, send reports to One drive, and download things from One Drive.

You can do all sorts of useful things once you can run a Power Shell command from inside Meta 5.

Now on with our demonstration!

Here we are on our Windows desktop running Meta 5 in the background.

We have a file directory in front of you, which is the BIDA hosting schedule directory.

This is where we put our Power Shell commands.

I will take you through each of the relevant files.

The first file you need, is a file to create the secure string, which will be your password.

You can just run this command, enter your e-mail address, and it will put the encrypted password into this file for you.

To show you what that looks like, I will just click on the encrypted password file.

It is just a series of numbers and letters.

It is the password to the BIDA common e-mail address encrypted.

The next thing you need is a bat file to run a PowerShell command.

This is a very simple file.

You can see the power shell exe command.

But notice the No Profile and execution policy bypass parameters.

These parameters are required with Meta 5 to bypass script security for Power Shell commands.

This is because Meta Five runs under a different profile than the Windows desktop.

So you need to remember to put in no profile execution policy of bypass.

Then you have the command parameter which is just a positional parameter.

That positional parameter is the command that you want to run.

In our example, we want to run a command to send an e-mail to say that the ETL batch has finished.

That is a very common thing that you want to do in data warehousing.

You can see the first line is the name of the e-mail address, that is going to send the e-mail.

The password you can see is, get content from the login password text file, and convert it to a secure string.

So we are getting that password, we are running it through convert to secure string, and we are assigning that to the password variable.

And then we are creating credentials of a new object, which is Automation ps credential.

The argument list is the administration name, the e-mail, the user we want to log in with, and the password for that user.

From there we just send the mail message from BIDA common to Peter’s business e-mail.

The subject is the BIDA weekday batch has completed.

The body of which is also the BIDA weekday batch has completed.

We are using the S M T P server of Office 3 6 5.

We are using the credentials of credential and we are saying use S S L and use port 5 8 7.

Now one note to mention.

This e-mail address must be authorized to send S M T P emails, and the default is to not be authorized.

You can just do a Google search and ask how do I authorize Office 3 6 5 e-mail address to use S M T P e-mail.

Now, there may be other ways of sending emails.

We happen to be using S M T P, so those are the commands that you need.

The text will be on the blog post, and you can see the commands here in the directory.

Now we are going to go over to the Meta 5 desktop.

We are going to open this capsule, run bat commands with results.

We have an icon, run bat file, and the icon is the I application icon.

So when you’re looking for it in new icons, you look for Integrator Icons and the I application icon in the Integrator Icons folder.

We have given it the name run bat file and this can be any PC application.

We say, execution information, Global application.

We have the Execution file parameter.

In here we see the path name.

I will zoom in on it for you.

It is BIDA, beta hosting, C 12, schedule.

You can see it in the video.

The file is run power shell command dot bat.

The exact command that you saw.

Then the command line parameter is the same folder.

And it runs the command run M O 1, send E T L, e-mail, P S 1.

The working directory is also entered.

Now I am just going to run the command.

It is going to execute that Power Shell command.

All we need to do is hit run and you will see it just takes a second.

Now it says complete, please continue.

Because I don’t want to show you Peter’s inbox, you can just trust me that the e-mail actually arrived.

OK!

That completes our demonstration today.

Today we showed you a very simple way of how to run Power Shell commands in Meta 5.

Thus, making the full power of PowerShell available to you, from inside the Meta 5 desktop, and from inside Meta 5 Capsules.

We hope you found this How do I Presentation useful and informative.

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. Your BIDA AI Assistant.

The commands that you saw in the video are as follows:

run-this-as-a-powershell-command.ps1
Read-Host -Prompt "Enter your email password" -AsSecureString | ConvertFrom-SecureString | Out-File "M:\A_BIDA_HOSTING\BIDAHS_C012_BC\S01-Schedule\bida-common-login-password.txt"

RunPowerShellCommand.bat
PowerShell.exe -noprofile -executionpolicy bypass -Command %1

run-m01-send-end-etl-email.ps1
$AdminName = "bida.common@businessintelligenceda.onmicrosoft.com"
$Pass = Get-Content "M:\A_BIDA_HOSTING\BIDAHS_C012_BC\S01-Schedule\bida-common-login-password.txt" | ConvertTo-SecureString
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Pass
Send-MailMessage –From bida.common@businessintelligenceda.onmicrosoft.com –To peter@bida.ro –Subject "BIDAWeekDayBatch001 - Batch Completed" –Body "BIDAWeekDayBatch001 - Batch Completed" -SmtpServer smtp.office365.com -Credential $cred -UseSsl -Port 587

BIDA0039 – BC365 OData Query Using 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 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