SYSTEM REQUIREMENTS

⚠ Important: It is of utmost importance that all system software used in the BI project is of the latest version and constantly updated. Any neglect of this task might result in an inability of BI4Dynamics to be installed or properly operate. For any questions related to system requirements, please send an email to support@bi4dynamics.com

1. Accounts and Permissions #

• Be (local) administrator on the windows server where BI4Dynamics is installed.
• Read permissions on the FO database.
• Read permissions on the master database of the source database 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 OLAP cubes will be
  deployed. Additionally, the Analysis Server service should run under the BI4Dynamics domain
  account.
• SQL Agent job Service user account needs all the above-stated permissions

ⓘ Note: All six (6) accounts can be combined into one (1) Windows domain account. One Windows domain account is preferred.

2. Windows Server #

2.1 Hardware #

Dedicated infrastructure for BI stack is required with the following specifications:

CPU: 4 – 8 Intel® processors or equivalent (at least 2GHz)
The more cores the better as the SSIS option can run parallel on as many cores as there are
available. Using SSIS option can speed up processing Stage and Data warehouse by 30 to 50%

• RAM: minimum recommendation:
• 16 GB RAM for FO database size < 50 GB
• 32 GB RAM for FO database size 100 … 250 GB
• 64 GB RAM for FO database size 250 … 500 GB
• 128 GB RAM for FO database size 500… 1000 GB
• 256 GB RAM for FO database size 1000…2000 GB
• 384 GB RAM for FO database size 2000…4000 GB
• 512 GB RAM for FO database size > 4000 GB

Consult us for larger databases as our recommendations depend on table structures

ⓘ Note:
Standard SQL Edition can use 128 GB Memory in Buffer Pool (database engine) and 16 GB Memory in Analysis Services Tabular. If data size requires more memory than MAX in STD edition, then the BI project can be split into two instances (Operations, Finance). If the memory limit persists, then SQL Enterprise edition should be implemented. Settings in SQL server allows extending Memory operation to disk. This affects the performance of processing and querying and should be tested if implemented.

Disk space depends on the size of the FO database, we recommend:

• 1.25 x size of Dynamics FO database used when no SSIS option is used
• 2 x size of Dynamics FO database used with SSIS option is used

SSIS options need more space as more tables are processed at the same time and more hard drive
space is needed for temporary calculations. As the result, the size of BI4Dynamics DW will be about
30- 50% of the FO database. If possible, use an SSD hard drive, which processes data about 30 – 50
% faster than standard 15k disks.

Please contact us on details when databases larger than 2 TB to get HW recommendation based on
table structure.

ⓘ Note:
FO database vs the number of BI users
Hardware configuration should correspond to the size of the Microsoft Dynamics database (this is important) and to a 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 a large set of data are read and written each time.
Good CPU with many cores

2.2. Setting up Analysis Services Tabular #

System requirements for the Tabular model are different from the Multidimensional model. While
Multidimensional requirements are like Data warehouse requirements (fast disks, many cores for
parallelism), Tabular requires fast CPU and fast RAM. It is possible to set the Analysis Services database
on a separate BI server.
These hardware components affect performance for Tabular engine:

CPU

• Faster CPU speed is the better and will affect query time (interaction between front end tools (Excel and Power BI) and Tabular engine when a user is analyzing data

RAM

• Faster memory is better
• Memory size should be 2 X of the sum of the size of all Tabular databases
• If there is not enough memory, data will be partly written to the disk and performance will be
  significantly slower
• Limitation of Analysis Service Standard Edition is 16 GB

2.3. Software #

• Windows Server 2012, 2012 R2, 2016, 2019, 2022
• Network availability:. UAC should be disabled, or the application needs to be installed outside the Program Files folder
• .NET Framework 4.7.2
• 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: a special offline license is required; please contact sales@bi4dynamics.com for further assistance.

3. SQL Server #

3.1. General
All BI4Dynamics features run on Standard SQL Edition. It is understood that it can run also on BI or Enterprise
edition.

• Microsoft SQL Server Standard Edition from version 2019 ( version 2022 is also supported).
  Version 2016 could be used with limited functionality (contact support).
  • Database Engine
  • Analysis Services (2 instances are needed):
    ▪ Tabular instance for Operational modules (cubes)
    This will be the main instance for the BI project (option enabling using Financial Statements on Analysis Services Tabular Model (in development)
    
  • Integration Services
  • SQL Agent
• All services run under the windows domain user with appropriate permissions
• Separate partitions for Data + Log + Temp (8 files for data and temp DB of the same size)

ⓘ Note: 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.

ⓘ Note: For Microsoft Dynamics FO databases, the collation of the data warehouse server should match the collationof the FO server

3.2. SQL version and edition #

If the customer can install Enterprise edition, currently there will be no difference in features or performance. Starting in 2021, BI4Dynamics will support SSAS Partitions, which will speed up the SSAS database update. Therefore, if the license for Enterprise is available at no additional costs, we would recommend installing Enterprise edition at the beginning. If there is a cost, the SQL Standard edition is still recommended.

In SQL Standard Edition we strongly recommend using SQL Server 2019
Standard and higher due to new features support:

• Showing dimensional: fact relationship in Excel (see picture)
• Using calculation groups
• Direct query mode for Tabular database

4. Source database (FO) #

• FO license needs to be uploaded either on the server (to the master database of SQL instance) or
  into the FO database
• There must be at least one entry in Currency and Country tables per Company (only applies to
  companies you intend to use in BI4Dynamics)
• If you use a non-default SQL port (1433), then you have to add an SQL port number when adding
  the data source in BI4Dynamics. SQL port number must be added after SQLServerName and must
  contain the prefix “,”. Example “SQLServerName,SQLPort \ SQLInstance”.

ⓘ Note: If the FO license is uploaded to FO (SQL) server, then the user also needs read permission on the master database of SQL Server where the FO database resides. *Only FO W1-World Wide version is supported.