BIDA0015-ETL Performance Improvements Case Study

0


Hello and welcome to our latest blog post.

We really appreciate you coming by to read or listen to our latest blog post!

So thank you for dropping by!

The text of this video will be a little less formal because this blog post is intended to be listened to as an audio rather than read on line.

We realize that you are very busy and you are more likely to listen to this blog post as an audio than you are to read the blog post itself.

So because this blog post is intended to be listened to? The text will be in a little more detail than usual.


Introduction

Now, on the front page of our web site we ask you questions about whether you are satisfied with your ETL system.

We pose questions about the speed of processing, the delivery time of the data and the cost of adding new tables and fields.

And we ask that if you are not satisfied with your ETL in any way, shape or form, we would like to talk with you and hear what your problems are.

We are pretty sure we can help you.

Today we are going to share with you an example of performance improvement at a local Romanian company we have been working with over the last 5 years.

Indeed, we were able to gain the confidence of this company by showing them some of the improvements we could make to their ETL way back in 2015.

To give you just a little background? They are a retailer with a chain of stores nationwide.

Pretty much everyone in Romania knows their name.


The Story Of ETL Performance Improvement for this Customer

So here we go with the story of how we have improved the ETL performance, and data coverage, for this Romanian retailer.

Ok?

The ERP involved is Microsofts Navision and so SQL Server was chosen as the target data warehouse platform.

The first release of the data warehouse had been implemented in 2012 using SQL Server 2008R2 because it was the latest stable release when the data warehouse went live.

After some years of operation the customer was having problems getting the data delivered to their users at a reasonable time.

In this case the data from the overnight processing in Navision was not available to the business users until after lunch, around 1pm.

What were some of the issues in the ETL System? Perhaps your ETL system contains some of these issues.

1. Using Stored Procedures For ETL Development

We do not recommend using Stored Procedures to build ETL Systems where they can be avoided.

Because Stored Procedure Languages differ from database to database you immediately lock your self in to that database if you use SPs as your ETL.

Today, we recommend ETL be written in ANSI Standard SQL Statements.

Where tools are used to perform ETL they should be database independent.

It should always be possible to move your data warehouse database to a different database even if you never actually do that.

It helps in negotiating license fees.

Stored Procedures are also the single most expensive approach to writing ETL systems.

2. Serial Execution Of ETL Processing

Because the ETL was written with Stored Procedures they were executed in serial.

It is difficult to design sql server jobs to execute Stored Procedures in parallel with reliable testing for success of groups of stored procedures.

This lack of parallel processing of the ETL system meant longer elapsed times for the ETL.

3. No Staging Area

There was no staging area constructed.

So the ETL processing, which lasted around 7 hours, was drawing data from the ERP production system directly that whole period.

Not only does this slow down the production ERP but the data that is being extracted is changing over the time of the extraction because business processing was occurring during the extraction period.

4. Incremental Updates Not Implemented

Incremental extracts and updates were not properly implemented.

It is quite difficult to implement incremental update processing properly across an entire data warehouse.

When incremental update processing is not properly implemented this leads to reprocessing of data that does not need to be reprocessed.

This reprocessing adds directly to the ETL run times and the delay of data for the business users.

In this particular case tens of millions of rows of data that had not changed were being re-created and re-processed.

5. Clustered Fact Tables Rather Than Partitioned Fact Tables

The largest fact tables were created as clustered tables using a clustered primary index.

The ETL was then reprocessing many rows in each of the largest fact tables.

To do this re-processing the ETL was re-creating the rows for up to 2 months, deleting the rows in the target fact table, and inserting the re-created rows.

These deletes and inserts were logged and the combination of the logging and the maintenance of the clustered indexes added a great deal of time to the fact table processing.

6. No Multi-Level Summary Tables

There were no multi-level summary tables as per Ralph Kimballs publications.

So once all the data was processed in to the data warehouse the reporting side of the project had to read the detailed transaction fact tables to populate “reports” for the users.

