🧭🏭Business Intelligence: Microsoft Fabric (Part V: One Person Can’t Learn or Do Everything)

Business Intelligence Series

Today’s Explicit Measures webcast [1] considered an article written by Kurt Buhler (The Data Goblins): [Microsoft] "Fabric is a Team Sport: One Person Can’t Learn or Do Everything" [2]. It’s a well-written article that deserves some thought as there are several important points made. I can’t say I agree with the full extent of some statements, even if some disagreements are probably just a matter of semantics.

My main disagreement starts with the title “One Person Can’t Learn or Do Everything”. As clarified in webcast's chat, the author defines “everything" as an umbrella for “all the capabilities and experiences that comprise Fabric including both technical (like Power BI) or non-technical (like adoption data literacy) and everything in between” [1].

For me “everything” is relative and considers a domain's core set of knowledge, while "expertise" (≠ "mastery") refers to the degree to which a person can use the respective knowledge to build back-to-back solutions for a given area. I’d say that it becomes more and more challenging for beginners or average data professionals to cover the core features. Moreover, I’d separate the non-technical skills because then one will also need to consider topics like Data, Project, Information or Knowledge Management.

There are different levels of expertise, and they can vary in depth (specialization) or breadth (covering multiple areas), respectively depend on previous experience (whether one worked with similar technologies). Usually, there’s a minimum of requirements that need to be covered for being considered as expert (e.g. certification, building a solution from beginning to the end, troubleshooting, performance optimization, etc.). It’s also challenging to roughly define when one’s expertise starts (or ends), as there are different perspectives on the topics. 

Conversely, the term expert is in general misused extensively, sometimes even with a mischievous intent. As “expert” is usually considered an external consultant or a person who got certified in an area, even if the person may not be able to build solutions that address a customer’s needs. 

Even data professionals with many years of experience can be overwhelmed by the volume of knowledge, especially when one considers the different experiences available in MF, respectively the volume of new features released monthly. Conversely, expertise can be considered in respect to only one or more MF experiences or for one area within a certain layer. Lot of the knowledge can be transported from other areas – writing SQL and complex database objects, modelling (enterprise) semantic layers, programming in Python, R or Power Query, building data pipelines, managing SQL databases, etc. 

Besides the standard documentation, training sessions, and some reference architectures, Microsoft made available also some labs and other material, which helps discovering the features available, though it doesn’t teach people how to build complete solutions. I find more important than declaring explicitly the role-based audience, the creation of learning paths for the various roles.

During the past 6-7 months I've spent on average 2 days per week learning MF topics. My problem is not the documentation but the lack of maturity of some features, the gaps in functionality, identifying the respective gaps, knowing what and when new features will be made available. The fact that features are made available or changed while learning makes the process more challenging. 

My goal is to be able to provide back-to-back solutions and I believe that’s possible, even if I might not consider all the experiences available. During the past 22 years, at least until MF, I could build complete BI solutions starting from requirements elicitation, data extraction, modeling and processing for data consumption, respectively data consumption for the various purposes. At least this was the journey of a Software Engineer into the world of data. 

References:
[1] Explicit Measures (2024) Power BI tips Ep.328: Microsoft Fabric is a Team Sport (link)
[2] Data Goblins (2024) Fabric is a Team Sport: One Person Can’t Learn or Do Everything (link)

🧮ERP: Implementations (Part I: The Process Seems to be Broken)

 

Participating in several ERP implementations, one has the expectation that things will change for the better when moving from one implementation to another. Things change positively in certain areas as experience is integrated, though on average the overall performance seems to be the same. Thus, one may wonder, how can this happen? Of course, there are so many explanations - what went wrong, what could have been done better, and the list is usually quite big. However, the history repeats in the next implementation. Something seems to be broken, or maybe this is the way implementations should work, though I doubt this!

An ERP implementation starts with a need and the customer usually has an idea of what the respective need is about. It might even have a set of high-level or even low-level requirements, which should be the case when starting on such a journey. Then the customer selects an implementation partner, event followed by a period of discovery in which the partner learns more about the business including the overall infrastructure, business processes, data and people. Once the requirements are available, the partner can evaluate them to identify the deviations from the standard functionality available and that translate into customizations, sketch solutions, respectively make a first estimate of the costs and resources needed.

Of course, there can be multiple iterations of the process in which the requirements are reviewed, reevaluated, justified, prioritized by all parties and a common understanding, respectively an agreement on the scope and expectations is reached. In the process some requirements are dropped, others are modified or postponed for a later phase or later phases. The whole process can take a few months, though it’s mandatory for creating a workable estimate used as basis for the statement of work and the overall contract.

In parallel the parties can also work on a project plan and agree upon a project methodology, following that once the legal paperwork is signed, resources to be allocated to the project. A common practice is then for the functional consultants to generate based on the requirements a set of documents - functional design documents (FDD), process diagrams - that should be used as basis for the setup, for programming the customizations and User acceptance testing (UAT). Of course, the documents need to be reviewed by the business, gaps or misunderstandings mitigated, and this takes several iterations until the business can sign-off on the respective documents. It’s the point where the setup and programming can start, usually half a year, or even a year or more after the initial steps.

Depending on the scope, in the best-case scenario the setup will take one to two months, at least until having a system ready for UAT with business data as needed for Go-Live. The agreed customizations can translate in further months and effort not only for programming, but also for testing, reviewing and further mitigations. This would be the time when many of the key users see for the first time a working version of the system, which frankly might be too late. Of course, they read and reread the FDDs, though until this point everything was very abstract and no matter how good such documents were written, they can’t replace the hand-on experience with working with the system, discovering the functionality, understanding how it works.

In the best-case scenario, the key-users are satisfied with the results and the UAT, respectively Go-Live can go on as planned, however the expectations for first time right are seldom (never) met. Further iterations and delays are then involved. Overall, the process doesn’t seem to be efficient!

Previous <<||>> Next

💼Project Management: Project Planning (Part V: Some Thoughts on Planning II)

A project’s dependency on resources’ (average) utilization time (UT) and quality expectations expressed as a quality factor (QF) doesn’t come as a surprise, as hopefully one is acquainted with project’s triangle which reflects the dependency between scope, cost and time in respect to quality. Even if this dependency is intuitive, it’s difficult to express it in numbers and study the way it affects the project. That was the purpose of the model built previously.

From the respective model there are a few things to ponder. First, it’s a utopia to plan with 90% UT, unless one is really sure that the resources have enough work to bring the idle time close to zero. A single person can achieve maybe a 90% UT if he works alone on the project, though even then there are phases in which the input or feedback from other people is necessary. The more people involved into the project and the higher the dependency between their activities, the higher the chances that the (average) UT will decrease considerably.

When in addition there’s also a geographical or organizational boundary between team members, the UT will decrease even more. In consequence, in big projects like ERP implementations the team members from customer and vendor side are allocated fully to the project; when this is not possible, then on the vendor side the consultants need to be involved in at least two projects to cover the idle time. Of course, with good planning, communication, and awareness of the work ahead one can try minimizing the idle time, though that’s less likely to happen.

Probably, a better idea would be planning with 75% or even 60% UT though the values depend on team's experience in handling similar projects. If the team members are involved also in operational activities or other projects, then a 50% UT is more realistic.

Secondly, in the previous post was considered in respect to quality the 80%-20% rule which applies to the various deliverables, though the rule has a punctual character. Taken on the average the rule is somehow attenuated. Therefore, in the model was considered a sprung between factors of 1 to 2 with a step of 0,25 for each 5% quality increase. It's needed to prove whether the values are realistic and how much they depend on project's characteristics.

On the other side, quality is difficult to quantify, and 100% quality is hypothetical. One discusses in theory about 3 sigma (the equivalent of 93,3 accuracy) or 4 sigma (99,4 accuracy) in respect to the number of errors found in the code, though from there on everything is fuzzy. In software projects each decision has the potential of leading to an error, and there’s lot of interpretability as long there’s no fix basis against to compare the deviations. One needs precise and correct specification for that.

