May 24, 2017 at 1:53 pm
Setting the stage:
We were approached late last week about the need for a process to be established to load and maintain a nightly copy of our primary warehouse into a new application the dev team is working on.
The application itself will feed off of that warehouse data and in turn, allow for records to be added and modified within the backend COBOL system which feeds the warehouses nightly.
Any edits to the COBOL data are maintained in the application database, and are considered the current record until the next nightly warehouse refresh has occurred.
So to recap, three systems
Application \ Warehouse \ COBOL
Application updates itself and COBOL (real-time)
COBOL updates Warehouse (Nightly)
Warehouse then updates Application (Nightly) <-- This is what we want to avoid.
The Goal and the Problem:
The problem with all of this is that my team and I are currently in the process of reigning in a massive amount of data duplication that has occurred over the years that has made maintenance an absolute nightmare. We have several established warehouses and a number of one-off application databases that contain the same data and want to centralize as much of the duplicated data as possible to one core common warehouse structure. This new application is requiring that we provide them with expensive copies of data that is available elsewhere and we want to stop this if at all possible.
The developers are utilizing Entity Framework "Code First", and are expecting to pass this off to BQA in a little over a month, so while we want to roll all this back and design this another way we don't have that option. We were able to buy ourselves a few weeks to attempt to rebuild their source data sets, which primarily consists of joining a table from the Application DB and the Warehouse DB and returning the most up-to-date record based on a handful of criteria. We were able to cover all of this utilizing generally simple views.
The issues we are running into now is with the devs relying on SQL Full Text Search for their functionality. It doesn't appear that we can use FTS on views.
All of the databases in play here are SQL 2012 or 2016
Is there another way to accomplish this on the SQL side before we go back to the devs?
Is there another approach we can take to getting the devs their data without having to clone off our primary warehouse for the application?
Thank you in advance...
May 25, 2017 at 6:40 am
Views and Full Text Search, are not exactly a good mix. The only way I can see that working is if the search criteria are fixed and known in advance, and then you can have the views in play with known fixed value searches. I suspect that's not what can go on here. As views don't accept parameters, the ONLY alternative is a stored procedure. Why a view is needed to begin with, or why someone expects to have a view work with full text search is beyond my comprehension. Sounds like there's a significant over-arching data design issue going on here, along with way too much of "the left hand doesn't know what the right hand is doing". Only someone in sufficiently senior management has any chance at putting a stop to the madness.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 25, 2017 at 8:30 am
Sadly, you are correct with your latter point.
Thanks for the clarification otherwise.
May 26, 2017 at 1:28 pm
sgmunson - Thursday, May 25, 2017 6:40 AM... As views don't accept parameters, the ONLY alternative is a stored procedure.
Table valued functions can function exactly like a parameterized view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply