🧭Business Intelligence: Data Culture (Part I: Generative AI - No Silver Bullet)

Business Intelligence Series

Talking about holy grails in Data Analytics, another topic of major importance for an organization’s "infrastructure" is data culture, that can be defined as the collective beliefs, values, behaviors, and practices of an organization’s employees in harnessing the value of data for decision-making, operations, or insight. Rooted in data literacy, data culture is an extension of an organization’s culture in respect to data that acts as enabler in harnessing the value of data. It’s about thinking critically about data and how data is used to create value. 

The current topic was suggested by PowerBI.tips’s webcast from today [3] and is based on Brent Dykes’ article from Forbes ‘Why AI Isn’t Going to Solve All Your Data Culture Problems’ [1]. Dykes’ starting point for the discussion is Wavestone's annual data executive survey based on which the number of companies that reported they had "created a data-driven organization" rose sharply from 23.9 percent in 2023 to 48.1 percent in 2024 [2]. The report’s authors concluded that the result is driven by the adoption of Generative AI, the capabilities of OpenAI-like tools to generate context-dependent meaningful text, images, and other content in response to prompts. 

I agree with Dykes that AI technologies can’t be a silver bullet for an organization data culture given that AI either replaces people’s behaviors or augments existing ones, being thus a substitute and not a cure [1]. Even for a disruptive technology like Generative AI, it’s impossible to change so much employees’ mindset in a so short period of time. Typically, a data culture matures over years with sustained effort. Therefore, the argument that the increase is due to respondent’s false perception is more than plausible. There’s indeed a big difference between thinking about an organization as being data-driven and being data-driven. 

The three questions-based evaluation considered in the article addresses this difference, thinking vs. being. Changes in data culture don’t occur just because some people or metrics say so, but when people change their mental models based on data, when the interpersonal relations change, when the whole dynamics within the organization changes (positively). If people continue the same behavior and practices, then there are high chances that no change occurred besides the Brownian movement in a confined space of employees, that’s just chaotic motion.  

Indeed, a data culture should encourage the discovery, exploration, collaboration, discussions [1] respectively knowledge sharing and make people more receptive and responsive about environmental or circumstance changes. However, just involving leadership and having things prioritized and funded is not enough, no matter how powerful the drive. These can act as enablers, though more important is to awaken and guide people’s interest, working on people’s motivation and supporting the learning process through mentoring. No amount of brute force can make a mind move and evolve freely unless the mind is driven by an inborn curiosity!

Driving a self-driving car doesn’t make one a better driver. Technology should challenge people and expand their understanding of how data can be used in different contexts rather than give solutions based on a mass of texts available as input. This is how people grow meaningfully and how an organization’s culture expands. Readily available answers make people become dull and dependent on technology, which in the long-term can create more problems. Technology can solve problems when used creatively, when problems and their context are properly understood, and the solutions customized accordingly.

Unfortunately, for many organizations data culture will be just a topic to philosophy about. Data culture implies a change of mindset, perception, mental models, behavior, and practices based on data and not only consulting the data to confirm one’s biases on how the business operates!

Previous Post <<||>> Next Post

Resources:
[1] Forbes (2024) Why AI Isn’t Going To Solve All Your Data Culture Problems, by Brent Dykes (link)
[2] Wavestone (2024) 2024 Data and AI Leadership Executive Survey (link)
[3] Power BI tips (2024) Ep.299: AI & Data Culture Problems (link)

♟️Strategic Management: Operations (Just the Quotes)

"Strategy, or the art of properly directing masses upon the theater of war, either for defense or for invasion. […] Strategy is the art of making war upon the map, and comprehends the whole theater of operations. Grand Tactics is the art of posting troops upon the battle-field according to the accidents of the ground, of bringing them into action, and the art of fighting upon the ground, in contradistinction to planning upon a map. Its operations may extend over a field of ten or twelve miles in extent. Logistics comprises the means and arrangements which work out the plans of strategy and tactics. Strategy decides where to act; logistics brings the troops to this point; grand tactics decides the manner of execution and the employment of the troops." (Antoine-Henri Jomini, "The Art of War", 1838)

"A policy therefore might be likened to strategy, the broad, overall, long term conception which gives direction and purpose to the tactics of immediately daily operations and decisions." (Lawrence K. Frank, "National Policy for the Family", 1948)

"Essential to organization planning, then, is the search for an ideal form of organization to reflect the basic goals of the enterprise. This entails not only charting the main lines of organization and reflecting the organizational philosophy of the enterprise leaders (e.g., shall authority be as centralized as possible, or should the company try to break its operations down into semiautonomous product or territorial divisions?), but also a sketching out of authority relationships throughout the structure." (Harold Koontz & Cyril O Donnell, "Principles of Management", 1955)

"The concern of OR with finding an optimum decision, policy, or design is one of its essential characteristics. It does not seek merely to define a better solution to a problem than the one in use; it seeks the best solution... [It] can be characterized as the application of scientific methods, techniques, and tools to problems involving the operations of systems so as to provide those in control of the operations with optimum solutions to the problems." (C West Churchman et al, "Introduction to Operations Research", 1957)

"The essential task of management is to arrange organizational conditions and methods of operations so that people can achieve their own goals best by directing their own efforts toward organizational objectives." (Douglas McGregor, "The Human Side of Enterprise", 1960)