I think that one should target in a first phase 80% quality (on average) and further build from there, try to improve the quality iteratively as the project goes on and as lessons are learned. In other words, a project plan, a concept, a design document doesn’t need to be perfect from the beginning but should be good enough to allow working with it. One can detail them as progress is made into the project, and hopefully their quality should converge to a value that is acceptable for the business.

Thirdly, in case a planning tool was used, one can use the model backwards to roughly prove timeline’s feasibility, dividing the planned effort by the estimated effort and the number of resources involved to identify the implied utilization time.  

#️⃣☯Software Engineering: Concept Documents (The Good, the Bad and the Ugly)

A concept document (simply a concept) is a document that describes at high level the set of necessary steps and their implications in order to achieve a desired result, typically making the object of a project. In other words, it describes how something can be done or achieved, respectively how a problem can be solved.

The Good: The main aim of the document is to give all the important aspects and to assure that the idea is worthy of consideration, that the steps considered provide a good basis for further work, respectively to provide a good understanding for the various parties involved, Therefore, concepts are used as a basis for the sign-off, respectively for the implementation of software and hardware solutions.

 A concept provides information about the context, design, architecture, security, usage, purpose and/or objectives of the future solution together with the set of assumptions, constraints and implications. A concept is not necessarily a recipe because it attempts providing a solution for a given problem or situation that needs a solution. Even if it bears many similarities in content and structure a concept it also not a strategy, because the strategy offers an interpretation of the problem, and also not a business case, because the later focuses mainly on the financial aspects.

A concept proves thus to be a good basis for implementing the described solution, being often an important enabler. On the other side, a written concept is not always necessary, even if conceptualization must exist in implementers’ head.

The Bad: From these considerations projects often consider the elaboration of a concept before further work can be attempted. To write such a document is needed to understand the problem/situation and be capable of sketching a solution in which the various steps or components fit together as the pieces of a puzzle. The problem is that the more complex the problem to be solved, the fuzzier the view and understanding of the various pieces becomes, respectively, the more challenging it becomes to fit the pieces together. In certain situations, it becomes almost impossible for a single person to understand and handle all the pieces. Solving the puzzle becomes a collective approach where the complexity is broken in manageable parts in the detriment of other aspects.

Writing a concept is a time-consuming task. The more accuracy and details are needed, the longer it takes to write and review the document, time that’s usually stolen from other project phases, especially when the phases are considered as sequential. It takes about 20% from the total effort needed to write a ‘perfect’ concept for writing a concept that covers only 80% of the facts, while 80% from the effort to consider the remaining 20% of the facts as the later involve multiple iterations. In extremis, aiming for perfection will make one start the implementation late or not start at all. It’s a not understandable pedantry with an important impact on projects'
 timeline and quality in the hope of a quality increase, which is sometimes even illusory.

The Ugly: The concept-based approach is brought to extreme in ERP implementations where for each process or business area is needed to write a concept, which often carries fancy names – solution design document, technical design document, business process document, etc. Independently how it is called, the purpose is to describe how the solution is implemented. The problem is that the conceptualization phase tends to take much longer than planned given the dependencies between the various business area in terms of functionality and activities. The complexity can become overwhelming, with an important impact on project’s budget, time and quality.

💻IT: Asset (Definitions)

[process asset:] "Anything that the organization considers useful in attaining the goals of a process area." (Sandy Shrum et al, "CMMI: Guidelines for Process Integration and Product Improvement", 2003)

[organizational process assets:] "Artifacts that relate to describing, implementing, and improving processes (e.g., policies, measurements, process descriptions, and process implementation support tools). The term process assets is used to indicate that these artifacts are developed or acquired to meet the business objectives of the organization, and they represent investments by the organization that are expected to provide current and future business value." (Sandy Shrum et al, "CMMI: Guidelines for Process Integration and Product Improvement", 2003)

[process asset:] "Artifacts that relate to describing, implementing, and improving processes (e.g., policies, process descriptions, guidance, examples, aids, checklists, project closeout reports, metrics data, and training materials). The artifacts meet the organization’s business objectives, and represent investments expected to provide current and future business value." (Richard D Stutzke, "Estimating Software-Intensive Systems: Projects, Products, and Processes", 2005)

[organizational process assets:] "Any or all process-related assets, from any or all of the organizations involved in the project that are or can be used to influence the project's success. These process assets include formal and informal plans, policies, procedures, and guidelines. The process assets also include the organizations’ knowledge bases such as lessons learned and historical information." (Project Management Institute, "Practice Standard for Project Estimating", 2010)

[organizational process assets:] "Any or all process related assets, from any or all of the organizations involved in the project that are or can be used to influence the project's success. These process assets include formal and informal plans, policies, procedures, and guidelines. The process assets also include the organizations' knowledge bases such as lessons learned and historical information." (Cynthia Stackpole, "PMP Certification All-in-One For Dummies", 2011)

[IT assets:] "Tangible deliverables created during the course of an IT project that can be used in other similar projects. Examples include design, software code, or a testing scenario." (Janice M Roehl-Anderson, "IT Best Practices for Financial Managers", 2010)

[organizational process assets:] "Plans, processes, policies, procedures, and knowledge bases specific to and used by the performing organization. " (Project Management Institute, "The Standard for Portfolio Management" 3rd Ed., 2012)

[organizational process assets:] "Plans, processes, policies, procedures, and knowledge bases that are specific to and used by the performing organization." (For Dummies, "PMP Certification All-in-One For Dummie", 2nd Ed., 2013)

[Software assets:] "software tools needed to manipulate the organization's information to accomplish the organization's mission." ( Manish Agrawal, "Information Security and IT Risk Management", 2014)

"Data contained in an information system; or a service provided by a system; or a system capability, such as processing power or communication bandwidth; or an item of system equipment (that is, a system component - hardware, firmware, software, or documentation); or a facility that houses system operations and equipment." (William Stallings, "Effective Cybersecurity: A Guide to Using Best Practices and Standards", 2018)

"Any item that has value to the organisation." (ISO/IEC 27000:2012)

#️⃣Software Engineering: Programming (Part XIV: Good Programmer, Bad Programmer)

Software Engineering Series

The use of denominations like 'good' or 'bad' related to programmers and programming carries with it a thin separation between these two perceptional poles that represent the end results of the programming process, reflecting the quality of the code delivered, respectively the quality of a programmer’s effort and  behavior as a whole. This means that the usage of the two denominations is often contextual, 'good' and 'bad' being moving points on a imaginary value scale with a wide range of values within and outside the interval determined by the two.

The 'good programmer' label is a idealization of the traits associated with being a programmer – analyzing and understanding the requirements, filling the gaps when necessary, translating the requirements in robust designs, developing quality code with a minimum of overwork, delivering on-time, being able to help others, to work as part of a (self-organizing) team and alone, when the project requires it, to follow methodologies, processes or best practices, etc. The problem with such a definition is that there's no fix limit, considering that programmer’s job description can include an extensive range of requirements.

The 'bad programmer' label is used in general when programmers (repeatedly) fail to reach others’ expectations, occasionally the labeling being done independently of one’s experience in the field. The volume of bugs and mistakes, the fuzziness of designs and of the code written, the lack of comments and documentation, the lack of adherence to methodologies, processes, best practices and naming conventions are often considered as indicators for such labels. Sometimes even the smallest mistakes or the wrong perceptions of one’s effort and abilities can trigger such labels.

Labeling people as 'good' or 'bad' has the tendency of reinforcing one's initial perception, in extremis leading to self-fulfilling prophecies - predictions that directly or indirectly cause themselves to become true, by the very terms on how the predictions came into being. Thus, when somebody labels another as 'good' or 'bad' he more likely will look for signs that reinforce his previous believes. This leads to situations in which "good" programmers’ mistakes are easier overlooked than 'bad' programmers' mistakes, even if the mistakes are similar.

A good label can in theory motivate, while a bad label can easily demotivate, though their effects depend from person to person. Such labels can easily become a problem for beginners, because they can easily affect beginners' perception about themselves. It’s so easy to forget that programming is a continuous learning process in which knowledge is relative and highly contextual, each person having strengths and weaknesses.