So not only did the ETL finish around 1pm. The reports that were then produced also had to be run by the users to get their results and the run times were long because detailed fact tables were being scanned.

So that was the situation we were faced with 5 years ago.


How did we address some of these issues over the last 5 years?

1. Manually Partition Fact Tables

The first thing we did was to implement an example of a partitioned fact table in test.

Then we ran the exact same ETL for the production fact table against the manually partitioned fact table in test.

Obviously the performance was about 30% faster than the production clustered fact table.

This got their interest because they had never considered manual partitioning.

Database partitioning was not supported in SQL Server 2012 Standard Edition.

They did not want to pay for SQL Server 2012 Enterprise Edition to get database partitioning.

2. Delta Detection To Eliminate Unnecessary Database Processing

We then implemented a simple delta detection algorithm for the partitioned fact table.

This meant that rather than deleting and re-inserting up to 2 months worth of rows, just those rows that needed to be inserted, updated or deleted were processed.

This cut run times by about 60% from the original processing times for this prototype fact table.

3. Introduction of Multi-Level Summary Fact Tables

In his early works in the 90s Ralph Kimball wrote about how a great deal of query time can be saved by having multi-level summary fact tables.

His product “Red Brick” implemented this idea.

SSAS in SQL Server and all cube products implement a version of this idea.

The customer had not implemented any multi-level summary fact tables.

We implemented sample tables for the customers sales transactions and GL transactions to show how the reports would run much faster.

The customer was a MicroStrategy customer.

MicroStrategy has built in features to navigate multi-level summary fact tables because many of the employees who worked at MicroStrategy in the 90s were trained by Ralph Kimball.

Once the then prospect saw that we could make all these improvements in their ETL Processing and Report Query times with relatively simple changes they chose to engage us to support their ETL and data models.

This was way back in 2015.

We then implemented these very simple ideas in to their production ETL system and the run times went down to about 5 hours from about 7 hours.


In 2016 the client wanted to upgrade both the ERP and the Data Warehouse from SQL Server 2008R2 to a later release.

They chose SQL Server 2014 Standard Edition.

Unfortunately the Optimizer had been altered and the extraction SQL from the production ERP could not be made to work on the new version of SQL Server.

After exhaustive testing to get the old SPs to work on the new version of the database failed we chose to implement a staging area.

We had planned to implement a staging area at some point in time.

The database upgrades simply brought the plan forward.

We used the Navision timestamp field to perform incremental extracts from the Navision ERP.

We chose to replicate the entire production Navision System in to the staging area and then repoint the production ETL SPs to the staging area.

Of course, this added an hour of ETL processing time so our processing times went back up from around 5 hours to around 6 hours.

This would be a temporary increase as we would now be able to have greater control over the staging area in terms of indexing and processing loads.

We had previously had to be very careful of processing loads on the production ERP caused by the ETL SPs.

We devised a way to create the staging area that was very quick to create.

And from our learning and experience in that area we can now create staging areas for Navision systems in a weeks work.

We have published a detailed white paper on how to create staging areas for Navision on our web site.

By having the staging area on the same machine as the data warehouse we were able to persuade the optimizer to query the staging area using the same access paths as had previously been used on the Navision Production ERP.

More importantly the full extraction process ran in just 30 minutes per day so the extraction could be run out of business hours and when almost no changes were happening inside the production ERP.

We implemented a delta detection processing algorithm for the 800 or so tables that had data in them.

So we could easily tell which rows in the staging area had changed using a flag rather than using a timestamp.

This was important in implementing incremental updates in later years for more tables being sent in to the data warehouse.

So in 2016 we were having run times of around 6 hours and the old Stored Procedures we inherited were still running serially.

The next major improvement we proposed was to implement the BIDA Scheduler.

The BIDA Scheduler is a very good scheduler that allows the creation and management of “processing groups” that can be processed in parallel.

These “processing groups” can be followed by a “semaphore” group to make sure all processing groups in the current group are complete prior to continuing to the next set of processing groups.

