LeapFrogBI supports a number of physical architecture options. Developers can select an architecture and configure each project as desired. Keep in mind that LeapFrogBI creates in-database (ELT) processing whenever possible. This means the database engine will be doing the heavy lifting regardless of the location of SSIS and package execution. When selecting a target architecture strive for simplicity while also meeting your organizations goals.
Guidelines
- The LeapFrogBI console and project components must be deployed to the same instance of SQL Server.
- The precedence solution may be deployed to any instance of SQL Server that can communicate with the project database server as well as all data sources. All required data source providers must be installed on the server that contains the precedence solution.
- Always deploy packages to the server that contains the precedence solution.
- You may freely collocate the LeapFrogBI console, precedence solution, and/or target data mart by setting project target connection definitions and environment variable values such that they point to the same database.
The simplest option is to deploy all assets to a single server and a single database for an entire project. Hardware and software license costs are kept to a minimum while taking full advantage of the available resources.
Setup – Create one environment variable on the target server with a value (connection string) pointing to the same database (initial catalog) as defined in your project’s target connection definitions. Set the precedence solution to reuse the lifecycle environment variable. This will create all of the configuration, precedence, and data mart assets in a single database.
Restrictions – The configuration and precedence solution create tables on the [dbo] schema. Your project cannot reuse the following table names; Configuration, Precedence, Variables.
While deploying to a single server it is also possible to separate the LeapFrogBI console and precedence functionality from the target data mart database/s. This does not impact performance, but it does offer a clean separation of objects at the database level.
Setup – Create one environment variable on the target server pointing to the database to be used as the LFBI console and precedence repository (both in same database). Remember, LeapFrogBI will take care of creating all destinations including databases that do not already exists. All you need to do is set the initial catalog property of the environment variable’s value (connection string) to name the target database.
Restrictions – None. By placing the console and precedence tables in their own database naming restrictions have been removed.
To fully separate functionality at the database level the console and precedence solutions can be placed in their own single purpose database. There are no performance or functionality implications to this design as opposed to using a single database or single console database.
Setup – Create one environment variable with a value (connection string) pointing to the LeapFrogBI console database (initial catalog). Create a second environment variable with a value pointing to the precedence solution database. In your project’s precedence setting set the ”Environment Variable” option to ”Custom Environment Variable” and enter the name of the environment variable that contains the connection to the precedence database.
Restrictions – None. By placing the console and precedence tables in their own database naming restrictions have been removed.
In some cases organizations require SSIS to run on a dedicated server. While LeapFrogBI does support this type of architecture, the result is added complexity and potentially negatively impacted performance. As an example, stage component will need to collect data from a source, move the data across the network to the ETL server, and then move the data across the network to the target database. The second trip across the network between the ETL server and the DB server is added overhead as a result of separating the ETL and DB server.
Setup On the ETL Server – Create the LeapFrogBI console environment variable with a value (connection string) pointing to the DB Server (data source) and the target database (initial catalog). To collocate the target data mart and the LeapFrogBI console into a single database ensure the LeapFrogBI console environment variable value’s initial catalog is equal to your project’s target connection/s database name. Create a second environment variable with a value pointing to the ETL server database instance and the target precedence database. In your project’s precedence settings set the ”Environment Variable” option to ”Custom Environment Variable” and enter the name of the environment variable that contains the connection to the precedence database.
Setup On the DB Server – No configuration is needed.
Restrictions – Your project may not reuse LeapFrogBI console table names; Configuration, Variables.
In some cases organizations require SSIS to run on a dedicated server. While LeapFrogBI does support this type of architecture, the result is added complexity and potentially negatively impacted performance. As an example, stage component will need to collect data from a source, move the data across the network to the ETL server, and then move the data across the network to the target database. The second trip across the network between the ETL server and the DB server is added overhead as a result of separating the ETL and DB server.
Setup On the ETL Server – Create the LeapFrogBI console environment variable with a value (connection string) pointing to the DB Server (data source) and the target database (initial catalog). To separate the target data mart and the LeapFrogBI console ensure the LeapFrogBI console environment variable value’s initial catalog is not equal to your project’s target connection/s database name. Create a second environment variable with a value pointing to the ETL server database instance and the target precedence database. In your project’s precedence settings set the ”Environment Variable” option to ”Custom Environment Variable” and enter the name of the environment variable that contains the connection to the precedence database.
Setup On the DB Server – No configuration is needed.
Restrictions – Your project may not reuse LeapFrogBI console table names; Configuration, Variables.