Each programmer has a particular set of skills that differentiate him from other programmers. Each programmer is unique, aspect reflected in the code one writes. Expecting programmers to fit an ideal pattern is unrealistic. Instead of using labels one should attempt to strengthen the weaknesses and make adequate use of a person’s strengths. In this approach resides the seeds for personal growth and excellence.

There are also programmers who excel in certain areas - conceptual creativity, ability in problem identification, analysis and solving, speed, ingenuity of design and of making best use of the available tools, etc. Such programmers, as Randall Stross formulates it, “are an order of magnitude better” than others. The experience and skills harnessed with intelligence have this transformational power that is achievable by each programmer in time.

Even if we can’t always avoid such labeling, it’s important to become aware of the latent force the labels carry with them, the effect they have on our colleagues and teammates. A label can easily act as a boomerang, hitting us back long after it was thrown away.

Previous Post <<||>>  Next Post

🧭Business Intelligence: Key Performance Indicators [KPI] (Part I: An Introduction)

Key Performance Indicators (KPIs) are quantifiable measurements (aka metrics) that reflect the critical success factor of an organization in respect to their strategic goals and objectives. They allow measuring the progress toward reaching the defined goals and, to some degree, forecasting the further  evolution. They help keeping the focus on the goals, increases awareness in what concerns the goals and provide visibility into the business.

As they reflect an organization’s objectives, KPIs need to be anchored and aligned with them. If there’s no association with an objective then one doesn’t deal with a KPI but with other form of performance metric. Therefore KPIs need to change with the objectives, they are not fix.

One important requirement for a KPI is to be defined using SMART (specific, measurable, attainable, relevant, time-bound) criteria. Thus a KPI needs to be clear and unambiguous (specific), needs to measure the progress against a goal (measurable), needs to be realistic (attainable), needs to be relevant for the business and its current strategy (relevant), and needs to specify when the result(s) can be achieved (time-bound). To the SMART criteria some consider also the requirement for a KPI to be periodically and consistently evaluated and reviewed (trackable) and agreed between the parties afected by it (agreed).

A KPI needs to be visible within an organization, understandable and non-redundant. Even if KPIs are a tool for the upper management, their definition and impact needs to be visible and understood by all the people working with it, even if this can lead to unexpected behavior. The requirement for non-redundancy implies a partition of the KPIs to limit the cases in which two or more KPIs provide the same information.

A KPI needs to be supported by actions and needs to trigger actions. It’s nice to have KPIs reported periodically to the upper management, though as long no action is triggered, there’s no value in it. A KPI is kind of reinforcement for questions like: “why are we doing good/bad?”. The negative variations must trigger some form of action, however also the positive variation could involve further analysis to understand what caused the improvement.

The variation of a KPI needs to be supported by facts – each variation needs to be explainable in one form or another. A number without a story remains a number that can or not be trusted. Therefore, it might be needed to have further metrics or reports that support the KPIs, that can be used to identify the sources for variation, in order to understand the data.

Last but not the least KPIs need to be documented. The documentation needs to include at minimum a rough definition that includes the rationale, the boundary as well the critical values, metric’s owners, unit of measure, etc. In addition, one can add historical information about the KPI in respect to when and what caused variations, respectively how the variations were brought under control.

KPIs vary from an organization to another, the variation in not only influenced by the different goals organizations might have, but also based on the fact that organizations tend to measure different things, often the wrong things. It’s in general recommended to have a small number of KPIs that reflect in one dasboard how the business is doing and what is important for the business.

KPIs provide a basis for change by providing insights into what needs to change to improve some aspects of the business. When adequately defined and measured, KPIs provide a good perspective over an organization’s effort in achieving its goals and objectives, and therefore a good tool for monitoring and stirring organization’s strategy.

Previous Post <<||>> Next Post

🌡Performance Management: Mastery (Part I: The Need for Perfection vs. Excellence)

A recurring theme occurring in various contexts over the years seemed to be corroborated with the need for perfection, need going sometimes in extremis beyond common sense. The simplest theory attempting to explain at least some of these situations is that people tend to confuse excellence with perfection, from this confusion deriving false beliefs, false expectations and unhealthy behavior. 

Beyond the fact that each individual has an illusory image of what perfection is about, perfection is in certain situations a limiting force rooted in the idealistic way of looking at life. Primarily, perfection denotes that we will never be good enough to reach it as we are striving to something that doesn’t exist. From this appears the external and internal criticism, criticism that instead of helping us to build something it drains out our energy to the extent that it destroys all we have built over the years with a considerable effort. Secondarily, on the long run, perfection has the tendency to steal our inner peace and balance, letting fear take over – the fear of not making mistakes, of losing the acceptance and trust of the others. It focuses on our faults, errors and failures instead of driving us to our goals. In extremis it relieves the worst in people, actors and spectators altogether. 

In its proximate semantics though at diametral side through its implications, excellence focuses on our goals, on the aspiration of aiming higher without implying a limit to it. It’s a shift of attention from failure to possibilities, on what matters, on reaching our potential, on acknowledging the long way covered. It allows us building upon former successes and failures. Excellence is what we need to aim at in personal and professional life. Will Durant explaining Aristotle said that: “We are what we repeatedly do. Excellence, then, is not an act, but a habit.” 

People who attempt giving 100% of their best to achieve a (positive) goal are to admire, however the proximity of 100% is only occasionally achievable, hopefully when needed the most. 100% is another illusory limit we force upon ourselves as it’s correlated to the degree of achievement, completeness or quality an artefact or result can ideally have. We rightly define quality as the degree to which something is fit for purpose. Again, a moving target that needs to be made explicit before we attempt to reach it otherwise quality envisions perfection rather than excellence and effort is wasted. 

Considering the volume of effort needed to achieve a goal, Pareto’s principles (aka the 80/20 rule) seems to explain the best its underlying forces. The rule states that roughly 80% of the effects come from 20% of the causes. A corollary is that we can achieve 80% of a goal with 20% of the effort needed altogether to achieve it fully. This means that to achieve the remaining 20% toward the goal we need to put four times more of the effort already spent. This rule seems to govern the elaboration of concepts, designs and other types of documents, and I suppose it can be easily extended to other activities like writing code, cleaning data, improving performance, etc. 

Given the complexity, urgency and dependencies of the tasks or goals before us probably it's beneficial sometimes to focus first on the 80% of their extent, so we can make progress, and focus on the remaining 20% if needed, when needed. This concurrent approach can allow us making progress faster in incremental steps. Also, in time, through excellence, we can bridge the gap between the two numbers as is needed less time and effort in the process.

#️⃣Software Engineering: Programming (Part VII: Documentation - Lessons Learned)

Software Engineering Series

Introduction

“Documentation is a love letter that you write to your future self.”
Damian Conway

    For programmers as well for other professionals who write code, documentation might seem a waste of time, an effort few are willing to make. On the other side documenting important facts can save time sometimes and provide a useful base for building own and others’ knowledge. I found sometimes on the hard way what I needed to document. With the hope that others will benefit from my experience, here are my lessons learned:

 

Lesson #1: Document your worked tasks

“The more transparent the writing, the more visible the poetry.”
Gabriel Garcia Marquez

   Personally I like to keep a list with what I worked on a daily basis – typically nothing more than 3-5 words description about the task I worked on, who requested it, and eventually the corresponding project, CR or ticket. I’m doing it because it makes easier to track my work over time, especially when I have to retrieve some piece of information that is somewhere else in detail documented.

   Within the same list one can track also the effective time worked on a task, though I find it sometimes difficult, especially when one works on several tasks simultaneously. In theory this can be used to estimate further similar work. One can use also a categorization distinguishing for example between the various types of work: design, development, maintenance, testing, etc. This approach offers finer granularity, especially in estimations, though more work is needed in tracking the time accurately. Therefore track the information that worth tracking, as long there is value in it.

   Documenting tasks offers not only easier retrieval and base for accurate estimations, but also visibility into my work, for me as well, if necessary, for others. In addition it can be a useful sensemaking tool (into my work) over time.

Lesson #2: Document your code