So, please let me provide a simple example of why parallel processing groups is a good idea.

In this customer about 800 tables in their Navision system have data in them.

Most tables are small. But, of course, the sales transactions and the GL transaction tables have quite a lot of rows added to them every day.

And these tables are important to be reported on as early as possible each day. Especially the sales table.

When using Stored Procedures to perform ETL processing the ETL processing is done one table after another.

So the sales transactions must be extracted, and then the GL transactions must be extracted.

The BIDA Scheduler allows you to create a schedule where the sales transactions and the GL transactions can be extracted at the same time.

Then it can guarantee that all extract processing has been completed successfully before allowing the processing to move on to the delta detection and application of all the changes to the staging area.

With the BIDA Scheduler we were able to define processing so that we could run portions of the ETL in parallel.

This reduces run times.

With the customers agreement we implemented the BIDA Scheduler and we split up the processing of the largest fact tables and ran them in parallel.

With all these changes applied, back in 2016, we were able to get processing down to around 4.5-5 hours.

Much better than the 6 hours or so we were then achieving and much better than the 7 hours or so we had started with.

And with processing times less than 5 hours we could make the 9am deadline pretty reliably every day.

Of course, between 2016 and May 2020 we added much more data to the ETL processing and data warehouse.

The volumes of transactions have increased as their number of stores have increased and the number of sales in stores have increased.

We would keep doing performance tuning as much as was possible to the old Stored Procedures and we wrote all the new ETL using the BIDA ETL tools and we implemented new tables using the BIDA data models.

So the fact that we maintained processing times to around 4.5-5 hours while adding a lot more data and volumes going up is also important.

With improvements in processing in the production ERP itself we were able to consistently move the ETL start time to earlier and earlier in the morning.

We also did a machine upgrade and database upgrade in 2019.

Now, the big reason for this blog post is this.

Recently the customer wanted to decrease the ETL run time significantly.

The customer wanted access to the results of prior day sales at 7am rather than 9am.

Without re-writing much of the now 8 year old ETL Stored Procedures we could not get that performance.

We opted instead for a CPU upgrade and some SSD.

We also chose to do some performance tuning of the old Stored Procedures.

Just enough to get the run times down to where we wanted them.

So on our blog post you will see two images of run times from the BIDA Scheduler.

One before and one after our recent performance tuning.

In the diagrams you can see that on the 5th of May the run time for the Week Day Batch was 4 hours, 29 minutes, and one second.

This is right on or about our target processing times.

We have been happy to see processing times in the 4.5-5 hour range for week day processing.

That has been our target range for the last four years.

Remember, that was down from around 7 hours in 2015 with Monday morning processing often being around 8 hours.

In the second diagram, on the 27th of May, you can see that the processing time for the Week Day Batch was 2h 34m 30s.

That is nearly two full hours reduction in ETL processing!

You can see that we can start the batch at 2:15am and so with processing times of around two and a half hours we know we can reliably, every day, be sure the ETL will be long finished by 7am for the business users.

Indeed, business users who come in very early at 6am will also have access to previous day sales final figures by 6am.

Just as a side note you can see the batch number is 1,272 on the 27th of May 2020. And yes, that does mean that there have been 1,272 batches run using the BIDA Scheduler since we put it in to production in 2016.

It has been very reliable in running this customers ETL System even though the original ETL System was written as Stored Procedures and run from the jobs menu in SQL Server Manager.

Now? What changes did we make to reduce the ETL run times by another 2 hours?

With the faster CPU and the SSD we placed the data that is most used in the ETL processing on to the SSDs for faster processing.

For the most part we put the monthly partitions of large fact tables for “this years data” on to the SSDs.

We also adjusted the level of parallelism for portions of the batch to run more of the workload in parallel.

This required some schedule changes that were relatively easy to do.

We also went in and looked at the most time consuming Stored Procedures and we altered some of the now very old Stored Procedures to improve their performance.

But all in all?

When you consider we had already done a lot of performance work to get the Week Day Batch to run in around 4.5 to 5 hours?