"Now we are looking for another basic outlook on the world - the world as organization. Such a conception - if it can be substantiated - would indeed change the basic categories upon which scientific thought rests, and profoundly influence practical attitudes. This trend is marked by the emergence of a bundle of new disciplines such as cybernetics, information theory, general system theory, theories of games, of decisions, of queuing and others; in practical applications, systems analysis, systems engineering, operations research, etc. They are different in basic assumptions, mathematical techniques and aims, and they are often unsatisfactory and sometimes contradictory. They agree, however, in being concerned, in one way or another, with ‘systems’, ‘wholes’ or ‘organizations’; and in their totality, they herald a new approach." (Ludwig von Bertalanffy, "General System Theory", 1968)

"The dogma of delegation is simple - the Sixth Truth of Management again: either the delegatee is capable of running the operation successfully by himself or he isn't. This handy formula relieves the top executive of any responsibility except that of finding, supervising, and (at the appropriate time) moving the men who are doing all the work. He Can then truly manage by exception: he does not get worked up over operations that are going well, but concentrates on the plague spots, where everything, including the management, is going badly." (Robert Heller, "The Naked Manager: Games Executives Play", 1972)

"Perhaps the fault [for the poor implementation record for models] lies in the origins of managerial model-making - the translation of methods and principles of the physical sciences into wartime operations research. [...] If hypothesis, data, and analysis lead to proof and new knowledge in science, shouldn’t similar processes lead to change in organizations? The answer is obvious-NO! Organizational changes (or decisions or policies) do not instantly pow from evidence, deductive logic, and mathematical optimization." (Edward B Roberts, "Interface", 1977)