“Always code as if the guy who ends up maintaining your code will be
a violent psychopath who knows where you live.”
Damian Conway

    There are split opinions over the need to document the code. There are people who advise against it, and probably one of most frequent reasons is rooted in Agile methodology. I have to stress that Agile values “working software over comprehensive documentation”, fact that doesn’t imply the total absence of documentation. There are also other reasons frequently advanced, like “there’s no need to document something that’s already self-explanatory “(like good code should be), “no time for it”, etc. Probably in each statement there is some grain of truth, especially when considering the fact that in software engineering there are so many requirements for documentation (see e.g. ISO/IEC 26513:2009).

   Without diving too deep in the subject, document what worth documenting, however this need to be regarded from a broader perspective, as might be other people who need to review, modify and manage your code.

    Documenting code doesn’t resume only to the code being part of a “deliverables”, but also to intermediary code written for testing or other activities. Personally I find it useful to save within the same fill all the scripts developed within same day. When some piece of code has a “definitive” character then I save it individually for reuse or faster retrieval, typically with a meaningful name that facilitates file’s retrieval. With the code it helps maybe to provide also some metadata like: a short description and purpose (who and when requested it).

   Code versioning can be used as a tool in facilitating the process, though not everything worth versioning.

 

Lesson #3: Document all issues as well the steps used for troubleshooting and fixing

“It’s not an adventure until something goes wrong.”
Yvon Chouinard

   Independently of the types of errors occurring while developing or troubleshooting code, one of the common characteristics is that the errors can have a recurring character. Therefore I found it useful to document all the errors I got in terms of screenshots, ways to fix them (including workarounds) and, sometimes also the steps followed in order to troubleshoot the problem.

   Considering that the issues are rooted in programming fallacies or undocumented issues, there is almost always something to learn from own as well from others’ errors. In fact, that was the reasons why I started the “SQL Troubles” blog – as a way to document some of the issues I met, to provide others some help, and why not, to get some feedback.

 

Lesson #4: Document software installations and changes in configurations

   At least for me this lesson is rooted in the fact that years back quite often release candidate as well final software was not that easy to install, having to deal with various installation errors rooted in OS or components incompatibilities, invalid/not set permissions, or unexpected presumptions made by the vendor (e.g. default settings). Over the years installation became smoother, though such issues are still occurring. Documenting the installation in terms of screenshots with the setup settings allows repeating the steps later. It can also provide a base for further troubleshooting when the configuration within the software changed or as evidence when something goes wrong.

   Talking about changes occurring in the environment, not often I found myself troubleshooting something that stopped working, following to discover that something changed in the environment. It’s useful to document the changes occurring in an environment, importance stressed also in “Configuration Management” section of ITIL® (Information Technology Infrastructure Library).

 

Lesson #5: Document your processes

“Verba volant, scripta manent.” Latin proverb
"Spoken words fly away, written words remain."

    In process-oriented organizations one has the expectation that the processes are documented. One can find that it’s not always the case, some organization relying on the common or individual knowledge about the various processes. Or it might happen that the processes aren’t always documented to the level of detail needed. What one can do is to document the processes from his perspective, to the level of detail needed.

 

Lesson #6: Document your presumptions

“Presumption first blinds a man, then sets him a running.”
Benjamin Franklin

   Probably this is more a Project Management related topic, though I find it useful also when coding: define upfront your presumptions/expectations – where should libraries lie, the type and format of content, files’ structure, output, and so on. Even if a piece of software is expected to be a black-box with input and outputs, at least the input, output and expectations about the environment need to be specified upfront.

 

Lesson #7: Document your learning sources

“Intelligence is not the ability to store information, but to know where to find it.”
Albert Einstein

    Computer specialists are heavily dependent on internet to keep up with the advances in the field, best practices, methodologies, techniques, myths, and other knowledge. Even if one learns something, over time the degree of retention varies, and it can decrease significantly if it wasn’t used for a long time. Nowadays with a quick search on internet one can find (almost) everything, though the content available varies in quality and coverage, and it might be difficult to find the same piece of information. Therefore, independently of the type of source used for learning, I found it useful to document also the information sources.

 

Lesson #8: Document the known as well the unknown

 

“A genius without a roadmap will get lost in any country but an average person
with a roadmap will find their way to any destination.”
Brian Tracy

   Over the years I found it useful to map and structure the learned content for further review, sometimes considering only key information about the subject like definitions, applicability, limitations, or best practices, while other times I provided also a level of depth that allow me and others to memorize and understand the topic. As part of the process I attempted to keep the  copyright attributions, just in case I need to refer to the source later. Together with what I learned I considered also the subjects that I still have to learn and review for further understanding. This provides a good way to map what I known as well what isn’t know. One can use for this a rich text editor or knowledge mapping tools like mind mapping or concept mapping.

Conclusion

    Documentation doesn’t resume only to pieces of code or software but also to knowledge one acquires, its sources, what it takes to troubleshoot the various types of issues, and the work performed on a daily basis. Documenting all these areas of focus should be done based on the principle: “document everything that worth documenting”.

Previous Post <<||>>  Next Post

♜Strategic Management: Risk Register (Definitions)

"A record of a company’s risks." (Annetta Cortez & Bob Yehling, "The Complete Idiot's Guide® To Risk Management", 2010)

"The document containing the results of the qualitative risk analysis, quantitative risk analysis, and risk response planning. The risk register details all identified risks, including description, category, cause, probability of occurring, impact(s) on objectives, proposed responses, owners, and current status." (Project Management Institute, "Practice Standard for Project Estimating", 2010)

"Formal document and management tool that records all risks identified by the project team, along with the team’s assessment of the risks, plans to manage the risks, and progress against the plans." (Mike Clayton, "Brilliant Project Leader", 2012)

"A document in which the results of risk analysis and risk response planning are recorded." (For Dummies, "PMP Certification All-in-One For Dummies" 2nd Ed., 2013)

"A documented collection of the risks impacting an activity or organization." (Sally-Anne Pitt, "Internal Audit Quality", 2014)

"A record of information about identified risks." (David Sutton, "Information Risk Management: A practitioner’s guide", 2014)

"Formal record of identified risks." (Chartered Institute of Building, "Code of Practice for Project Management for Construction and Development" 5th Ed., 2014)