And when you consider we were now able to get that elapsed time down to around 2.5 hours?

We think we did something that is worth telling you about in a blog post!


Summary

Now I would like to summarize what we have shared with you in this blog post because we feel it is important.

We would also like to leave you with some “food for thought” about your ETL system on SQL Server, especially if it is written using Stored Procedures.

We are a startup company and while our company is in start up mode we are focusing on finding customers who are using SQL Server as their data warehouse database and who have problems with their ETL Subsystems.

Of course, we don’t expect you to volunteer the fact if you do have ETL problems.

We know we have to earn your respect by demonstrating to you we know how to fix ETL problems and we know how to improve ETL performance.

If we prove to you that we can do that and you come to respect that we can do that?

Then maybe you will be willing to share with us what you feel are the issues that you have with your ETL system.

Ok?

So in this blog post we have told you about how we were introduced to a prospect here in Romania who were getting their data in to their data warehouse only at about 1pm each day. And then reports had to be run off that data.

They would have liked to get their data earlier in the day.

There were a number of limitations in the ETL and Data Warehouse. They:

  1. Used Stored Procedures For ETL Development.
  2. Had Serial Execution Of ETL Processing
  3. Had No Staging Area.
  4. Had Not Implemented Incremental Updates Properly.
  5. Had Used Clustered Facts Tables Rather Than Partitioned Fact Tables.
  6. Had No Multi-Level Summary Tables.

Over time we were able to address all these issues without vastly re-writing the ETL Stored Procedures.

We are maintaining more than 60,000 lines of Stored Procedure code written as long ago as 2012.

By addressing those issues, and even with adding a staging area, we were able to cut the ETL processing time from 7 hours to 6 hours.

When we introduced the BIDA Scheduler to be able to properly run processing in parallel we cut that further to about 4.5-5 hours.

We have maintained the 4.5-5 hour processing window for the last 4 years despite adding a lot more processing and a lot more data to the daily ETL processing.

The customer recently asked us to reduce the processing time more and we chose to move forward with a combination of a faster CPU, an SSD and some relatively simple to implement improvements to the old Stored Procedures.

In the end we were able to reduce processing time to just over 2.5 hours.

This is what we have talked about in this blog post today.

We hope that by reading or listening to this post we have given you some reasonable evidence we know how to improve processing times for ETL systems implemented on SQL Server, especially those implemented with Stored Procedures.

If you are using SQL Server for your data warehouse and you have any of the six limitations we have listed above?

We would really like to show you how we can reduce your ETL run times and get your business users the data they need on their devices, sooner, each day.


Before we finish this blog post we want to leave you with one last thought.

Ok?

One of the main reasons Stored Procedures should never be used for ETL is this.

The programmers of the Stored Procedures often cut corners and produce code that is inefficient, slow and hard for someone else to come along and maintain.

They do this because when they are writing the Stored Procedure the emphasis is on:

“How long does it take to write, test and implement the Stored Procedure in to production?”

This emphasis is because there is an easily recognized and easily quantifiable cost to the development of the Stored Procedures.

The programming consultants work on a paid by the hour basis.

So if it takes 8 hours to write a Stored Procedure and put it into production?

The easily quantifiable cost is 8 hours times the hourly rate.

Many customers sincerely believe that this cost represents the majority of the cost of the development of the Stored Procedure.

It does not.

In fact, when an ETL Stored Procedure is implemented in to production, depending on the skill of the developer, you have only incurred between 20%-30% of the cost of that Stored Procedure.

The other 70%-80% of the cost of that Stored Procedure is yet to be incurred.

This is called “maintenance” and it can be 3-5 times the cost of development.

Since we know these are the numbers?

We know how to implement ETL systems that are not only faster and cheaper to develop, but they must be faster and cheaper to maintain too.

If you are using SQL Server and you have your ETL System written using Stored Procedures?

Or if you think your ETL maintenance costs are too high and would like to reduce them?

