Environment Transfer, Deploy and Execution dependencies

We have a setup with 3 environments (Dev/Test/Prod) running in the legacy version, with a BU Based ODX, DSA, MDW and Several SSLs. Based on a on-prem SQL Server setup. Fairly common I guess.

We are multiple developers on a shared project, making changes daily to the project, and therefore needs some QA process. Our target is to have changes and new functionality running on Test for a week before transfering to production. 

In addition to out centralised BI org, we support the data needs of analysts in the different departments. For this we have established replica databases of ODX and MDW. The analysts can read these replicas without interfering with the centralised data processing (The primary reason for the replicas). Along with the data read access, we have a database the analysist have the rights create objects in (typically views and stored procedures). The analyst environment will only be exposed on our prod platform.

We would like to provide a better SLA for our Analysts for new tables in the ODX_replica (down to minutes or hours depending on the urgency), but this would involve a environment transfer and a partial deployment (only deploying the relevant new bits).

Would any changes in the projects transfered, but NOT deployed, interfere (invalidate) with the currently running version. I’m primarily concerned about the data movement blocks, but might have other blind angles.

I’m quite familiar with source control in non TX data projects, but curious whether a partial deployment scenario like the above is achieveable in TX.

Any input. Links, videos, anything is welcome.

Thanks in advance.


Best answer by daniel 2 May 2023, 13:55

View original

5 replies

Userlevel 4
Badge +5

Dear @EvidaXrem ,

In my experience the answer is: It Depends.
When there is a version that has been transferred might interfere with the regular cases. It is because the complete project is beging transferred, so even when you don’t deploy the changes it might give problems during execution. The execution will run the new project version and might want to run tables, transformations or project related things that have not been deployed. This might cause errors and then will interfere with the execution of pats of the project. But if there are no mayor changes and the tables stay more or less the same you will not in to problems.

I hope that in newer versions partial release will becaome available.


Hope this helps


Userlevel 6
Badge +7

Hi @EvidaXrem ,

it can certainly work but you should develop some instincts coupled to certain types of changes. I.e. mapping changes are applied on-save, not waiting for a deploy. You also want to be wary of changing settings at the project and layer level. You may need to pre-emptively guard objects and add conditions to mappings that should not be active in a subsequent environment yet.

As different organisations have different types of changes happening, you usually learn what to look out for in your environment. If you are not using perspectives and work items to isolate changes and keep track of what is ready for deployment, I would certainly start there. 

Userlevel 6
Badge +5

Hi René

Daniel is correct, you will always transfer the whole project. There is no part transfer available.

When your project have been transferred, it should be able to see what changes have been applied and only deploy that. The issue will be that you may have some changes that aren’t ready for use yet and they will also be transferred.

@Thomas Lind @rory.smith @daniel 

Thanks for your input, it is also my understanding that I might break the system running in prod by transfering unfinished functionality to this environment, and only deploy the finished bits.

We are looking at another approach, as we will only support urgent changes to our ODX layer (and replica). The idea is to implement the changes in the DEV environment, and the also implementing the changes directly in the PROD version.

The idea is that the version we modify in PROD directly will be overwritten with the version transfered from DEV, and the deploy in PROD will fix the database objects previously deployed (extended props and such).

Do you see any issues with that?

Userlevel 6
Badge +7

As @daniel said: it depends (on details)

I always advise users to try to implement changes by adding new tables containing the change and keeping the old up until you know your new change is stable. You can then later remap to the new table and even later phase out the old table. Removing things and changing (meaning of) things can introduce more risk. I.e. if you change schema assignments in Dev you need to be careful how this gets pushed to Prod.

You will also potentially run into the situation where a hotfix is required in Prod that then needs to be backported to Dev by reimplementing it there.