The LeapFrogBI DWA platform enables the use of existing tables and views. For example, when extending a data mart there will be existing dimensions that need to be referenced by the new fact table. In this case we need to profile the existing dimensions so that the tool knows how to access them. The same is true when using existing transform and existing stage components.
Profiling an existing resource requires that a package be uploaded to LeapFrogBI. This profile package can be downloaded from the template selection page by right clicking the “File” link and selecting “Save As”. Ensure that the correct extension is used (dtsx for ssis).
After downloading the profile template, the steps included in the template should be completed. Finally, the profile is uploaded to LeapFrogBI when creating the new “Existing” type component.
SQL Statement From Clause
LeapFrogBI is going to use the provided SQL Statement in the exact format as is provided by the uploaded profile. If parameters are included, then LeapFrogBI will take care of performing replacements dynamically. It is important to take care when deciding how to build the From clause. LeapFrogBI will execute the SQL Statement in the context of the created component’s destination connection. Follow the below guidelines.
One part name – If the destination connection is the same as the source connection, then it is acceptable to use a 1 part name in the from clause.
[myTable]
Three part name – If the source and destination connection are on the same SQL Server instance, but are in different databases, then a three part name should be used.
[myDatabase].[dbo].[myTable].
Four part name – If the source is in a different SQL Server instance from the destination, then a four part name should be used.
[Server\Instance].[myDatabase].[dbo].[myTable]
In this case care must be taken when considering lifecycle migration as LeapFrogBI will use the same SQL statement in all lifecycle deployments. If the existing table is in the same SQL Server instance regardless of the lifecycle, then this will work fine. Otherwise, a parameter should be used to dynamically set the server name.
“Select … From [” + ServerVariable + “].[myDatabase].[dbo].[myTable]”
