Analytics with Ecodel Data Warehouse as Source

In this article

Ecodel app and data warehouse

Set up and configure Analytics

LS Retail ehf. has been testing a third-party solution as an alternative to setting up replication with Data Director for Analytics implementations with LS Central SaaS. The solution is a Business Central app called Ecodel Exclusive - Data Warehouse Connector and is available from AppSource. You can find more details and price information on the product website.

As this is a third-party solution, LS Retail does not take any responsibility for the quality of the data the Ecodel data warehouse connector app Exports, but all our testing so far has been positive. If you have any questions or issues with the export you should direct those to Ecodel support.

To ease the export setup, a configuration file is provided, that can be imported and includes all tables that are needed for Analytics.

This article also explains what needs to be done during and after the Analytics setup for the Ecodel data warehouse to function as a source for Analytics.

Note: You can not switch the source of an existing Analytics data warehouse from LS Central to Ecodel data warehouse. You should always create a new Analytics environment.

Ecodel app and data warehouse

1. Set up the Ecodel Exclusive - Data Warehouse Connector app in your LS Central SaaS environment (if you want to test it in a Sandbox first, you should of course do that), and create the database and storage account resources in Azure according to their setup instructions. A video is available from the AppSource page.
2. Set up the field configuration for the tables and fields you want to export on the Data warehouse Connector Setup page in LS Central, or import the configuration files using these instructions:
   1. Download the configuration excel file.
   2. In LS Central, navigate to Configuration Packages.
   3. Select Import from Excel, and browse to the excel file.
   4. When the dialog window opens, select Import from the top menu.
   5. Once the file is imported, you must Apply the package.
   6. When the application has finished (there might be errors but that is OK – they are because of flow fields that cannot be exported by the Ecodel app), you can navigate back to the Data warehouse Connector Setup page.
   7. In the Data warehouse Connector Setup, you must enable all the tables for Analytics to run correctly.
   8. In this stage, you can also add checksum fields on the tables.
3. Next, you need to schedule the export to run every half hour.
   Note: You need to do this in every company you want to use data from in Analytics.
4. Now you just let the export run for one company at a time. To check if all data has been exported, you can query the [dbo].[BcDatawarehouseChecksums] table for row counts and checksums. The export exports the data 100.000 rows at a time, so it can take a while for the export to finish, if the tables are large. If there are any errors in the export, you should contact Ecodel support.
5. While data is being exported, you need to run a script that adds the NAV App Installed Apps table on the Ecodel data warehouse database.
6. When all data has been exported from LS Central to the Ecodel data warehouse database, you are ready to use the Ecodel data warehouse as a source for Analytics.
7. Now that all tables have been fully exported from LS Central to the Ecodel data warehouse, you can set the Ecodel app to export as frequently or infrequently as you want.
   We recommend exporting frequently throughout the day while transactions are being created, and then pausing after the store has closed and while the Analytics Scheduled run pipeline is running. You can also just schedule the export to run once a day, if you have low transactional throughput. In that case you just have to make sure that the export has completed before the Analytics Scheduled run pipeline is run.

Set up and configure Analytics

Read through these instructions before starting the Analytics setup.

1. Follow the setup instructions for Analytics with LS Central on-premises.
   • Since you will be using the Ecodel Data warehouse as a source, you can skip the following steps in the setup wizard:
     • Step 1 - Create SQL user in LS Central. This is not needed since you already have an admin user for the Ecodel data warehouse database that you will insert when the script prompts for credentials to LS Central.
     • Step 3 - Setup gateway server. This is not needed since the Ecodel DW database is located in Azure and you will use AutoResolveIntegrationRuntime for the connection later on.
     • Part of Step 6 - Setup gateway server section. This is not needed since the Ecodel DW database is located in Azure and you will use AutoResolveIntegrationRuntime for the connection later on.
   • You need to have an Azure subscription and get the latest product package for Analytics. You can use the same Azure account, resource group, and server that you used when setting up the Ecodel data warehouse database.
   • When the script prompts for LS Central source information, enter the information for the Ecodel data warehouse that you have created in Azure.
     • Remember that when connecting to Azure SQL server you need to supply the server name with the .database.windows.net suffix.
   • When the script prompts for Companies, you must enter the company name manually into the file. One company name per line, if you have more than one company you want to add. Make sure that the company name is written exactly like it is in the companies table in Business Central. Then save the file and follow the instructions in the script. The script then reads the file and asks you to confirm the company name(s).
   • Do not run the initial load pipeline until you have completed the steps below.
2. When the setup is complete and resources have been created in Azure, run the following scripts on the Analytics data warehouse database.
   • Script to update AppAffixRegistry
   • Scripts to update procedures with timestamp and duration fields, ACI Alerts and ACI Signals
   • Script that updates Hospitality DetailedRevenuEntry stored procedure.
3. In the Azure data factory you also need to do the following:
   • Change the integration runtime used in the LSCentralSource linked service to the AutoResolveIntegrationRuntime instead of the Analytics-integrationRuntime.
     • Test the connection to the Ecodel data warehouse.
       • If test is successful you can continue, otherwise you need to check the parameters used in the linked service to connect to the Ecodel data warehouse.
   • Now follow the steps below to update the PopulateQueryBase pipeline query with the new sql query and publish the change:
     1. Open the PopulateQueryBasePipeline.
     2. Open the PopulateQueryBase copy activity by clicking it.
     3. Navigate to the Source tab.
     4. In the Source tab, click the Edit button by the query, and replace the query with the one from the document you downloaded. Click the OK button to save the changes.
     5. Publish the change to the data factory by selecting the Publish button in the top menu.

        

4. Go back to the instructions, and find the Run the Initial load pipeline section, complete the steps, and then complete the rest of the steps in the onboarding process to set up a schedule for the Scheduled run pipeline and connect the Power BI reports.