About This Release
Released - April 29, 2021
This version of LS Insight supports versions 8.4 and later of LS Central On-premises and later versions of LS Central In-cloud (SaaS). Setup of LS Insight for LS Central In-cloud (SaaS) needs some additional setup from the standard LS Insight onboarding process which is explained in detail in the onboarding documentation.
In this version support has been added for affixes to extension tables. All tables added to Business Central with LS Central get the prefix LSC in version 17.5 of LS Central. Because of these changes we will not be able to support LS Central SaaS version 17.5 until we have been able to test the replication against version 17.5 on SaaS. This should be available with our next version of LS Insight.
A few issues, reported by customers and partners, have been fixed. These issues were caused by the dynamic staging table changes made in version 2021.1. One of the issues was duplicate lines in LS Insight fact tables, and by adding merging to all fact tables this issue has been fixed. There are jobs in LS Central that round transactions cost values and those changes caused duplicate lines in LS Insight fact tables.
A few improvements were also made to the Deployment script and they are explained in more details below.
Note: Updates for version 2021.1 are provided in a special update package. If you have already set up version 2021.1 you can update to 2021.2 without creating a new instance of LS Insight. See update guidelines for more information. Please note that if you have customizations in your LS Insight instance, you need to make sure that the updates do not affect these customizations before you start.
The following is a list of changes and key features in LS Insight version 2021.2.
New or Enhanced Features
Setup
Onboarding
The deployment script has been improved to enhance the user experience and accuracy of parameter data. The onboarding documentation has been updated to reflect these updates.
Module loading
Now the scripts always load the latest LS Insight modules from the product folder the Deployment script is located in. This means that if the modules have previously been loaded from a different folder (to set up an earlier version of LS Insight), they will be removed and re-imported from the current folder.
Region selection
The Azure resource (SQL server, db and ADF) region selection now allows users to decide whether they want to use the same region as is set on the selected resource group, or whether they want to select from the regions that support ADF v.2 and are allowed on their subscription.
The lookup for allowed regions takes a few minutes, but it is worth the wait to only get the list of regions you can actually use for the setup.
New Azure SQL server password
A validation has been added to the password that is selected for the Admin user of a new SQL server. Previously, the only validation was of the Admin user name fulfilling the Azure requirements, but now the password is validated as well. Instructions about user name and password rules and restrictions is provided in the script and onboarding process documentation.
Power BI connection information
A printout has been containing the information needed to connect the Power BI report templates to the LS Insight database to the last step of the Deployment script. This allows the user to capture this information for later use without having to do a lookup in the Azure portal.
Data Warehouse
Database
Tables
Two meta data tables have been added to LS Insight because of the extension affix change in LS Central 17.5.
- LSInsight$ShortTableNameMap
- LSInsight$PublisherAffixReg
Both of those tables are used in the PopulateQueryBase pipeline to trim away prefixes and suffixes and to create the staging table name that is the same as the table name from older versions. This is done so that we do not have to have multiple versions of each stored procedure used to load the dimensions and fact tables in the Data warehouse.
Stored procedures
To avoid duplicate entries in the LS Insight fact tables caused by updates in transaction tables, an update mechanism called merging in SQL has been added to all fact tables except two.
This means that even if there are updates in values and timestamps and we have duplicate rows in the staging tables because of incremental load, we always use the latest version of the row and update the fact table accordingly.
Merging has been added to the following stored procedures:
- factSalesPosted
- factInventory
- factDiscount
- factHotelDetailedRevenue
Two fact tables are small and fully loaded every time the data warehouse is loaded with new data from LS Central:
- factHotelStatistics
- prefactItemCostAdjustment
Pipelines
PopulateQueryBase fixes
Modifications have been made to the script that is run in the GetMetadata if True activity in the Generate LS Insight Query Base pipeline in the Azure Data Factory. The GETCentralColumnMetadata activity Source script has been modified.
The script now:
- Checks explicitly for the LS Central app guide in the NAV installed app table to decide which version of LS Central is being queried.
- Supports affixes to extension tables. The affix mapping is done through two new tables; LSInsight$PublisherAffixReg and the LSInsight$ShortTableNameMap. These tables have been populated with the correct data for use with LS Central 17.5 and later.
- Uses LEFT JOIN instead of JOIN when joining together columns and tables in older versions as was already the procedure for newer versions. This greatly reduces the time the script takes to run when LS Central has multiple companies.
- Filters the tables more precisely to only query relevant tables from LS Central.
New pipelines
Two new pipelines have been added to LS Insight. They are only needed for new instances of LS Insight with LS Central version 17.5 and later.
1- LS Insight query setup
- Add or Delete App Affix
- Add or Delete ShortTableNameMapping
These pipelines are used to add and delete from the Affix meta data tables that have been added to the DW. These pipelines should only be used when extending and customizing the Data Warehouse. The LS Insight DW template database already contains the mappings and LS Retail prefixes need for standard LS Insight.
Reports
LS Insight Sales
Two discount calculations were fixed in the Sales report:
- Standard Price on discount changed
- From: Standard Price On Discount Transactions = Sum('Actuals Discounts'[SalesAmountFromActualSales]) + [Total Discount]
- To:Standard Price On Discount Transactions = sumx('Actuals Discounts',[SalesAmount] + [Total Discount] )
- Margins From Discount Sales:
- From: Margin From Discount Sales = [Standard Price On Discount Transactions] - [Total Discount] - Sum('Actuals Discounts'[CostFromActualSales])
- To:Margin From Discount Sales = [Standard Price On Discount Transactions] - [Total Discount] - [Cost Amount]
One discount calculation was added:
- Cost Amount = Sumx('Actuals Discounts','Actuals Discounts'[CostAmount])
LS Insight Inventory
No changes.
LS Insight Hotels
No changes.