We would be very interested in showing you how our BIDA ETL will significantly reduce your cost of ownership of your data warehouse over the next 5 year period.

Ok?


Now, here at the end of our blog post?

I would like to say again that we are very pleased you have chosen to read or listen to our blog post.

We hope we have given you some real food for thought about your ETL system you have implemented on SQL Server.

We hope by sharing with you some of the story of the ETL support we have provided for this particular Romanian customer that we have earned just a little credit towards demonstrating that we know what ETL systems are about and how to improve them.

My contact details are on our web site!

I would love to talk with you and hear what you consider are your issues with your ETL system.

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

I really appreciate you!

Thank you!

Mihai Neacsu


Contact Us


BIDA0014 – BIDA Navision BI First Dashboard Demo

0


Hello and welcome to our latest blog post.

We are pleased you have come back to see our latest news!

Last week we released our first video demonstration for our BIDA Navision BI Dashboards.

Today we are pleased to release a detailed white paper on how this dashboard was developed.

This white paper goes in to a great level of detail on how to build the dashboard that we demonstrated last week.

If you would like to download this white paper?

Please just click the button below.

Get White Paper

BIDA0013 – BIDA Navision Dashboard Demonstration

0

BIDA0012 – BIDA Navision Data Models Update

0

Hello and welcome to our latest blog post.

We are pleased you have come back to see our latest news!

A couple of months ago we released the first public details of our BIDA Navision BI Solution by releasing diagrams of the underlying data models for Sales and Party Profiles.

If you have not seen these White Papers then we recommend that view the blog posts for them on this site and retrieve them through the forms placed on the blog posts for this purpose.

As luck would have it, as we were completing our first US Project using the Meta5 DBA tool as the documentation tool for the models we implemented, we found out we can print images of data models in UHD using Meta5.

We did not know this when we created the prior version of the documentation.

So this blog post is to inform you that we have released an update to the data models previously presented to the public that are MUCH BETTER and you will surely want to get your updated copy of them!

If you would like to be sent an email with the latest updates to our documentation for our BIDA Navision BI Data Models then please just put your best email address in the form below.

You will be automatically emailed links to the documentation in both powerpoint and PDF formats.

Thank you again for dropping by our web site to read our latest news!

BIDA0011 – Our First Successful Project As BIDA!

0

Hello and welcome to our latest blog post. We are pleased you have come back to see our latest news!

We have been a little quiet over the last two months and there has been a very good reason for this!

The reason is that we have delivered our first successful project in the USA!!

This means that within 6 months of “Start Up” we have delivered (AND BEEN PAID) for our very first successful overseas project!

NOT BAD! 

The project is subject to non-disclosure agreements so there is very little we can say about it publicly. We are a sub contractor to a US Business Partner who was searching for a solution to build a BI Solution for a very popular ERP in the US. The ERP has more than 3,000 installed accounts world wide. The ERP does not have very good BI capabilities.

Our business partner is a consulting firm who has a number of customers using this ERP.  Our business partner has been looking for a way to provide a high quality BI Solution for the ERP at lower costs than can be achieved in the US. They found us through a common acquaintance.

What we can say about this project publicly in a blog post is this:

  1. Our BIDA ETL was re-branded by our business partner to provide to the end customer.
  2. Our BIDA Data Models were re-branded by our business partner to provide to the end customer.
  3. There are more than 650 tables and 14,000 fields in the staging area we built.
  4. We delivered 100% of the ERP data into the staging area with complete delta detection.
  5. There are more than 35 fact and 50 dimension tables in the “Release 1.0” BI Solution we delivered.
  6. We delivered the working ETL / DWH system in just 6 weeks from being given access to the ERP data. This includes learning how the ERP works.
  7. We did not work on the project full time over the 6 weeks because there were pauses for question / answer cycles.
  8. We delivered General Ledger, Accounts Receivable, Accounts Payable, Sales Orders and Inventory Management in Release 1.0.
  9. Our business partner developed front end reports for Profit & Loss, Balance Sheet and Cash Flows.
  10. Our business partner is developing more reports and dashboards as part of their portion of the project.
  11. The end customer is now deploying these reports and is considering more subject areas from the ERP to be added to the BI Solution!