"Management science [operations research] also involves the philosophy of approaching a problem in a logical manner (i.e., a scientific approach). The logical, consistent, and systematic approach to problem solving can be as useful (and valuable) as the knowledge of the mechanics of the mathematical techniques themselves." (Bernard W. Taylor III, "Introduction to Management Science, 1986)

"Strategy means abstract thinking and planning, as opposed to tactics, which are the individual operations used to implement strategy. Tactics are specific; strategy is general. Tactics tend to be immediate, strategy long-term." (Bruce Pandolfini, "Weapons of Chess: An omnibus of chess strategy", 1989)

"At the heart of reengineering is the notion of discontinuous thinking - of recognizing and breaking away from the outdated rules and fundamental assumptions that underlie operations. Unless we change these rules, we are merely rearranging the deck chairs on the Titanic. We cannot achieve breakthroughs in performance by cutting fat or automating existing processes. Rather, we must challenge old assumptions and shed the old rules that made the business underperform in the first place." (Michael M Hammer, "Reengineering Work: Don't Automate, Obliterate", Magazine, 1990)

"Even with simple and usable models, most organizations will need to upgrade their analytical skills and literacy. Managers must come to view analytics as central to solving problems and identifying opportunities - to make it part of the fabric of daily operations." (Dominic Barton & David Court, "Making Advanced Analytics Work for You", 2012)

"DevOps recognizes the importance of culture. The acronym CAMS (culture, automation, measurement, and sharing) is used to encapsulate its key themes. Culture is acknowledged as all important in making development and IT operations work together effectively. But what is culture in this context? It is not so much about an informal dress code, flexible hours, or a free in-house cafeteria as it is about how decisions are taken, norms of behavior, protocols of communication, and the ways of navigating hierarchy and bureaucracy to get things done." (Sriram Narayan, "Agile IT Organization Design: For Digital Transformation and Continuous Delivery", 2015)

♜Strategic Management: Resilience (Definitions)

"The ability to recover from challenges or to overcome obstacles. In a social-ecological context this refers to the innovation capacity of the organization to successfully address societal and environmental challenges." (Rick Edgeman & Jacob Eskildsen, "Social-Ecological Innovation", 2014)

"The quality of being able to absorb systemic 'shocks' without being destroyed even if recovery produces an altered state to that of the status quo ante." (Philip Cooke, "Regional Innovation Systems in Centralised States: Challenges, Chances, and Crossovers", 2015)

"The ability of an organization to quickly adapt to disruptions while maintaining continuous business operations and safeguarding people, assets, and overall brand equity. Business resilience goes a step beyond disaster recovery, by offering post-disaster strategies to avoid costly downtime, shore up vulnerabilities, and maintain business operations in the face of additional, unexpected breaches." (William Stallings, "Effective Cybersecurity: A Guide to Using Best Practices and Standards", 2018)

"A capability to anticipate, prepare for, respond to, and recover from significant multi-hazard threats with minimum damage to social well-being, the economy, and the environment." (Carolyn N Stevenson, "Addressing the Sustainable Development Goals Through Environmental Education", 2019)

"The ability of a project to readily resume from unexpected events, threats or actions." (Phil Crosby, "Shaping Mega-Science Projects and Practical Steps for Success", 2019)

"The ability of an infrastructure to resist, respond and overcome adverse events" (Konstantinos Apostolou et al, "Business Continuity of Critical Infrastructures for Safety and Security Incidents", 2020)

"The capacity to respond to, adapt and learn from stressors and changing conditions." (Naomi Borg & Nader Naderpajouh, "Strategies for Business Sustainability in a Collaborative Economy", 2020)

"The word resilience refers to the ability to overcome critical moments and adapt after experiencing some unusual and unexpected situation. It also indicates return to normal." (José G Vargas-Hernández, "Urban Socio-Ecosystems Green Resilience", 2021)

"Operational resilience is a set of techniques that allow people, processes and informational systems to adapt to changing patterns. It is the ability to alter operations in the face of changing business conditions. Operationally resilient enterprises have the organizational competencies to ramp up or slow down operations in a way that provides a competitive edge and enables quick and local process modification." (Gartner)

[Operational resilience:] "The ability of an organization to absorb the impact of any unexpected event without failing to deliver on its brand promise." (Forrester)

[Business resilience:] "The ability to thrive in the face of unpredictable events and circumstances without deteriorating customer experience or sacrificing the long-term viability of the company." (Forrester)

♜Strategic Management: Decision-Making (Definitions)

[decision-making:] "The process of making choices in a project team environment. Several types of decision-making are useful in projects: consensus, leader-imposed, delegated, voting, and scoring models." (Timothy J  Kloppenborg et al, "Project Leadership", 2003)

[semistructured decisions:] "Decisions in which only some of the phases are structured; require a combination of standard solution procedures and individual judgment." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

[strategic decision:] "refers to a decision that exhibits the following characteristics: it is made in a situation of uncertainty, of incomplete information, in a complex environment, variable/mutating environment (as opposed to 'all things being otherwise equal'); it is not recurrent, therefore the decision maker is relatively deprived; it may have far-reaching (favorable or adverse) consequences that could jeopardize the survivability of the enterprise; it is systemic (many elements with many relationships among them); the decision maker does not have experience-proven models (we cannot resort to 'turnkey' mechanisms). " (Humbert Lesca & Nicolas Lesca, "Weak Signals for Strategic Intelligence: Anticipation Tool for Managers", 2011)

[strategic decisions:] "Decisions for sustained enterprise success and business growth." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

[tactical decisions:] "Decisions ensuring that existing operations and processes are in alignment with business objectives and strategies." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

[decision-making processes:] "Management processes that define objectives, study alternatives, analyze available data, and reflect on intuitive beliefs. They interpret findings and compare alternates to form a conclusion or make a choice upon which the organization may act." (Carl F Lehmann, "Strategy and Business Process Management", 2012)

[microdecision:] "A small decision made many times by many workers at the front line of the organization. They usually have a significant impact on organizational performance due to their sheer volume." (Evan Stubbs, "Delivering Business Analytics: Practical Guidelines for Best Practice", 2013)

[decision-making:] "How decisions are made, based on what types of resources, information, and specific processes are available." (Jim Davis & Aiman Zeid, "Business Transformation: A Roadmap for Maximizing Organizational Insights", 2014)

[decision-making] "the process of making choices or reaching conclusions, especially on important political or business matters." (Ken Sylvester, "Negotiating in the Leadership Zone", 2015)

[tactical decisions:] "broader decision questions than operational ­decisions, semistructured in nature, some but not all information ­necessary to make the decision is available, primarily internally focused and made by middle-level managers." (Daniel J. Power & Ciara Heavin, "Data-Based Decision Making and Digital Transformation", 2018)

[operating or function-specific decisions:] "day-to-day, routine ­decisions with a concise decision question and a clear, well-defined, and structured algorithm to make a choice among alternatives." (Daniel J. Power & Ciara Heavin, "Data-Based Decision Making and Digital Transformation", 2018)

[strategic decisions:] "complex, nonroutine, unstructured decisions involving many different and connected parts. Some variables may not be well understood, often information required to make the decision may be unavailable, incomplete, and in some situations information may be known to be flawed or inaccurate. These decisions usually involve a high degree of uncertainty about outcomes. If implemented, strategic ­decisions often result in major changes in an organization." (Daniel J. Power & Ciara Heavin, "Data-Based Decision Making and Digital Transformation", 2018)

💎SQL Reloaded: Pulling the Strings of SQL Server VII (List of Values)

Introduction

    Lists are one of the basic structures in Mathematics, the term referring to an (ordered) set of elements separated by comma, space or any other delimiter (e.g. “:”, “;”). The elements of a list can be numbers, words, functions, or any other type of objects. In the world of databases, a list is typically formed out of the values of a given column or a given record, however it could span also a combination of rows and records, is such cases two delimiters being needed – one for column and one for row. From here comes probably the denomination of list of values. In a more general accept a list of values could be regarded as a delimited/concatenated subset. Such lists are formed when needed to send the data between the layers of an application or applications, this type of encoding being quite natural. In fact, also the data in a database are stored in similar tabular delimited structure, more complex though.  

    An useful example in which the list of values are quite handy is the passing of multiple values within the parameter of stored procedure or function (see example). This supposes first building the list and then use the values in a dynamic build query (like in the before mentioned example) or by building a table on the fly. We can call the two operations composition, respectively decomposition of list of values.

Composition 

Composition, whether on vertical or horizontal is nothing but a concatenation in which the values alternate with one or more delimiters. Let’s reconsider the concatenation based on the values of a Person.AddressType AdventureWorks table. As the logic for concatenating for one or more attributes is the same, the below example concatenates a list based on a single attribute, namely AddressTypeID in SingleList, respectively two attributes, AddressTypeID and Name.

-- concatenation of values across a table 
;WITH CTE (AddressTypeID, Name, Ranking) 
AS (--preparing the data       
     SELECT AddressTypeID  
     , Name 
     , ROW_NUMBER () OVER(ORDER BY Name) Ranking 
     FROM Person.AddressType 
     -- WHERE ... 
) 
, DAT (SingleList, DoubleList, Ranking) 
AS ( -- concatenating the values 
     SELECT Cast(AddressTypeID as varchar(max)) SingleList 
     , Cast('('+ Cast(AddressTypeID as varchar(10)) + ',''' + Name + ''')' as varchar(max)) DoubleList 
     , Ranking 
     FROM CTE 
     WHERE Ranking = 1 
     UNION ALL 
     SELECT DAT.SingleList + ',' + Cast(CTE.AddressTypeID as varchar(20)) SingleList 
    , Cast(DAT.DoubleList + ', ('+ Cast(CTE.AddressTypeID as varchar(10)) + ',''' + CTE.Name + ''')' as varchar(max)) DoubleList 
    , CTE.Ranking  
     FROM CTE          
       JOIN DAT           
          ON CTE.Ranking = DAT.Ranking + 1       
)  

-- the lists 
SELECT SingleList 
, DoubleList 
FROM DAT 
WHERE Ranking = (SELECT MAX(Ranking) FROM DAT) 

 
 
   The second example is based on atypical delimiters, resembling to the structure built for a batch insert or table value constructor-based statement, and as we’ll see later, ideal to be used in a dynamically-built query

Decomposition

Decomposition follows the inverse path, though it’s much easier to exemplify. In fact it’s used the same technique introduced in the last example from the previous post belonging to the same cycle, Subparts of a String, in which a space was used as delimiter. Another example is the dbo.SplitList function which decomposes a string using a loop.

-- decomposition of a string to a table using CTE 
CREATE FUNCTION dbo.StringToTable( 
 @str varchar(500) 
,@Delimiter char(1)) 
RETURNS @Temp TABLE ( 
Id int NOT NULL 
,Value varchar(50)) 
AS 
BEGIN  
     ;WITH CTE (PrevString, Position, Word)  
     AS (  
     SELECT LTrim(RTrim( CASE  
           WHEN CharIndex(@Delimiter, @str)>;0 THEN Right(@str, Len(@str)-CharIndex(@Delimiter, @str))  
           ELSE ''  
      END)) PrevString  
     , 1 Position  
     , LTrim(RTrim(CASE  
           WHEN CharIndex(@Delimiter, @str)>0 THEN LEFT(@str, CharIndex(@Delimiter, @str)-1)  
           ELSE @str  
       END)) Word  
      UNION ALL  
      SELECT LTrim(RTrim(CASE  
            WHEN CharIndex(@Delimiter, PrevString)>0 THEN Right(PrevString, Len(PrevString)-CharIndex(@Delimiter, PrevString))  
             ELSE ''  
       END)) PrevString  
      , Position + 1 Position  
      , LTrim(RTrim(CASE  
           WHEN CharIndex(@Delimiter, PrevString)>0 THEN LEFT(PrevString, CharIndex(@Delimiter, PrevString)-1)  
          ELSE PrevString  
      END)) Word      FROM CTE  
     WHERE Len(PrevString)>0  
    )  
     INSERT @Temp(Id, Value) 
     SELECT Position  
     , Word      FROM CTE  
     OPTION (maxrecursion 100)  
     RETURN 
END    

Here are two examples based on the single list created above and another one based on alphabet:

-- decomposing a list
SELECT Id 
, value 
FROM dbo.StringToTable('6,1,2,3,4,5', ',')     


-- decomposing the "alphabet" 
SELECT Id 
, value 
FROM dbo.StringToTable('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', ',') 

    


   
Even if the function deals only with a delimiter, it could be used to decompose lists involving multiple delimiters, as long the list is adequately built:

-- decomposing double list 
SELECT Id 
, value 
, Left(value, CHARINDEX(',', value)-1) LeftValue 
, Right(value, len(value)-CHARINDEX(',', value)) RightValue 
FROM dbo.StringToTable('6,Archive;1,Billing;2,Home;3,Main Office;4,Primary;5,Shipping', ';')     

 
 The tables built thus from list of values can be further used in queries when needed to create a table on the fly. It would be interesting maybe to show that the composition and decomposition are inverse functions, however that’s out of scope, at least for current set of posts. 

💎SQL Reloaded: Pulling the Strings of SQL Server VIII (Insertions, Deletions and Replacements)

Until now, the operations with strings resumed to concatenation and its reverse operation(s) - extracting a substring or splitting a string into substrings. It was just the warm up! There are several other important operations that involve the internal manipulation of strings – insertion, deletion and replacement of a substring in a given string, operations performed using the Replace and Stuff functions.

Replace function, as its name denotes, replaces all occurrences of a specified string value with another. Several scenarios in which the function is quite useful: the replacement of delimiters, special characters, correcting misspelled words or any other chunks of text. Here are some basic simple examples, following to consider the before mentioned applications in other posts:

-- examples with replace 
DECLARE @str varchar(30) 
SET @str = 'this is a test string' 
SELECT replace(@str, ' ', ',') Example1 
, replace(@str, ' ', ' ') Example2 
, replace(@str, ' ', '') Example3 
, replace(@str, 'is', 'as') Example4  

Output:

Example1	Example2	Example3	Example4
this,is,a,test,string	this is a test string	thisisateststring	thas as a test string

When there are good chances that the searched string won’t appear in the “searched” string, and especially when additional logic is depending on the replacement, logic that could be included in the same expression with the replacement, then maybe it makes sense to check first if the searched character is present:

-- replacement with check 
DECLARE @str varchar(30) 
DECLARE @search varchar(30) 
DECLARE @replacememt varchar(30) 
SET @str = 'this is a test string' 
SET @search = 'this string' 
SET @replacememt = 'other string' 
SELECT CASE            
    WHEN CharIndex(@search, @str)>0 THEN Replace(@str, @search, @replacememt)             
    ELSE @str        
END result 

Unfortunately the function doesn’t have the flexibility of the homonym functions provided by the languages from the family of VB (VBScript, VB.NET), which allow to do the replacement starting with a given position, and/or for a given number of occurrences. This type of behavior could be obtained with a simple trick – splitting the string into two other strings, performing the replacement on the second string, and then concatenating the first string and the result of the replacement:

-- replacement starting with a given position 
DECLARE @str varchar(30) 
DECLARE @search varchar(30) 
DECLARE @replacememt varchar(30) 
DECLARE @start int 
SET @str = 'this is a test string' 
SET @search = 's' 
SET @replacememt = 'x' 
SET @start = 7 
SELECT Left(@str, @start-1) FirstPart 
, RIGHT(@str, Len(@str)-@start+1) SecondPart 
, CASE        
    WHEN @start <= LEN(@str) THEN Left(@str, @start-1) + Replace(RIGHT(@str, Len(@str)-@start+1), @search, @replacememt)        
     ELSE @str  
END Replacement 

Output:

FirstPart	SecondPart	Replacement
this i	s a test string	this ix a text xtring

The logic can be encapsulated in a function together with additional validation logic.

Stuff function inserts a string into another string starting with a given position and deleting a specified number of characters. Even if seldom used, the function it’s quite powerful allowing to insert a string in another, to remove a part of a string or more general, to replace a single occurrence of a string with another string, as can be seen from the below examples:
 

-- Stuff-based examples 
DECLARE @str varchar(30) 
SET @str = 'this is a test string' 
SELECT STUFF(@str, 6, 2, 'was ') Example1 
, STUFF(@str, 1, 0, 'and ') Example2 
, STUFF(@str, 1, 0, 'that') Example3 
, STUFF(@str, LEN(@str) + 1, 0, '!') Example4 

Output:

Example1	Example2	Example3	Example4
this was a test string	and this is a test string	thatthis is a test string	NULL

If in the first example is done a replacement of a text from a fix position, in the next examples are attempted insert on a first, middle respectively end position. As can be seen, the last example doesn’t work as expected, this because the insert position can’t go over the length of the target string. Actually, if the insert needs to be done at the beginning, respectively the end of a string, a concatenation can be much easier to use. A such example is the padding of strings with leading or trailing characters, typically in order to arrive to a given length. SQL Server doesn’t provide such a function, however the function is quite easy to build.
 

-- left/right padding 

DECLARE @str varchar(30) 
DECLARE @length int  
DECLARE @padchar varchar(1) 
SET @str = '12345'  
SET @length = 10 
SET @padchar = '0' 
SELECT @str StringToPad  
, CASE  
     WHEN LEN(@str)<@length THEN Replicate(@padchar, @length-LEN(@str)) + @str       
     ELSE @str  
END LeftPadding  
, CASE  
     WHEN LEN(@str)<@length THEN @str + Replicate(@padchar, @length-LEN(@str))      
     ELSE @str  
END RightPadding 

 
Output:

StringToPad	LeftPadding	RightPadding
12345	0000012345	1234500000

Note:
The queries work also in SQL databases in Microsoft Fabric.

Happy Coding!

💎🏭SQL Reloaded: Pulling the Strings of SQL Server III: (Concatenation)

Typically, a database in general, and a table in particular, that follows the normalization rules, is designed to have the columns contain the smallest semantic chunks of data, it could be a Street, a Zip Code, City, a person’s First or Last Name, but also a large chunk of text like a Description or a Comment. No matter how well designed is a database, there will always be the need to do various operations with strings, typically concatenation, extraction of subpart of a string, insertion or deletion of characters, rearangement of string’s characters, trimming, splitting it in substrings, or of getting various numeric values: length, position of a given text, number of not empty characters, on whether the text represents a valid numeric or date values, etc. In the following posts I will attempt to address the respective operations in the context of select statements, and let’s start with concatenation. 

Concatenation is the operation of joining two or more string values within an expression. In SQL Server the “+” operator is used for concatenation, and it could be used to concatenate two or more members. In order to concatenate two members from which one of them is a string, the second term needs to be explicitly converted to a string data type, otherwise an error will occur. For readability or post-processing purposes, the strings are concatenated using a delimiter in order to delimit the boundaries of the initial value, it could be used a space, a comma, a semicolon, a pipe, a tab or any other character that could be used typically in delimiting columns.  

-- concatenation of strings 
SELECT 'a string' + ' and ' + 'a second string' Example1  
, 'a string' + ',' + 'a second string' Example2  
, '1245' + '67890' Example3  
, '1245' + '.' + '67890' Example4 

The concatenation of string variables or columns functions based on the same principles: 

-- concatenating string variables 
DECLARE @string1 varchar(50) 
DECLARE @string2 varchar(50) 
DECLARE @string3 varchar(50) 

SET @string1 = 'this is a string'  
SET @string2 = 'this is another string'  

SELECT @string1 + ' and ' + @string2 Example1 
, @string1 + char(31) + @string2 Example2 
, @string1 + ', ' + @string2 Example3 
, @string1 + ' ' + @string3 Example4 
, @string1 + IsNull(@string3, '!') Example5 

 
 

Here’s another example based on the concatenation of columns coming from two joined tables from AdventureWorks database:
 

-- concatenating columns of joined tables 
SELECT PAD.AddressID 
, IsNull(PAD.AddressLine1, '')  
+ IsNull(', ' + PAD.AddressLine2, '') 
+ IsNull(', ' + PAD.City, '') 
+ IsNull(', ' + PAD.PostalCode, '') 
+ IsNull(', ' + PSP.Name, '') Address 
FROM Person.Address PAD 
      JOIN Person.StateProvince PSP 
         ON PAD.StateProvinceID = PSP.StateProvinceID 

 


As stressed in the previous post, the NULL values need to be adequately handled either by initializing values or by using the IsNull or COALESCE functions. The concatenation of strings combined with IsNull function could be used creatively in order to add a comma only when a value is not null, as in the above example.

There are scenarios in which is needed to concatenate the values belonging to the same column but from different records, for example concatenating the email values in order to send a single email to all the users in one single action. Before the introduction of common table expressions (CTE), wasn’t possible to concatenate the string values belonging to different records, at least not in a query, this functionality being achieved by using cursors or loops, or simply performed on client or intermediate layers. As I already gave an example on how to use cursor in order to loop through the values of a table and concatenate them (see “Cursors and Lists” post), I will focus on the use of loops and simple CTEs.

Loops are one of the basic functionality in programming languages, no matter of their complexity or type. Either if are talking about WHILE, FOR, foreach or do … until loops, the principles are the same: perform a list of actions until one or more conditions are met. In this case the actions performed is reduced to a set of concatenations based on the letters of the (English) alphabet:

-- concatenation within a loop 
DECLARE @list varchar(max) 
DECLARE @index int  
SET @list = '' 
SET @index = ASCII('a') WHILE (@index<ASCII('z')) 
BEGIN 
     SET @list = @list + ', ' + Char(@index) 
     SET @index = @index + 1 
END 
SELECT @list Result 

There is more work that needs to be performed in order to remove the leading comma from the output, but that’s a topic for the next post, when discussing about decomposition of strings.

CTEs are a little more complex to use than the loops, though the concatenation could be achieved across records and this in one query and not in procedural language as in the above example. In order delimit the two components of a CTE, I made use of a second CTE which simulates the existence of a given table:
 

-- concatenation across records 
;WITH Data(Column1, Ranking)  
AS  
( -- preparing test data 
       SELECT 'A' Column1, 0 Ranking  
       UNION ALL  
       SELECT 'B' Column1, 1 Ranking  
       UNION ALL  
       SELECT 'C' Column1, 2 Ranking 
) 
, Result(Column1, Ranking)  
AS 
(  -- performing the actual concatenation 
      SELECT Cast(Column1 as nvarchar(max)) Column1 , Ranking 

      FROM Data 

      WHERE Ranking = 0  
      UNION ALL 
      SELECT Cast(B.Column1 + ',' + A.Column1 as nvarchar(max)) Column1 , A.Ranking  
      FROM Data A  
         JOIN Result  B  
             ON A.Ranking - 1 = B.Ranking  
) SELECT Column1  
FROM Result  
WHERE Ranking IN (SELECT MAX(Ranking) FROM Result) 

  
The logic for doing a simple concatenation seems maybe complicated, though the volume of work is not so big if we ignore the first CTE. On the other side I introduced an “index” within the Ranking column, fact that allows processing easier the records. When dealing with the records coming from a table it’s probably much easier to use one of the ranking functions that suits best.

Notes:
The queries work also in SQL databases in Microsoft Fabric

Happy coding!

💎SQL Reloaded: Pulling the Strings of SQL Server I 7- Introduction

    The (character) string or simply the character data type, how is named by the MSDN documentation, is one of the primary data types available in a database and probably the most complex given the fact that it can encompass any combination of numeric, literal, date or any other primary data type. In fact it could include any type of chunk of text that could be written in any language as SQL Server supports Unicode and thus most of the (written) languages. In this post I’m not intending to make a complete anthology of strings, and neither to retake the whole volume of information available in MSDN or other important books. My intent is to look at strings from a slightly different perspective, considering the various functions involving strings and how they could be used in order to provide various functionality, in fact the cornerstone of everyday developer.

   A few things to remember:

1. there were mainly two types of non-unicode strings: the char (or character) of fixed length, respectively the varchar of variable length (varying character)

2. if initially both types of strings were having a maximum length of 8000 of characters, with SQL Server it’s possible to have a varchar with maximum storage size, declared as varchar(max).

3. if in the past there were some strict recommendations in what concerns the use of char or varchar, nowadays the varchar tends to be used almost everywhere, even for the strings of length 1.

4. talking about length, it denotes the number of chracters a string stores.

5. the trailing spaces, the spaces found at the right extremity of a string are typically ignored, while the leading spaces, the spaces found at the left extremity, are not ignored.

6. starting with SQL Server 2000, for the two character data types were introduced the corresponding unicode data types prefixed with n (national): nchar, respectively nvarchar.

7. given the fact that a unicode character needs more space to store the same non-unicode string, actually the number of bits doubles, the maximum length for an unicode string is only 4000.

8. there is also a non-unicode text, respectively ntext unicode data type, designed to store maximum length, though as it seems they could become soon deprecated, so might be a good idea to avoid it.

9. not-initialized variables, including strings, have the value NULL, referred also the NULL string, therefore it’s always a good idea to initialize your variables.

10. by empty string string is designated the string containing no character “’’”, and has the length 0.

11. there are several specific functions available for the creation, manipulation and conversion of strings from and to other data types.

12. not all of the aggregated functions work with string values (specifically the ones requesting a number value like SUM, AVG, STDV).

13. the operations performed on strings of different data types are generally not impacted by this aspect, though there are some exceptions.

14. there are several (basic) operations with strings, typically concatenation, extraction of subpart of a string, insertion, replacement or deletion of characters, rearrangement of string’s characters, trimming, splitting it in substrings (decomposition), etc.

15. there are several numeric values based on strings: length, position of a given text in a text, number of not empty characters, encoding of a character, on whether the text represents a valid numeric or date values, etc.

🛢DBMS: Cartesian Product (Definitions)

"All the possible combinations of the rows from each of the tables specified in a join. The number of rows in the Cartesian product is equal to the number of rows in the first table times the number of rows in the second table. Once the Cartesian product is formed, the rows that do not satisfy the join conditions are eliminated." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

[cross join:] "When all rows from one table are joined to all rows of the other table." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"All of the possible combinations of the rows from each of the tables involved in a join operation. The number of rows in a Cartesian product of two tables, for example, is equal to the number of rows in the first table multiplied by the number of rows in the second table. Cartesian products almost never provide useful information. Queries using the CROSS JOIN clause or queries without enough JOIN clauses create Cartesian products." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

[cartesian join:] "A set comprised of all the possible combinations from multiple constraints." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed., 2002)

"The set of all ordered pairs {a, b} where a is a member of set A and b is a member of set B. In database terms, a cartesian product joins all rows in Table1 with all rows in Table2. Thus if Table1 has the values {T_a1, T_b1} and Table2 has the values {T_a2, T_b2} then the cartesian product is {(T_a1, T_a2) (T_a1, T_b2) (T_b1, T_a2) (T_b1, T_b2)}.Cartesian products are useful for explanation, but when we see an operation which 'goes cartesian', we usually criticize the optimizer. " (Peter Gulutzan & Trudy Pelzer, "SQL Performance Tuning", 2002)

"A join between two tables where no join condition is specified, and as a result, every row in the first table is joined with every row in the second table." (Bob Bryla, "Oracle Database Foundations", 2004)

"A mathematical term describing a set of all the pairs that can be constructed from a given set. Statistically it is known as a combination, not a permutation. In SQL jargon, a Cartesian Product is also known as a cross join." (Gavin Powell, "Beginning Database Design", 2006)

"All of the possible combinations of the rows from each of the tables involved in a join operation." (S. Sumathi & S. Esakkirajan, "Fundamentals of Relational Database Management Systems", 2007)

[cross join:] "A join that performs a relational product (also known as the Cartesian product) of two tables." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"In data processing, given two or more populations, the Cartesian product is the set of all possible combinations, taking one value from each population. Usually a large and meaningless answer set for an incorrectly phrased query. SEE ALSO query from hell." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"The result of a Cartesian join, occurring when every row of one table is joined to every row of another table (or itself) without the use of join predicates." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures", 2012)

[cartesian join:] "A join in which one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets." (Oracle, "Database SQL Tuning Guide Glossary", 2013)

🌁Software Engineering: DevOps (Definitions)

"An application delivery philosophy that stresses communication, collaboration, and integration between software developers and their information technology (IT) counterparts in operations. DevOps is a response to the interdependence of software development and IT operations. It aims to help an organization rapidly produce software products and services." (Pierre Pureur & Murat Erder, "Continuous Architecture", 2015)

DevOps is an approach based on lean and agile principles in which business owners and the development, operations, and quality assurance departments collaborate to deliver software in a continuous manner that enables the business to more quickly seize market opportunities and reduce the time to include customer feedback. Indeed, enterprise (Sanjeev Sharma & Bernie Coyne, "DevOps For Dummies" 2nd Ed, 2015)

"Is a method for software development and management that integrates the development and deployment cycles to achieve a more agile, continuous evolution of software-based products and services" (Diego R López & Pedro A. Aranda, "Network Functions Virtualization: Going beyond the Carrier Cloud", 2015)

"DevOps is a mindset, a culture, and a set of technical practices. It provides communication, integration, automation, and close cooperation among all the people needed to plan, develop, test, deploy, release, and maintain a Solution." (Dean Leffingwell, "SAFe 4.5 Reference Guide: Scaled Agile Framework for Lean Enterprises" 2nd Ed., 2018)

"Short for development operations, an information technology environment in which development and operations are tightly tied together, yielding small incremental releases to gain user feedback." (O Sami Saydjari, "Engineering Trustworthy Systems: Get Cybersecurity Design Right the First Time", 2018)

"The practice of incorporating developers and members of operations and quality assurance (QA) staff into software development projects to align their incentives and enable frequent, efficient, and reliable releases of software products." (Shon Harris & Fernando Maymi, "CISSP All-in-One Exam Guide" 8th Ed., 2018)

"The tighter integration between the developers of applications and the IT department that tests and deploys them. DevOps is said to be the intersection of software engineering, quality assurance, and operations." (William Stallings, "Effective Cybersecurity: A Guide to Using Best Practices and Standards", 2018)

"A software engineering practice that aims at unifying software development (Dev) and software operation (Ops)." (Jun Bi et al, "Automatic Address Scheduling and Management for Broadband IP Networks", Emerging Automation Techniques for the Future Internet, 2019)

"Develop operations, or DevOps, is an agile methodology that merges the functions of software development and operations in the enterprise software development domain. This approach has been adopted in the networking world to facilitate a programmable approach to network operations. Often when applied to networking the term is changed to NetOps." (Patrick Moore, "Model-Centric Fulfillment Operations and Maintenance Automation", Emerging Automation Techniques for the Future Internet, 2019)

"Practices and technologies that promote tighter coupling of software development (Dev) and operations (Ops) - typically marked by more automation, continuous monitoring, shorter development cycles and higher deployment frequencies. A key driver for security policy automation. DevSecOps is a related term that refers to practices and technologies that aim to embed security in DevOps practices." (Myo Zarny et al, "Network Security Policy Automation: Enterprise Use Cases and Methodologies", 2019)

"Development and operations is an abbreviation for 'development' and 'operations'; is a software engineering methodology for managing software development (Dev) and technology operations (Ops). The main aim of DevOps is to enable automation and tracing for all phases of software implementation, from integration, testing, releasing to deployment and infrastructure management." (Antoine Trad & Damir Kalpić, "Using Applied Mathematical Models for Business Transformation", 2020)

"Development and operations (DevOps) has been adopted by prominent software and service companies (e.g., IBM) to support enhanced collaboration across the company and its value chain partners. In this way, DevOps facilitates uninterrupted delivery and coexistence between development and operation facilities, enhances the quality and performance of software applications, improving end-user experience, and help to simultaneous deployment of software across different platforms." (Kamalendu Pal & Bill Karakostas, "Software Testing Under Agile, Scrum, and DevOps", 2021)

"DevOps is a sprint-based approach that can catch coding flaws during the development of code due to security reviews, rework on previous sprint cycles, and testing." (David A Bird, "Hacker and Non-Attributed State Actors", Real-Time and Retrospective Analyses of Cyber Security, 2021)

"It is a set of practices emerging to bridge the gaps between operation and developer teams to achieve a better collaboration." (Mirna Muñoz, "Boosting the Competitiveness of Organizations With the Use of Software Engineering", 2021)

"It is a way to work were the software is rapidly developed and immediately deployed for operating in a computational productive environment. It is continuous delivery product development lifecycle. It must automate the development process. DevOps is both a culture and a set of technologies and tools used for automation." Laura C Rodriguez-Martinez et al, "Service-Oriented Computing Applications (SOCA) Development Methodologies: A Review of Agility-Rigor Balance", 2021)

"People from software development and operations work together to enhance the speed of delivery of new software features. It is a concept for bridging the gap between software development and software operations and integrating the logic of common responsibility for the complete software delivery lifecycle into one cross-functional team." (Anna Wiedemann et al, "Transforming Disciplined IT Functions: Guidelines for DevOps Integration", 2021)

"DevOps is a set of tools and processes that help automate IT operations." (Aniruddha Deswandikar,"Engineering Data Mesh in Azure Cloud", 2024)

"DevOps is a catch‑all term for the blending of roles between developers and operations engineers. As the barriers between roles such as database administrator, systems administrator, and software engineer have eroded, the term DevOps has emerged as a way of describing the intersection of responsibilities from all these camps, and their increasing interrelation in the lifecycle of a product. A crucial enabling aspect of this movement is the increased use of automation in building, deploying, and monitoring large applications." (NGINX) [source]

"DevOps is a collection of best practices and working methods for the software development process whose cumulative goal is to shorten the development life cycle and support practice such as continuous integration, continuous delivery and continuous deployment." (Sum Logic) [source]

"DevOps is a set of practices that works to automate and integrate the processes between software development and IT teams, so they can build, test, and release software faster and more reliably." Atlassian [source] 

"DevOps is the combination of cultural philosophies, practices, and tools that increases an organization’s ability to deliver applications and services at high velocity: evolving and improving products at a faster pace than organizations using traditional software development and infrastructure management processes." (Amazon) [source]

"DevOps refers to a broad range of practices related to the development and operation of software code in production in cloud data centers. DevOps is centered in Agile project management techniques and microservice support. DevOps approaches the entire software development lifecycle with automation based around version control standards." (VMWare) [source]

"The cultural movement that stresses communication, collaboration and integration between software developers and IT operations." (Global Knowledge)