The LeapFrogBI platform provides a toolset that not only streamlines initial data mart deployment, but also greatly simplifies incremental modification tasks. There are a few points that must be understood to get the most out of LeapFrogBI.
– LeapFrogBI does not connect directly to source or destination system.
– LeapFrogBI’s output is SSIS packages. It is up to the developer to deploy the packages.
– Each package is responsible for creating the target object (table/view).
– Project configuration is managed centrally.
Each component created in LeapFrogBI will generate a single SSIS package. This package will create the required target object. Two conditions determine if the target object is created. First, the “CreateTarget” configuration property value is checked. If the value is set to 0, then the target object will not be created. If the value is -1 or a positive integer, then the package will check if the target object exists. If it does exist, then no further action is taken. If it does not exist, then the object is created. A “CreateTarget” property of -1 causes the package to check for the target object each and every time the package is run. A positive integer value triggers the package to perform this action each time the package is executed for the specified number of executions. By default each component will receive a “CreateTarget” property value of 1 causing the component to perform the above described tasks on the first package execution only.
How to Deploy a Changed Component
There are several options for redeploying components after changes have been made. The type of component being modified, type of modifications made, development phase, and business conditions should be considered when determining which option to choose.
Deploy Only
When a component is changed in a way that does not structurally affect the target object it can be redeployed without making any further modifications. Changes such as calculation edits that do not change output data types, statistic collection changes, set operation changes, and filter changes fall into this category.
Drop & Recreate
During initial development it is often most efficient to simply drop the target object and let the package recreate it. This requires the developer to complete two tasks prior to executing the modified component package. First, the target object must be manually dropped. Second, the “CreateTarget” configuration property must be set to -1 (always) or a positive integer (check if exist n number of times) such as 1. After completing these tasks, the component package will create the target object when it is next executed.
This approach is suitable for components that are truncated during each execution (stage & transform) as well as components which create views (transform & “existing” type templates). In these cases there is not any data to be retained. In general, if there is not any data to retain, then this is the preferred method.
Rename & Reload
When data exist in the target object that should be retained, this approach should be considered. First, the target object is renamed. This can be done by simply renaming the target object directly or a backup object can be generated using the INTO command. If the target object is renamed, then care should be taken that index and constraint names are also renamed. Next, the modified component is executed with configuration set to create the target object. Finally, the data from the renamed source is used to load the component target object and the renamed object is the dropped.
This option requires that the developer perform two tasks manually; rename target object & reload the newly generated target object. Care should be taken if component changes result in a different set of data being loaded such as filter changes. In this case it is often more efficient or required to reload the target from the source. The renamed object is still important in this case as it serves as a backup which can be used to easily restore to the previous state.
Version with Synonyms
SQL Server offers the ability to use synonyms to alias tables and views. This functionality can be used to manage component changes is some situations. For example; consider a dimension component that is modified in a way that does not affect the dimension key. Foreign key lookups from dependent fact components will likely not be impacted by this type of modification. Therefore, it is possible to create a new dimension component version in LeapFrogBI with the desired changes & named differently such as myDim_v2. A synonym can be created to direct request for myDim to myDim_v2. The first iteration of such a method would require that the original target object, myDim, be renames or dropped. Subsequent versions can be managed by simply recreating the myDim synonym. In cases where ongoing changes are likely, this method should be considered.
Manual Alter
LeapFrogBI requires that each component target be in sync with the defined structure. In some cases, such as the addition or deletion of a field, it is a simple process to manually alter an existing target object. Developers must ensure, however, that the altered object matches the expected component target object including the data type and any required data type arguments. This method is only recommended in cases where modifications are clear and easily accomplished. Consider using other options, such as Rename & Reload, in more complex scenarios. The structure of the target object can be extracted from the modified component package or directly from the LeapFrogBI component details page.
To recap, deploying modified components without first dropping the target object does require that the developer clearly understand what is being changed. In ideal situations changes will be first completed in a development environment where dropping the target object is not an issue. Migrating to production can then be managed using one of the above described methods.
Why Doesn’t LeapFrogBI Generate Alter Scripts?
LeapFrogBI strives to simplify data mart development including both the initial design and ongoing modifications. While it is possible for LeapFrogBI to generate scripts that would automate the management of component modifications, doing so would often add overhead, hinder workflow, and inject risks to the extent that the costs would often outweigh the benefit. Consider the current workflow. Developers can create components, deploy, and modify components asynchronously without restriction within LeapFrogBI. To automate changes to a target object required by component modifications (without dropping first), LeapFrogBI would need to generate ALTER scripts. Two pieces of information would need to be known; the state of the deployed object (current structure) & the new structure based on the modified component. The second (new structure) requirement is known. The first requirement (current structure) is not known. Developers may have deployed the component at any point prior to the most recent modification. LeapFrogBI does not know at which point this deployment was completed. Therefore, it is not possible to reliably generate an ALTER statement which would modify the target object.
With this understanding in place we can start discussing what can be done to ensure that LeapFrogBI does know the structure of the currently deployed state. We could assume that the latest build prior to the modifications being made has been deployed. This would require that LeapFrogBI act as a type of source control so that the latest build is always known. If the hurdle was overcome, then there is still no way to validate that the build was truly deployed.
Another option would be to provide the developer a way to upload the current target object profile. With this information differencing logic could potentially generate the required ALTER statement. In limited cases this might work well, but in many cases the overhead related to repeatedly profiling the existing target object, uploading the profile, and building an ALTER script would outweigh the benefit.
Yet another option would be for LeapFrogBI to connect directly to the target system and extract the profile directly. Doing so, however, would require high level access from a public website to secure internal resources be facilitated. While there are ways to accomplish this (reverse proxy, vpn, etc…), the overhead of doing so would often outweigh the benefits. More importantly, the security risk that this option offers is likely too high for most organizations to accept.
Summary
In summary, there are pros and cons to all design decisions. LeapFrogBI’s zero footprint SaaS delivery model offers a level of flexibility that is unmatched. Perhaps in the future LeapFrogBI could offer an optional thick client agent that would collect information securely. Doing so would provide a method which would simplify the component modification process in some cases. As always, our ears are open to your ideas. When we find an improved design that makes sense, it will quickly find its way onto our product roadmap.