"A repository in which outputs of risk management processes are recorded." (Project Management Institute, "A Guide to the Project Management Body of Knowledge (PMBOK® Guide )", 2017

"A component that captures details of individual project risks, including a list of identified risks, potential risk owners, and potential risk responses." (Cate McCoy & James L Haner, "CAPM Certified Associate in Project Management Practice Exams", 2018)

"A document in which the iterative results of the risk identification, risk analysis, and risk response planning processes are recorded." (H James Harrington & William S Ruggles, "Project Management for Performance Improvement Teams", 2018)

"A record of information about identified risks." (ISO Guide 73:2009)

♜Strategic Management: Strategic Planning (Definition)

"[…] strategic planning […] is the continuous process of making present entrepreneurial (risk-taking) decisions systematically and with the greatest knowledge of their futurity; organizing systematically the efforts needed to carry out these decisions; and measuring the results of these decisions against the expectations through organized, systematic feedback." (Peter F Drucker, "Management: Tasks, Responsibilities, Practices", 1973)

"The process of determining how a problem or opportunity may be responded to. Involves identifying problems or opportunities, analyzing relevant characteristics of the circumstances, organizing the formal response, deputizing a leader to head the response effort, and supervising the person(s) selected." (Robert McCrie, "Security Operations Management" 2nd Ed., 2006)

"Written record of a strategic plan, usually consisting of an overview, strategy charter, description of the current environment, research findings, tactics, roles and accountabilities, key performance indicators, and recommended next steps." (Teri Lund & Susan Barksdale, "10 Steps to Successful Strategic Planning", 2006)

"The implementation of an organization's objectives. Strategic planning decisions will have long-term impacts on the organization while operational decisions are day-to-day in nature." (Jae K Shim & Joel G Siegel, "Budgeting Basics and Beyond", 2008)

"The selection of short- and long-term objectives and the drawing up of tactical and strategic plans to achieve those objectives. After deciding on a set of strategies to be followed, the organization needs more specific plans, such as locations, methods of financing, and hours of operation. As these plans are made, they will be communicated throughout the organization. When implemented, the plans will serve to coordinate the efforts of all parts of the organization toward the company's objectives." (Jae K Shim & Joel G Siegel, "Budgeting Basics and Beyond", 2008)

"A deliberative, disciplined approach to producing fundamental decisions and actions that shape and guide what an organization (or other entity) is, what it does, and why it does it.” (John M Bryson, 2011)

"A long-range plan that serves as a business’s road map for the future. It includes the product lines and services, the number of employees, technology requirements, industry trends, competitor analysis, revenue and profitability goals, types of customers, and long-range marketing plans." (Gina Abudi & Brandon Toropov, "The Complete Idiot's Guide to Best Practices for Small Business", 2011)

"A series of processes in which an organization selects and arranges its businesses or services to keep the organization viable even when unexpected events distrupt one or more of its business's markets, products, or services." (Linda Volonino & Efraim Turban, "Information Technology for Management" 8th Ed., 2011)

"A high-level document that explains the organization's vision and mission, plus the approach that will be adopted to achieve this mission and vision, including the specific goals and objectives to be achieved during the period covered by the document." (Project Management Institute, "The Standard for Portfolio Management" 3rd Ed., 2012)

"The process by which an organization envisions its future and develops the necessary goals and procedures to achieve that vision." (Joan C Dessinger, "Fundamentals of Performance Improvement" 3rd Ed., 2012)

"A systematic process of envisioning a desired future and translating this vision into broadly defined goals or objectives and a sequence of steps to achieve them." (Robert F Smallwood, "Information Governance: Concepts, Strategies, and Best Practices", 2014)

"The process by which organizations identify a desired outcome, the resources required to support that outcome, and the plan to achieve the outcome. Typically, strategic planning is an important step in identifying the creation of new competitive advantages." (Evan Stubbs, "Big Data, Big Innovation", 2014)

"A process of selecting from alternative courses of action, matching that with the available resources, and combining these in a way that will most effectively achieve the objective; Intended action toward an organizational goal or objective." (Ken Sylvester, "Negotiating in the Leadership Zone", 2015)

"A formalised step-by-step set of procedures for coordinating the strategy process." (Duncan Angwin & Stephen Cummings, "The Strategy Pathfinder" 3rd Ed., 2017)

"A document used to communicate with the organization the organization’s goals, the actions needed to achieve those goals, and all the other critical elements developed during the planning exercise." (William Stallings, "Effective Cybersecurity: A Guide to Using Best Practices and Standards", 2018)

🔦Process Management: Baseline (Definitions)

"A documented characterization of the actual results achieved by following a process, which is used as a benchmark for comparing actual process performance against expected process performance." (Sandy Shrum et al, "CMMI: Guidelines for Process Integration and Product Improvement", 2003)

"A range of expected results that would normally be achieved by following a defined process. Often expressed in terms of the process control limits defined by the discipline of statistical process control." (Richard D Stutzke, "Estimating Software-Intensive Systems: Projects, Products, and Processes", 2005)

"Documented process performance values used as a reference to compare actual and expected process performance." (Richard D Stutzke, "Estimating Software-Intensive Systems: Projects, Products, and Processes", 2005)

"A documented characterization of the range of expected results that would normally be achieved by following a specific process under typical circumstances." (Sally A Miller et al, "People CMM: A Framework for Human Capital Management" 2nd Ed., 2009)

"A documented characterization of the results achieved by following a process that is used as a benchmark for comparing actual process performance against expected process performance." (Sally A Miller et al, "People CMM: A Framework for Human Capital Management" 2nd Ed., 2009)

[capability baseline:] "A statistically based description of the performance or results of a process that has been performed repeatedly. Capability baselines can quantify attributes of the process (e.g., effort or duration) or of the product produced by the process (e.g., amount or quality). Control charts used in statistical process control are one form of capability baseline. However, other statistical representations may be more appropriate, depending on the nature of the data being characterized. The purpose of a capability baseline is to predict outcomes and to interpret the results of process performance." (Sally A Miller et al, "People CMM: A Framework for Human Capital Management" 2nd Ed., 2009)

🔦Process Management: Process model (Definitions)

"A formal, detailed description of a process that covers policies, activities, work products, roles, and responsibilities. Typically contains standards and procedures and identifies methods and tools as well. Contrast with process architecture." (Richard D Stutzke, "Estimating Software-Intensive Systems: Projects, Products, and Processes", 2005)

"A formal description of a business process. The definition is performed via a process definition language (PDL), which in most cases is WfMS-dependent." (C Combi & G Pozzi, "Workflow Management Systems for Healthcare Processes", 2008)

"Any description of a process (not necessarily formal), that shows a series of steps aimed at accomplishing some goal." (Harry S Delugach, "Formal Analysis of Workflows in Software Development", 2009)

"A means of representing the interrelated processes of a system at any level of detail with a graphic network of symbols, showing data flows, data stores, data processes, and data sources/destinations. Process modeling techniques are used to represent processes graphically for clearer understanding, communication, and refinement." (Anthony D Giordano, "Data Integration Blueprint and Modeling", 2010)

"Processes models (PM) are processes of the same nature that are classified together into a model. It involves the description and/or prescription of processes by the instantiation of levels to define process procedures and fuzzes." (Oluwole A Olatunji & William D Sher, "The Applications of Building Information Modelling in Facilities Management", 2010)

"(1) A framework wherein processes of the same nature are classified into an overall model, e.g. a test improvement model. (2) A method-independent process description of development processes." (IQBBA, "Standard glossary of terms used in Software Engineering", 2011)

"A model of the functions, activities, and procedures performed in any organization. A business process model may consist of: 1.A context diagram showing the relationship of the overall process to those outside the model’s scope, along with the inputs to and outputs from the overall process, 2.One or more functional decomposition diagram showing how the overall process is made up of contributing processes at lower levels (a “vertical view”), 3.One or more process flow diagrams showing how the outputs of one process serve as the inputs to other process (a “horizontal view”). The process flow may be cross-functional or within a single function, 4.One or more business process model diagrams, each depicting the inputs, outputs, start and end events, component activities, roles, and metrics of a single process, 5.The business definition of each process, and 6.The value chain analysis of the process, identifying relationships to data, organizations, roles, and systems." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"A detailed workflow diagram that expands upon a process map by including detailed descriptions of subprocesses, activities, and tasks including all input, output, decisions, and exceptions, as well as measurements of the resources consumed (such as time, FTEs, material, capital, systems, etc.) during the execution of the process. Supports analysis via drill-down examination and can provide the metrics necessary for use by software capable of process simulation and what-if scenario testing of alternative variables." (Carl F Lehmann, "Strategy and Business Process Management", 2012)

[Process Modeling and Analysis:] "The tools and techniques used to (1) map a workflow diagram illustrating the activities and tasks associated with a business process; (2) add complete detail necessary to identify and measure all the resources consumed during the execution of the processes; (3) measure performance outcomes; (4) simulate changes to activities, tasks, sequences, resources, assumptions, and so on using what-if scenarios to test and recalculate performance outcomes; (5) conclude the best combination of adjustments or changes necessary to optimize performance outcome of the process." (Carl F Lehmann, "Strategy and Business Process Management", 2012)

"A model showing the processes carried out by a system and the data interfaces between those processes; same as a data flow model." (James Robertson et al, "Complete Systems Analysis: The Workbook, the Textbook, the Answers", 2013)

🚧Project Management: Business Case (Definitions)

"The business reason or value for undertaking the project." (Timothy J  Kloppenborg et al, "Project Leadership", 2003)

"A “needs assessment” or justification to show why an endeavor is necessary to an organization." (Margaret Y Chu, "Blissful Data ", 2004)

"A business problem, situation, or opportunity that justifies the pursuit of a technology project." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling" 2nd Ed., 2005)

"[...] a formal document used to justify investments in new products, product enhancements, and marketing expenditures." (Steven Haines, "The Product Manager's Desk Reference", 2008)

"The process that documents the customer's justification for their investments in products and services." (Steven Haines, "The Product Manager's Desk Reference", 2008)

"A document that provides a justification for a business investment, often used in terms of technology investments. Business cases can be built by identifying financial (hard-dollar) benefits and intangible benefits, including mitigation of risk." (Janice M Roehl-Anderson, "IT Best Practices for Financial Managers", 2010)

"A structured format for organizing the reasons, benefits, and estimated costs for initiating a project or program." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"A written document that is used by managers to justify funding for a specific investment and also to provide the bridge between the initial plan and its execution." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

"An analysis of the benefits and costs of making a change to the way things are done." (Mike Clayton, "Brilliant Project Leader", 2012)

"The justification for a project or program, against which performance is compared throughout the life cycle. Typically, the business case contains costs, benefits, risks, and timescales." (Paul C Dinsmore et al, "Enterprise Project Governance", 2012)

"Framework for making decisions, explanation of benefits and costs, anticipated outcomes, and project factors associated with a performance improvement effort." (Joan C Dessinger, "Fundamentals of Performance Improvement" 3rd Ed, 2012)

"A documented economic feasibility study used to establish validity of the benefits to be delivered by a program." (Project Management Institute, "The Standard for Program Management" 3rd Ed., 2013)

"A documented economic feasibility study used to establish validity of the benefits of a selected component lacking sufficient definition and that is used as a basis for the authorization of further project management activities." (For Dummies, "PMP Certification All-in-One For Dummies, 2nd Ed.", 2013)

"Information necessary to enable approval, authorisation and policy-making bodies to assess a project proposal and reach a reasoned decision." (Chartered Institute of Building, "Code of Practice for Project Management for Construction and Development, 5th Ed", 2014)

"A written analysis of the financial, productivity, auditability, and other factors to justify the investment in software and hardware systems, implementation, and training." (Robert F Smallwood, "Information Governance: Concepts, Strategies, and Best Practices", 2014)

"A compilation of costs and benefits associated with a planned project or investment." (Gregory Lampshire, "The Data and Analytics Playbook", 2016)

"A business case captures the reasoning for initiating a project or task. The business case justifies the investment." (by Brian Johnson & Leon-Paul de Rouw, "Collaborative Business Design", 2017)

"A documented evaluation (pre-project) of the potential impact a problem or an opportunity has on the organization to determine if it is worthwhile investing the resources to correct the problem or take advantage of the opportunity for improvement. It captures the reason for initiating a potential project or program." (H James Harrington & William S Ruggles, "Project Management for Performance Improvement Teams", 2018)

"Documentation of the rationale for making a business investment, used both to support a business decision on whether to proceed with the investment and as an operational tool to support management of the investment through its full economic life cycle." (ISACA)

🚧Project Management: Project Charter (Definitions)

"A document issued by senior management that formally authorizes the existence of a project. Provides the project manager with the authority to apply organizational resources to project activities." (Timothy J  Kloppenborg et al, "Project Leadership", 2003)

"A document issued by an executive, project sponsor, or customer, announcing a project and delegating authority to the project manager." (Bonnie Biafore, "Successful Project Management: Applying Best Practices and Real-World Techniques with Microsoft® Project", 2011)

"A document issued by the project initiator or sponsor that formally authorizes the existence of c a project, and provides the project manager with the authority to apply organizational resources to project activities." (Cynthia Stackpole, "PMP® Certification All-in-One For Dummies®", 2011)

"A statement of objectives, scope, and stakeholders or participants in a project or program." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"A document officially announcing an approved project. Distributed by the project sponsor, the charter identifies the project manager and the extent of the project manager's authority." (Bonnie Biafore & Teresa Stover, "Your Project Management Coach: Best Practices for Managing Projects in the Real World", 2012)

"a project management document that defines a project scope, objectives, benefits, assumptions, etc. May also identify team assignments, project sponsor, time and cost estimates and constraints, and areas that are out of scope." (Bill Holtsnider & Brian D Jaffe, "IT Manager's Handbook" 3rd Ed., 2012)

"A document that formally authorizes a project to move forward. Having such a document reduces project cancellation risk due to lack of support or perceived value to the company. A charter documents the project's overall objectives and helps manage expectations of those involved." (Robert F Smallwood, "Information Governance: Concepts, Strategies, and Best Practices", 2014)

"The project charter is the document issued by the project initiator or sponsor that formally authorizes the existence of a project and provides the project manager with the authority to apply organizational resources to project activities. It documents the high-level information on the project and on the product, service, or result the project is intended to satisfy." (Cate McCoy & James L Haner, "CAPM Certified Associate in Project Management Practice Exams", 2018)

🚧Project Management: Work Package (Definitions)

 "A deliverable or project work component at the lowest level of each branch of the work breakdown structure. See also control account." (Cynthia Stackpole, "PMP® Certification All-in-One For Dummies®", 2011)

"A task that represents actual work that resources do; it appears at the lowest level of the WBS." (Bonnie Biafore, "Successful Project Management: Applying Best Practices and Real-World Techniques with Microsoft® Project", 2011)

"A defined chunk of work, usually contained within a single work stream." (Mike Clayton, "Brilliant Project Leader", 2012)

"The task defined at the lowest level of the work breakdown structure. The work package is a project component that's finite enough to be estimated, scheduled, assigned, tracked, and managed. Often synonymous with task." (Bonnie Biafore & Teresa Stover, "Your Project Management Coach: Best Practices for Managing Projects in the Real World", 2012)

"The work defined at the lowest level of the work breakdown structure for which cost and duration can be estimated and managed." (For Dummies, "PMP Certification All-in-One For Dummies, 2nd Ed.", 2013)

"A well-defined scope of work that terminates in a deliverable product or completion of a service." (Christopher Carson et al, "CPM Scheduling for Construction: Best Practices and Guidelines", 2014)

"Set of tasks identified in order to reach one or several goals for business lines. A work package can be part of a project or a program." (Gilbert Raymond & Philippe Desfray, "Modeling Enterprise Architecture with TOGAF", 2014)

"The lowest level of the WBS; it is assigned a unique identifier." (Cate McCoy & James L Haner, "CAPM Certified Associate in Project Management Practice Exams", 2018)

"A document used to agree and record the business analysis work to be carried out, the boundaries, activities and outputs/deliverables." (Christina Lovelock & Debra Paul, "Delivering Business Analysis: The BA Service handbook", 2019)

"A document that identifies the work the assigned resources are to perform and any specifications associated with the work. This can range from a simple to-do list to a full page of notes and supporting documents such as specifications, blueprints, and guidelines. Also known as the WBS dictionary." (Bonnie Biafore & Teresa Stover, "Your Project Management Coach: Best Practices for Managing Projects in the Real World", 2012)

🚧Project Management: Project Plan (Definitions)

"A formal, approved document used to guide both project execution and project control." (Timothy J  Kloppenborg et al, "Project Leadership", 2003)

"A document that identifies project tasks, and describes how an organization intends to perform and control these tasks. The plan typically describes the tasks, the schedule, the production and management processes, the resources required, organization and responsibilities of the participants, and potential risks. For large projects, project plan(s) are usually split into several separate plans covering development, configuration management, quality assurance, risk management, and so forth." (Richard D Stutzke, "Estimating Software-Intensive Systems: Projects, Products, and Processes", 2005)

"A plan that provides the basis for performing and controlling the project’s activities, which addresses the commitments to the project’s customer." (Sandy Shrum et al, "CMMI: Guidelines for Process Integration and Product Improvement, Second Edition", 2006)

"The project plan consists of one or several planning documents (e.g., work breakdown structure, schedule, resource planning) that define the project scope and essential project attributes. It may also consist of a directory structure with different files. The project plan is the basis for project control. If the project plan consists of several planning documents, care must be taken that, in sum, the individual documents represent a conclusive, coherent whole." (Lars Dittmann et al, "Automotive SPICE in Practice", 2008)

"A document that describes a project and the plan for completing it and achieving its objectives. The project plan guides the execution and control of the project." (Bonnie Biafore, "Successful Project Management: Applying Best Practices and Real-World Techniques with Microsoft Project", 2011)

"Documentation of a project's projected activities including timing, resource assignments, assumptions, constraints, costs, etc." (Bill Holtsnider & Brian D Jaffe, "IT Manager's Handbook" 3rd Ed., 2012)

"A formal, approved document used to guide both project execution and project control. The primary uses of the project plan are to document planning assumptions and decisions, facilitate communication among stakeholders, and document approved scope, cost, and schedule baselines. A project plan may be summary or detailed." (Peter Oakander et al, "CPM Scheduling for Construction: Best Practices and Guidelines", 2014)

"Includes the project charter and project schedule and a delineation of all project team members and their roles and responsibilities." (Robert F Smallwood, "Information Governance: Concepts, Strategies, and Best Practices", 2014)

"Detailed proposal, that describes activities and resources needed to achieve an objective" (ITIL)

🧭Business Intelligence: Troubleshooting (Part II: Approaching a Query)

Business Intelligence Series

Introduction

You received a (long) query for troubleshooting, reviewing, conversion or any similar tasks. In addition, you don’t know much about the underlying table structure or business logic. So, what do you do then? For sure two things are intuitively clear: you don’t need to panic and, understanding the query may help you in your task. Understanding the query, it seems such a simple statement, though there is more to it. Here are some points on how to approach a query.

State your problem
 

“A problem well stated is a problem half solved” (Charles F. Kettering). Before performing any work, check what’s requested from you, whether you are having the information required for the task(s) ahead, for example documentation, valid examples, all code, etc. If something is missing, don’t hesitate to request all the information you need. While waiting for information, you can continue with next steps. As we don’t live in a perfect world, there will be also cases in which you’ll have to fill the gaps by yourself by performing additional research/work. When troubleshooting is important to understand what’s wrong and, when possible, have data against which to compare query’s output.

Save the work

Even if you are having a copy of the query somewhere on the server, save the previous version of the query and, when possible, use versioning. It might seem a redundant task, however the fact is that you never know when you need to refer to it and, as you’ll see next, it can/should be used as a baseline for validating the changes. In case you haven’t saved the query, check whether your RDBMS is tracking metadata about the queries run and, if the metadata were not reset in the meantime, you might be lucky enough to find a copy of your query.

I found that is important to save the daily work, the various analysis performed in order to understand a query, the various versions and even the data used for testing. All this work could help you letter to review what you made, the steps you missed, you can reuse one of the queries for further work, etc.

Break down

When the query is too complex, it could be useful to break the query into chunks that could be run and understood in isolation. Typically such chunks derive from query’s structure (e.g. inline queries, subqueries derived from unions). I found that often, focusing only a chunk of a query help isolating issues.

Restructure

Many programmers still write queries using the old non-ANSI joining syntax in which the join constraints appear in the WHERE clause, making the understanding and troubleshooting of a query more difficult. Often I found myself in the position of transforming first a query to ANSI SQL syntax, before performing further work on it. It’s actually a good occasion to gain a first understanding of query’s structure, but I’d prefer not to do it so often. In addition, during restructure phase it makes sense to differentiate between the join and filter constraints, this helping isolating the issue(s).

Check cardinalities

Wrong join constraints lead to duplicates or fewer records than expected, such differences being difficult to track when the variances in the numbers of records are quite small. Even if RDBMS come in developers’ help by providing metadata about the join relations, the columns and predicates participating in a join are not always so easy to identify. Therefore, in order to address this issue, it’s needed to check the constraints between any two tables between participating in a join. Sometimes, when the query is based on the table with the lowest level of detail, it can be enough to check the variations of the number of records.

Check filter constraints

Filter constraints are maybe more difficult to identify, especially when is needed to reengineer the logic built in applications. Many of the filter constraints are logical, though when you have no documentation about the schemas, is like rambling in the dark, having to check real examples and identify the various values and the impact they have on the behavior of your report.

Validate changes
 

So, you made the changes, everything looks perfect. Is it so? Often your intuition might tell you that the logic of a query is correct, though as software is not based on magic, at least not all the time, check some of the records to assure that the data are rendered as expected, check totals, compare the current with previous version, identify variations, etc. You don’t need to use all the technique you know, but to choose the best and minimal set of tools that allows you to validate the query.

Perform refactoring
    

Refactoring, the way to (continuous) code improvement, should become part of each developer’s philosophy about programming. A query, as any other piece of code, is rarely perfect as technical and factual knowledge is relative, features get deprecated and new techniques are introduced. On the other side, there is an old saying in IT – don’t change something that’s already working, so, there should be kept a balance between the two – the apparent and needed for change.

Document
    

I hope it’s not the case to stress the importance of documentation. From versioning to logic description, it’s a good practice to document the important parts of your work, especially the parts that will facilitate later work.

Previous Post <<||>>  Next Post

🧭Business Intelligence: Enterprise Reporting (Part VII: A Reporting Guy’s Issues)

Business Intelligence Series

Introduction

For more than 6 years, between many other tasks (software development, data migrations, support), I was also the "reporting guy", taking care of ad-hoc reporting requirements, building a data warehouse and a reporting solution for the customer I worked for. 99% of the reports were based on the two ERP systems (Oracle e-Business Suite & IFS-iV) the customer had in place during this time, fact that helped me learn a lot about the data architecture of such systems, about processes, data, data quality and many other issues related to data, how to do and how not do things. In this post I just want to highlight some of the issues I was confronted with, and I don’t intend to point the finger at anybody, so I apologize if anybody is offended!

The Lack of Knowledge about the Business

Even if it’s hard to believe, the main issue was revolving around the lack of relevant documentation on applications, especially on database models and processes, or, even if there were such documents, they were not updated and the value of true of the information contained were supposed to be always checked against the data. Of course, there are always knowledge workers from whom valuable information could be elicited though they were not always available and many of them are highly specialized in their field. Therefore, one needs to interview multiple users to build a close to complete picture, and even then, one must check the newly acquired information against the data! From time to time, one may even find out that the newly acquired information doesn’t entirely match the reality, that there are always exceptions and (business) rules forgotten or not known. 

Sometimes, it’s easier to derive knowledge directly from the data, table structure and other developers’ experience (e.g. blogs, books, forums) rather than hunting down the knowledge workers. Things aren't that bad, despite the reengineering part, in the end one manages to get the job done, though it takes more time. Sometimes it took even 2-3 more time to accomplish a task, time for which one could found better use. However, in time, accumulating more experience, I become proactive by exploring (and mapping) the unknown "territories" in the breaks between tasks, a fact that allowed me to easier fulfill users’ reporting requests.

Oracle e-Business Suite  

During the past 3 years I have been supporting mainly Oracle e-Business Suite (EBS) users with reports and knowledge about the system, and therefore most of the issues were related to it. In addition to its metadata system implemented in system table structure, Oracle tried to build an external metadata system for its ERP system, namely Metalink (I think it was replaced last year), though there were/are still many gaps in documentation. It’s true that many such gaps derive from the customizations performed when implementing the ERP system, though I would estimate that they qualify only for 20% of the gaps and refer mainly to the Flex Fields (customer-defined fields) used for the various purposes. 

A second important issue was related to the Oracle database engine, were several bugs not patched that didn’t allowed me to use SQL ANSI 92 syntax for linking more than 6-7 tables to a parent table, fact that made me abuse of inline views to solve this issue; even if Oracle had for long a patch to address this, it wasn’t deployed by admins, maybe from well supported reasons. A third issue was related to the different naming conventions used for the same attributes from the source system, mainly a result of the fact that solutions brought from other bought vendors were integrated with a minimum of changes. A fourth issue is related to the poor UI and navigation, basic if we consider the advances made in web technologies during the past years. Conversely, given the complexity of an ERP system, it’s challenging to change the UI at this scale.

 Self-Service BI

There's the belief that everybody can write a query and/or of an ad-hoc report. It’s true that writing a query is a simple task and in theory anybody could learn to it without much of effort, though there are other aspects related to Software Engineering and Project Management, respectively related to a data professional's experience than need to be considered. There are aspects like choosing the right data source, right attributes and level of detail, design the query or solution for the best performance (eventually building an index or using database objects that allow better performance) and reuse, use adequate business rules (e.g. ignoring inactive records or special business cases), synchronize the logic with other reports otherwise two people will show the management distinct numbers, mitigate the Data Quality and Deliverables Quality issues, identify the gaps between reports, etc.

In addition, having users create personal solutions instead of using a more standardized approach is quite risky because the result is a web of such siloed solutions over which there's no control from a strategic and/or data security point of view. Conversely, the users need the possibility to analyze the data by themselves (aka self-service BI), to join data coming from multiple sources. Therefore, special focus should be given also to such requirements, though once their reporting needs have been stabilized, they should be moved, if possible, to a more standardized solution.

When multiple developers are approaching reporting requirements they should work as a team and share knowledge not only on the legacy system but also on users’ requirements, techniques used and best practices. Especially when they are dispersed all over the globe, I know it’s difficult to bring cohesion in a team, make people produce deliverables as if they were written by the same person, though not so impossible to achieve with a little effort from all the parties involved. 

Why I’m mentioning this? The problem is that the more variability is introduced in deliverables, greater the risk is to have the quality of deliverables questioned, in time leading to users not adopting a system or preferring to use one resource in the detriment of another. Moreover, must be considered also the effort needed to find the gaps between reports, to modify deliverables to expectations, etc. From this perspective is always a good idea to document at least at minimum all deliverables, detailing the scope and particularities of the respective request. I know that many believe that code is self-explanatory and needs no additional documentation, though when the basic needed documentation is not available, it's occasionally challenging to intuit the context and identify the problem, respectively why a technique or a certain level of detail was preferred, or why some constraints were used. 

Outsourcing  

Outsourcing is a hot topic these days, when in the context of the current economic crisis organizations are forced to reduce the headcount and cut costs, and thus this has inevitably touched also the reporting area. Outsourcing makes sense when the interfaces between service providers and the customers are well designed and implemented. Beyond the many benefits and issues outsourcing approaches come with, people have to consider that for a developer to create a report is needed not only knowledge about the legacy systems and tools used to extract, transform and prepare the data, but also knowledge about the business itself, about users expectations and organization’s culture, the latter two points being difficult to experience in a disconnected and distributed environment. 

Of course, even if delivering the same result and quality is possible as if the developers were onsite, in the end outsourcing implies additional iterations and overwork, the users need to be trained to specify the reporting requirements adequately or a special resource needs to be available to translate the requirements between the parties involved, lot of back-and-forth communication and all the other issues deriving from it. 

Outsourcing makes sense from a reporting perspective, though it might take time to become efficient. Anyway, the decisions for this approach are usually taken at upper management level. From a reporting guy's perspective, if I consider the amount of additional effort and time spent to deliver comparable quality, I will say "No" to an outsourcing model when the time used to build something is just shifted in managing the communication with the outsourcer, writing emails after emails for issues that could have been solved in a 10-minute meeting. Probably the time and money can be invested in other resources that better enable the process. 

Previous Post <<||>> Next Post

Written: Mar-2010, Last Reviewed: Apr-2024

🏗️Software Engineering: Documentation (Just the Quotes)

"Amid a wash of paper, a small number of documents become the critical pivots around which every project's management revolves. These are the manager's chief personal tools." (William Bengough, "Scene in the old Congressional Library", 1897)

"Notice that even the data is commented. One of the most effective ways to document a program is simply to describe the data layout in detail. If you can specify for each important variable what values it can assume and how it gets changed, you have gone a long way to describing the program. [...] Document your data layouts." (Brian W Kernighan & Phillip J Plauger, "The Elements of Programming Style", 1974)

"The best documentation for a computer program is a clean structure. It also helps if the code is well formatted, with good mnemonic identifiers and labels (if any are needed), and a smattering of enlightening comments. Flowcharts and program descriptions are of secondary importance; the only reliable documentation of a computer program is the code itself. The reason is simple -whenever there are multiple representations of a program, the chance for discrepancy exists. If the code is in error, artistic flowcharts and detailed comments are to no avail. Only by reading the code can the programmer know for sure what the program does." (Brian W Kernighan & Phillip J Plauger, "The Elements of Programming Style", 1974)

"Writing a computer program eventually boils down to writing a sequence of statements in the language at hand. How each of those statements is expressed determines in large measure the intelligibility of the whole; no amount of commenting, formatting, or supplementary documentation can entirely replace well expressed statements. After all, they determine what the program actually does." (Brian W Kernighan & Phillip J Plauger, "The Elements of Programming Style", 1974)

"There is nothing in the programming field more despicable than an undocumented program." (Edward Yourdon, "Techniques of program structure and design", 1975)

"The representation of knowledge in symbolic form is a matter that has pre-occupied the world of documentation since its origin. The problem is now relevant in many situations other than documents and indexes. The structure of records and files in databases: data structures in computer programming; the syntactic and semantic structure of natural language; knowledge representation in artificial intelligence; models of human memory: in all these fields it is necessary to decide how knowledge may be represented so that the representations may be manipulated." (Brian C Vickery, "Concepts of Documentation", 1978)

"Even though it is better if the system can be used without documentation, it may be necessary to provide help and documentation. Any such information should be easy to search, focused on the user's task, list concrete steps to be carried out, and not be too large." (Jakob Nielsen, "Usability Engineering", 1994)

"The following two statements are usually both true: 

There's not enough documentation. 

There's too much documentation." (Larry Wall, [Usenet article], 1997)

"But code as a design document does have its limits. It can overwhelm the reader with detail. Although its behavior is unambiguous, that doesn’t mean it is obvious. And the meaning behind a behavior can be hard to convey. […] A document shouldn’t try to do what the code already does well. The code already supplies the detail. It is an exact specification of program behavior. Other documents need to illuminate meaning, to give insight into large-scale structures, and to focus attention on core elements. Documents can clarify design intent when the programming language does not support a straightforward implementation of a concept. Written documents should complement the code and the talking." (Eric Evans, "Domain-Driven Design: Tackling complexity in the heart of software", 2003)

"Good code is its own best documentation." (Steve McConnell, "Code Complete", 2004)

"Documentation is a love letter that you write to your future self." (Damian Conway, "Perl Best Practices", 2005)

"Developing fewer features allows you to conserve development resources and spend more time refining those features that users really need. Fewer features mean fewer things to confuse users, less risk of user errors, less description and documentation, and therefore simpler Help content. Removing any one feature automatically increases the usability of the remaining ones." (Jakob Nielsen, "Prioritizing Web Usability", 2006)

"Any comment that forces you to look in another module for the meaning of that comment has failed to communicate to you and is not worth the bits it consumes." (Robert C Martin, "Clean Code: A Handbook of Agile Software Craftsmanship", 2008) 

"Features have a specification cost, a design cost, and a development cost. There is a testing cost and a reliability cost. […] Features have a documentation cost. Every feature adds pages to the manual increasing training costs." (Douglas Crockford, "JavaScript: The Good Parts: The Good Parts", 2008)

Software is usually accompanied by documentation in the form of big fat scary manuals that nobody ever reads. (Dave Barry, "Dave Barry in Cyberspace", 2010)

"In addition to developing the proper culture, invest in your testing infrastructure by developing linters, documentation, or other assistance that makes it more difficult to write bad tests." (Titus Winters, "Software Engineering at Google: Lessons Learned from Programming Over Time", 2020)

"Documentation is a practice concerned with all the processes involved in transferring documents from sources to users." (Brian C Vickery)

"Documentation is not understanding, process is not discipline, formality is not skill." (Jim Highsmith)

"The guy who knows about computers is the last person you want to have creating documentation for people who don't understand computers." (Adam Osborne)

"This is generally true: any sizeable piece of program, or even a complete program package, is only a useful tool that can be used in a reliable fashion, provided that the documentation pertinent for the user is much shorter than the program text. If any machine or system requires a very thick manual, its usefulness becomes for that very circumstance subject to doubt!" (Edsger W. Dijkstra, "On the reliability of programs")