INTRODUTION #
On-premises vs Cloud
This documentation is for BI4Dynamics BC on-premises installation. The source of data is BC running on premises or in the cloud Platform as Service (PaaS). A separate documentation is available for BI4Dynamics BC Cloud. This document is not for BC Cloud.
Supported BC / NAV versions
We support any version from NAV 2009 to latest BC 21.
Latest versions of system software
It is of utmost importance that all system software used in BI project is of the latest version (or latest – 1) and constantly updated. Any neglect of this task might result in inability for BI4Dynamics to be installed or properly operate.
Note: Most common misunderstanding is that SQL updates will come automatically with Windows update. SQL server updates do not come with Windows update automatically. SQL updates must be downloaded manually from Microsoft page as CU update (cumulative update). Updates are coming frequently, every few months.
https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates
INSTALLATION REQUIREMENTS #
For any questions related to system requirements, please send an email to support@bi4dynamics.com
1. Permissions #
All six (6) accounts can be combined into one (1) Windows domain account. One Windows domain account is preferred:
- Be (local) administrator on the windows server where BI4Dynamics is installed.
- Read permissions on the BC database.
- Read permissions on the master database of the source database SQL Server (only required if the BC license is uploaded to the SQL server)
- Create database and linked server permissions on MS SQL Server where the BI4Dynamics DWH will be deployed.
- Granted Server role on MS SQL Analysis Services where the BI4Dynamics Tabular model will be deployed. Additionally, the Analysis Server service should run under BI4Dynamics domain account.
- SQL Agent job Service user account needs all the above stated permissions.
2. BI Server (on-premise installation) #
2.1 Hardware #
A dedicated infrastructure for BI stack is recommended. Please consolidate such decision with IT services experts to determine the optimal configuration for the environment.
Dedicated BI infrastructure stack
With modern VM infrastructure, it is easy to have an independent and dedicated BI environment up and running within a few hours. If your company uses virtualisation, you can create a windows server machine that can host a dedicated BI SQL Server instance.
- Separating the BC production environment from BI environment gives you more flexibility regarding system resources and increases BC environment stability
- Please check for any maintenance jobs running at source DB server and set the BI4Dynamics daily job accordingly.
Shared BI infrastructure stack
Adding a dedicated BI infrastructure is not always possible. Existing BC production environment can also be used.
- It is advisable to allocate some extra hardware resources on existing server for BI purposes.
- All running agent jobs needs to be aligned with the BI4Dynamics agent jobs to avoid accidental interlocking issues.
1-CPU and Memory
BI server requires memory for:
- Storing data
- Storing temporary data (while processing)
- Operations (to function)
When database size grows, the Tabular engine can achieve much better compression.
| BC database size | CPU (logical processors) | RAM (GB) |
| < 50 GB | 4 | 20 |
| 100 to 250 GB | 6 | 40 |
| 250 to 500 GB | 8 | 75 |
| 500 to 1000 GB | 16 | 150 |
| 1 to 2 TB | 24 | 300 |
| 2 to 4 TB | 32 | 400 |
| 4 TB > | 32 | 512 |
Consult us for larger databases.
2-DISK
Disk space depends on the size of BC database, we recommend:
- 1.25 x size of Dynamics BC database used when no SSIS option is used
- 2 x size of Dynamics BC database used with SSIS option is used.
- 1 x size when using Columnstore feature
SSIS option: SSIS options need more space as more tables are processed at the same time so more disk is needed for temporary calculations. The result – size of BI4Dynamics DW – will be about 30-50% of BC database.
SSD: If possible, use SSD hard drive, which processes data about 30 – 50 % faster than standard 15k disks
Columnstore option
BI4Dynamics supports Columnstore storage in staging (schema = stage) and Datawarehouse area (schema = fact). Columnstore benefit depend highly on table structure and can achieve around 10X compression. Dim schema is not supported with Columnstore as dimensional table are usually smaller and compression does not help performance. Loading stage may increase by 15-25% due to building Columnstore (index).
ⓘ Note: BC database vs number of BI users
Hardware configuration should correspond to the size of Microsoft Dynamics database (this is important) and to lesser extent to the number of analytical users (this is less important).
ⓘ Note: Rules for fast processing:
Enough RAM: RAM amount under the recommended size will significantly affect the processing speed.
Fast disks: Most important parameter as large set of data are read and written each time.
Good CPU with many cores
2.2 Operating system #
1-Operating system
For SQL 2016 and SQL 2017 installation
- Windows Server 2012, 2012 R2, 2016, 2019 or Win 8,10
For SQL 2019 and SQL 2022 installation
- Windows 10 TH1 1507 or greater
- Windows Server 2016 or greater
2-NET Framework
Version 4.7.2
3-Server settings
UAC should be disabled, or application needs to be installed outside Program Files folder
Network availability:
- Online servers: Server is connected to the Internet with port 80 opened (if the server is under proxy, the application needs access to our authorization web service)
- Offline servers: special offline license is required; please contact sales@bi4dynamics.com for further assistance.
2.3 SQL Server (software) #
1-SQL server editions
All BI4Dynamics features run on Standard SQL Edition. It also runs on Enterprise edition.
Per instance Limitation of SQL server Standard Edition:
- SQL server engine Buffer pool 128 GB
- Analysis Services Tabular 16 GB
When data size requires more hardware resources, BI project should be split into two instances:
- Finance (Finance, Receivables, Payables, Fixed Assets, Bank Account)
- Operations (Sales, Inventory, Purchase, Retail, Manufacturing, Warehouse, Service)
2-SQL server database
Recommended is version 2022 due to new features and better user experience. With SQL Server 2022 composite models were introduced. This option allows a Power BI report to have two or more data connections from different source groups. These source groups can be one or more Direct Query connections and an import connection.
SQL 2016 and SQL 2017 provides limited functionality as some SQL functionality used in latest versions of BI4Dynamics application is not fully supported in lower versions of SQL.
3-Analysis services (local)
We do support only Tabular model of analysis database. Tabular model can be implemented on:
- Azure VM or local server,
- Azure Analysis Services,
- Power BI Premium as XMLA endpoint feature.
When using Multidimensional model, you need to use older versions of BI4Dynamics.
4-Permissions
| Azure Portal | Analysis Database | Power BI Premium | |
| Administrator | Analysis Services Admin | Server Administrator | Workspace Admin |
| Business User | Reader Role | Read Role | Viewer |
5-Integration services
Integration Services can speed up processing Stage and Data warehouse by 30 to 80% by running stored procedures in parallel. Integration Services is mandatory. If not installed or selected then loading stage tables and processing DW tables is running sequentially, one table after another, so processing will be much slower and process flow may not run as expected.
6-SQL Agent
SQL Agent must be enabled to run daily or hourly updates.
To introduce additional speed improvements, it is recommended to put data files on a RAID 5 for fast read speeds and log & temp files on RAID 1 for fast write speeds. For Microsoft Dynamics BC databases, the collation of the data warehouse server should match the collation of the BC server
3. BC (application software) #
3.1 Installation #
- BC license needs to be uploaded either on the server (to the master database of SQL instance) or into the BC database
- If you use non-default SQL port (1433), then you have to add SQL port number when adding the data source in BI4Dynamics. SQL port number must be added after SQLServerName and must contain prefix “,”. Example “SQLServerName,SQLPort \ SQLInstance”.
ⓘ Note: If the BC license is uploaded to BC (SQL) server, then user also needs read permission on master database of SQL Server where BC database resides. *Only BC W1-World Wide version is supported
3.2 Content #
1-Company table setup
There must be at least one entry in Currency and Country tables per Company (only applies to companies you intend to use in BI4Dynamics).
2-Account Schedules setup
Account Schedule dimension, a part of GL Analysis, has some unique SQL and DAX requirements as it is using the Calculation groups on Analysis Services Tabular (introduced in SQL 2019). Although Account Schedules may work fine in Dynamics BC, these are BI requirements need to be fulfilled:
- Account Schedules Line columns must be the same across all companies:
- Line No.
- Row No.
- Description
- Totaling
- Show Opposite Sign
This is achieved in BC by copy/paste Account Schedule lines from one company to another into empty (new) lines, so that every added Line No. will have value 10000 higher than the previous one.
- Do not use following characters in Totaling: |, -, *, /, <>, <, >, “,” (comma), =, <=, >=, ~=, !=, ^=, ( and ). These signs should be avoided due to SQL language. BI4Dynamics are aware that this feature is supported by Microsoft Dynamics BC.
- Currently supported Totaling Types:
- Posting Account
- Total Account
- Formula
- BI4Dynamics creates an Analysis database with Case Sensitive and Accent Sensitive collation. Be mindful when creating the RowNo and Description columns in Dynamics BC. Case and Accent properties must be the same for a specific RowNo and Description in the Account Schedule.
Failing to fulfill these requirements may result in processing error or in removing specific Account Schedule name and lines from BI project.
