This article talks about a golden rule called PRIDESTES DEPLOY to sort out any SQL BI (business intelligence) related issue and it is particularly useful for resolving database, data warehouse or even reporting related issues where the database is built through modern tools like Azure Data Studio.
I am coining a new term perhaps never used before, but it is practiced generally in almost every IT environment where teams are busy resolving issues round the clock.
PRIDESTES DEPLOY principle is not the solution it is rather a key behind a solution that may be related to any sensitive environment including the Production environment.
About PRIDESTES DEPLOY
After actively taking part in numerous professional life scenarios to resolve production related issues with reference to a database or data warehouse business intelligence solution I have been inclined to formalize the standard or principle that has helped us and can help other professionals and teams while they already use it and it is just a matter of a polite reminder.
I call it PRIDESTES DEPLOY someone may call it something else but as long as we follow it in accordance with its essence it is a fast-track way to either find a solution or the cause that can facilitate the solution.
Let us explore this in detail.
What is PRIDESTES DEPLOY?
PRIDESTES DEPLOY is an acronym and a principle that can be adopted in order to take steps in resolving an issue related to any IT framework including database or data warehouse-related issues.
What does PRIDESTES DEPLOY Stand for?
PRIDESTES DEPLOY stands for the following things:
P: Preliminary Analysis
R: Replicating the Problem
I: Identifying the cause
DES: Designing the Solution/Fix
TES: Testing the Solution/Fix
DEPLOY: Deploying the Solution/Fix
Preliminary Analysis (P)
As the name indicates any problem-solving story begins with getting to know the IT or SQL/BI-based problem and the best way to understand the nature of the problem and factors surrounding it is to analyze the issue.
Preliminary analysis suggests that the first step is always to analyze the problem domain by doing some checks to understand exactly what the problem is.
So, the P in PRISDESTES DEPLOY encourages the solution developer to start their work by first analysing the problem itself and this requires performing preliminary analysis.
Please remember the most important part of preliminary analysis is (analysing) the source that is responsible for reporting the problem such as the database issue reported by a customer to gather as much information as possible. However, this phase is quite challenging.
In a typical environment potential source reporting the problem can be one of the following:
- A Customer can simply raise a support ticket about a database/data warehouse/reporting problem he/she is faced with
- Post-deployment checks by you or other team members can sometimes reveal a potential issue
- Any automated checks can also raise an issue provided they are configured or designed to contain enough information for the preliminary analysis by the respective developer/analyst
No matter which source notified the issue your preliminary analysis should always target the most important piece of information found in the reported problem in order to gather precise information to speed up the problem-solving task.
There is one more important point that is about the assignment of the task for preliminary analysis. For example, a Power BI related issue must first be assigned to the Power BI team of the organization so that they can pass it onto the relevant expert often the developer for preliminary analysis.
Please remember, the preliminary analysis must focus on the following:
- What is the problem?
- A rough idea of solving the problem
- Information about next steps
- Information about the best person who can resolve the issue
Professional Tip
Finally, if you are an automation fan then I suggest any raised work item by the customer should automatically trigger preliminary analysis after assigning it to the most appropriate team and if you automate preliminary analysis further such as analysing the problem statement and extracting more information then that’s a plus but not always required.
Replicating the Problem (R)
The R in PRIDESTES DEPLOY refers to “Replicating the Problem”. Now, this is a very crucial step and should never be skipped, ignored, or underestimated unless you have a genuine reason to do so.
In the world of problem solvers, replicating a problem successfully is half the solution and sometimes it is the solution because you know what went wrong you just need to build it (solution) after you know it. However, please stay focussed as replicating the problem especially a production issue is not a child’s play because a very solid understanding and experience in the related field is required to be able to replicate a reported problem.
For example, if a customer reports an error in a Power BI report then you have to replicate the exact error to understand what might have caused the error.
Replicating the database or data warehouse related issue requires a fair amount of effort and time provided you have done the homework because you should have some means to replicate the problem. Now, this is a matter of a lot of discussions whether the production issue needs to be replicated in the production (Prod) or should be doing it in QA or Test environment.
Please bear in mind sometimes Production issues can only be replicated in Production and that’s why they are rightly called Production issues and in that case please have some solid strategy to replicate a production issue.
For example, if a customer says his record has failed to save in the database then you have to try to save the exact record (row) into the database and prepare to see the error but also keep an eye on the underlying database structure and the objects (procedures) taking part in saving the record because often the problem lies there for that particular case.
Professional Tip
Please remember it is always handy to have a live test workspace in case of Power BI error handling that must have a test user to replicate any problem reported by the customers.
Identifying the cause (I)
Now, what is a problem without identifying the cause.
For example, you are informed by a team (self-discovered production error) that for some strange reason duplicates are generated in the final FACT table. Now, unless you know the exact reason removing the duplicates from the current result set is not going to keep the system calm as this may happen again.
That’s why we must identify the actual cause of the issue to resolve it completely and the preceding steps (preliminary analysis and replicating the problem) can help a lot to identify the cause of the problem. In my opinion, this is a very demanding step as it can test the patience of the developer assigned to do the job of resolving the production issue because he may have to run numerous tests to find out the exact cause of the issue.
However, the experience and a solid background in that area (such as database, data warehouse or reporting) can be handy here. If you have spent over a decade resolving such issues, then it may turn out to be a piece of cake to identify the cause, but every Production issue in itself can be sometimes unique and challenging even though you have seen this before and know the cause.
Professional Tip
Please remember to be fully aware of the underlying architecture and the processes involved in a reporting solution (when handling a reporting issue reported by the customer) can help you to identify the cause of an issue although be prepared to run a couple of stringent tests to get to the point.
Designing the Solution/Fix (DES)
Finally designing the solution or fix is proof that you have worked hard in the previous steps.
You cannot perform well in this phase, if you have not done much in the previous phases that’s why in order to design the solution you must have done the right amount of preliminary analysis followed by replicating the problem and identifying the cause.
If you know the cause you can fix the problem and to fix the problem, you design the solution.
Sometimes designing the solution is as simple as modifying a small, stored procedure to behave correctly in case of a special use case that has caused the system error. Now you modify the stored procedure to ensure that you don’t break the existing code and you address the issue being reported.
On some other days, designing a solution can be adding a new data workflow to handle archiving requirements that are not correctly handled by the existing data warehouse architecture.
Professional Tip
Designing the fix or solution in a traditional professional problem-solving scenario ultimately means developing it with the help of tools and technologies and this also requires you to define how your solution/fix fits well into the current database or data warehouse architecture including the data movement activities to achieve the desired goal that may mean using some form of data integration service such as Azure Data Factory or Integration Services Projects (SSIS Packages) to extract, transform and load data.
Testing the Solution/Fix (TES)
Designing the solution is not enough it has to be tested to meet its requirements. Testing in itself is a skill as I have seen often times the developers can spot the issue in their code even before it is picked up by a tester. However, testing is also a very broad area.
You have to be pretty specific in this step and it is somewhat similar to unit testing provided you understand that the unit being tested can be as big as a report showing wrong figures and then narrowing it down to the object (table) behind that error or the workflow (data loading activity) that keeps the object active.
Testing your fix simply means you have to check from the user perspective whether the problem has been resolved or not and this can be a step where you can do your part of testing and then hand it over to the team of professionals responsible for the overall testing of the whole module.
Professional Tip
If it is a small issue, then I suggest the person who is developing the fix should test it first if he completely understands the bigger picture. For example, sometimes a business intelligence developer/analyst is smart enough to know that fixing this piece of the puzzle solves the mystery although he is not completely aware of all the other components that interact with the data warehouse such as data models, Power BI reports or real-time analysis in Excel.
Deploying the Solution/Fix (DEPLOY)
Finally, you have to deploy the solution or the fix that you have worked so hard on it.
Again, deployment can be a small piece of code ranging from a stored procedure modification to a fully functional process consisting of several objects and sub-processes, but the ultimate goal remains the smooth deployment to the Production server.
Considering the modern-day tools and technologies the deployment can be completely automated such as using Azure DevOps Builds and Release pipelines but there is nothing that stops you to use a simpler and even manual way of deploying an object to the Production system (with the help of the teams responsible for finally pushing it to the Server) as long as you have a predefined set strategy that is acceptable, workable and shareable.
Professional Tip
If your data warehouse (database) is managed by a SQL database project then you can simply deploy a small fix such as a modified stored procedure or view that addresses the issue to Dev (development database) using publish script or even schema compare tool and then from there (after successful unit testing) let it be handled by the next team or you can also deploy to Test and UAT regardless of the fact that you have got a very sophisticated fully managed deployment strategy or just a manual check list that is shared across other team members and is as solid as an automated deployment strategy.
I am not against automated builds and releases, but I don’t recommend for a sole developer to overcomplicate tasks that can assist rather than focussing and working on the main objective expected to be delivered as soon as possible.
A Word of Advice
We all know that practice makes a man perfect, but I read in an interesting article that sometimes practice does not make a man perfect if he is not doing the things correctly then he is mastering to do things incorrectly.
In other words, surround yourself with experts of your field and get the knowledge-based understanding of your area of expertise through knowledge sharing sessions or taking reputable good courses along with finding time to learn and implement things from credible sources that you learn and most importantly it is crucial to be aware of standard practices and how to encourage others to follow them.
Once you have a solid foundation and experience you can find your own ways and feel free to experiment with PRIDESTES DEPLOY to mend it according to your level of comfort but without compromising the standards and please keep in mind without dedication and commitment the journey is difficult so be proactive and be ready to improve and to embrace new technologies and tools that can help you to solve Production issues but at the same time work on the methodology even it is just a few dry steps that are actually helpful for you, your team and your organization to solve real-time production issues efficiently and effectively.
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023