As we noted this project is subject to non-disclosure agreements.

Our US Business Partner now has an agreement to use BIDA ETL and Data Models under their company brand and logo.

They have the source code for everything we deliver and their own staff can manage and support their customers.

They are responsible for building front ends to the data warehouse we developed.

They will be marketing the BI Solution we helped develop in the US starting in January!

We consider this a red letter day in our “start up” process!

For a Romanian “startup” to acquire our first US Customer and to deliver successfully inside the first six months is quite an achievement. We expect our business partner to make many sales of this BI Solution in the future.

We are, of course, suitably excited at the prospect of now having our first ERP BI Solution in marketing phase in the US!

This project delayed our development work on our Navision BI Solution and that is perfectly ok!

If you would like to ask us questions about this, our very first successful US Project, please put your best email address in the form below or contact us through our contacts page.

Thank you again for dropping by our web site to read our latest news!

BIDA0010 – Navision BI Party Profiles

0

Hello and welcome to our latest blog post. We are pleased you have come back to see our latest news.

Our latest news is that we are now able to release details of our BIDA Navision BI Party Profiles data models.

We continue to work on the development of the BIDA Navision BI Solution. As we have news of how that is progressing we will be releasing portions of the data models.

So please check back regularly or subscribe to our mailing list to be informed when new posts are put on our web site.

We use our BIDA ETL software to populate our BIDA Navision BI Data Models and the portions are are releasing are “production ready” meaning that you could take these portions of the models we are publishing and put them in to production at your site.

We have written a white paper on the BIDA Navision Party Profiles and provides some examples of how they can be used. We hope you like it!

If you would like to download a copy of this detailed white paper showing you some examples of how to use the BIDA Navison BI Party Profiles Sub Models?

Please just put your best email in the form below and we will email you a link to the download.

BIDA0009 – Navision BI Sales Category

0

Hello and welcome to our latest blog post. We are pleased you have come back to see our latest news.

Our latest news is that we are now able to release details of our BIDA Navision BI Sales Category data models.

We continue to work on the development of the BIDA Navision BI Solution. As we have news of how that is progressing we will be releasing portions of the data models.

So please check back regularly or subscribe to our mailing list to be informed when new posts are put on our web site.

We use our BIDA ETL software to populate our BIDA Navision BI Data Models and the portions are are releasing are “production ready” meaning that you could take these portions of the models we are publishing and put them in to production at your site.

We have written a detailed white paper on the BIDA Navision Sales Category data models and how we have implemented them.

If you would like to download a copy of this detailed white paper showing you exactly how we have designed the BIDA Navison Sales Category Sub Models?

Please just put your best email in the form below and we will email you a link to the download.

BIDA0008 – Navision Staging Area Access Details

0

Hello and welcome to our latest blog post. We are pleased you have come back to see our latest news.

We recently had to build an entirely new staging area for our Navision BI development partner. The reasons we needed to do this are not particularly relevant. It had to be done.

What is very relevant to you as a Navision user is that we were able to build a completely new instance of the staging area in just 4 working days. This was 1,867 tables and 35,591 fields from a Navision 2009 instance.

This new staging area has now been in production for more than a month and it is running smoothly. With the smaller Navision instance to scan for changes the extract processing time is down from around 30 minutes to 5-6 minutes.

We were rather surprised the extract times are so fast but without 7 years of transaction history this is the extract times we are seeing. It takes about another 10 minutes to apply the updates to the staging area. So we are seeing extract and update times to the staging area in the 15-20 minute range depending on the volume of transactions of course.

We have written a short white paper on how such staging areas can be access by a product we happen like called Meta5. We hope you like it!

If you would like to download a copy of this detailed white paper showing you exactly how we are able to access the Navision Staging Areas we build with Meta5?

Please just put your best email in the form below and we will email you a link to the download.

BIDA0007 – Navision Staging Area Construction Details

0

Hello and welcome to our latest blog post. We are pleased you have come back to see our latest news.

Our latest news is that we recently had to build an entirely new staging area for our Navision BI development partner. The reasons we needed to do this are not particularly relevant. It had to be done.

What is very relevant to you as a Navision user is that we were able to build a completely new instance of the staging area in just 4 working days. This was 1,867 tables and 35,591 fields from a Navision 2009 instance.

The first time we did this 3 years ago it took us about 10 days work. Then again that was the first time we had ever undertaken building such a large staging area with our new techniques we had developed. And we had to write two functions for field and table name mappings that took quite some time.

This time it went much more smoothly. All work was completed and the staging area was ready to go in just 4 working days. We did have to do a few other small changes and problems we found as we tested so you could call it 5 working days, or a weeks work.

We have written a detailed white paper on exactly how we did this.

If you would like to download a copy of this detailed white paper showing you exactly how we were able to build a proper, production, incremental extract Navision staging area in just 4 working days?

Please just put your best email in the form below and we will email you a link to the download.

BIDA0006 – MicroSoft Navision BI Solution

0

In this blog post we wish to provide a short explanation of our MicroSoft Navision BI Solution which is under development.

For the last 3+ years we have been providing 24×7 support for our Romanian development partner.

Today this development partner has 280+ retail stores and sells 300,000 to 400,000 items in their stores each day.

The data warehouse is on SQL Server Standard Edition running on a 16 core server for the data in the staging area and data warehouse.

The time span of the data goes back to 2012 and we are able to support analytics for sales and finance back to 2012 based on this data warehouse.

The ERP they use is MicroSofts Navision.

We have also implemented a full staging area taking all the data from their Navision ERP and placing it in to the staging area on the data warehouse machine.

We take incremental extracts from the production system two times per day and we run these through a delta detection process to apply inserts and updates to the staging are to keep the staging area in sync with the production ERP system.

This means the customer has 100% of their ERP production data available in the staging area to query with any tool they please and therefor no extra load is placed on to the production system for ad-hoc and “one time” queries.

The ETL system was developed by another Romanian based company and was written with SQL Server stored procedures.

We have not replaced these stored procedures with our BIDA ETL. However, we have done new development using our BIDA ETL.

We migrated the processing schedules from purely stored procedures across to our BIDA Scheduler to be able to run portions of the ETL in parallel to reduce the over all ETL elapsed time.

Given this three years of experience we naoe have very good skills with Navision.

As a result we have decided to bring to market a Navision Business Intelligence Solution.

Our Navision BI Solution is under development on Navision Version 2009.

We have completed the development of the staging area and the main business entities for retailers.

For example we have the following fact tables completed in development:

  • Customer Ledger Header
  • Customer Ledger Txn
  • Fixed Asset Ledger Txn
  • GL Account Balance
  • GL Account Txn
  • Outlet Sale Header
  • Outlet Sale Txn
  • PO Receipt Header
  • PO Receipt Line
  • Return Header
  • Return Line
  • Sale Txn
  • Store Payment Component Txn
  • Vendor Ledger Header
  • Vendor Ledger Txn

We have also implemented the mechanisms for managing summaries for sales with incremental updates of the summaries.

The proposal for the Navision BI Solution is to move ALL DATA from the Navision ERP rather than to move a limited amount of data.

We will deliver the initial suite of reports in Excel using the Meta5 product because Excel is pervasively used in Navision customers we have talked with.

During our start up phase we are going to limit the number of customers for our Navision BI Solution to five.

We will focus on our first five customers to get them implemented and supported properly while we hire and train some of the “best and brightest” graduates to support our local Romanian customers.

We will soon be releasing marketing materials on Navision BI Solution.

If you are running any version of Navision and you would like to ask us any questions about our planned Navision BI Solution?

Please put your best email address in the form below.

You have space to ask any question you would like on the form as well.