Showing posts with label operations. Show all posts
Showing posts with label operations. Show all posts

05 March 2024

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

Business Intelligence
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!

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)

25 December 2016

Strategic Management: Strategy & Tactics in Chess (Just the Quotes)

"If we assume that strategy consists in defining the object and making plans accordingly, and tactics in executing these plans, then we must look upon combination as the culmination of tactics. By combination we mean a short part of the game, within which a certain purpose is attained by force. Its sequence of moves forms a logical chain and cannot be divided up. When looked at one by one, they may seem to be purposeless or even mistakes, yet together they form an exceedingly beautiful unit. After a series of moves incomprehensible by themselves, the solution suddenly follows and their real purpose comes clearly to light. From this it follows that the aim must have already been conceived .from the first move of the combination. This is the difference between combinative and straightforward play. For a short space of time special and not general rules apply; as it were an exceptional state of things prevails." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"In chess we distinguish between Strategy and Tactics. Strategy is concerned with the setting of an aim and the forming of schemes. Tactics are concerned with the execution of the schemes. Strategy is abstract, tactics are concrete. Expressing it in a popular way: Strategy requires thought, tactics require observation." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"Notwithstanding the obviously great importance of tactics, nearly all existing manuals give greater prominence to strategica1 problems. This is principally due to two reasons: Firstly: the development of tactical capabilities is for the greater part a matter of practice and a question of routine. Secondly: the problems of tactics are so numerous and so varied in nature, that it seems an almost impossible undertaking to treat this domain systematically." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"We call games such as the preceeding one, in which strategy plays such an important part, positional games, in contrast to combinative games, in which the strategy is of secondary importance. One must not, however, identify strategy with positional play, for strategy is an aim and positional play represents a certain method of playing. The study of positional play teaches us the strategic lines." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"We have stated that strategy forms an indispensable element in the proper treatment of a game of chess; the same can be said, perhaps even with greater reason, about tactics. The chess-player who can judge a position very clearly and who can adapt his schemes to this position, will not be able to make use of these capabilities, if at the same time he is not well practised in tactics. As a rule a tactical mistake involves a much heavier punishment than a strategical one.
Nor are the conceptions of tactics and combinative play identical. One might say that tactics comprise all the moves in a game, and thus include also the combinations which occur in it." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"We make a difference between general and special principles of strategy. The general principles originate directly from the aim and the nature of Chess, and therefore they are constantly in force. It is, for instance, a general principle which goes without saying, that one has to procure the greatest possible freedom of action for one's men. The special principles apply only if the position shows certain peculiarities on account of which a special line of strategy has to be followed." (Dr. Max Euwe, "Strategy & Tactices in chess", 1937)

"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)

"Castling may even be unnecessary. In some cases it might be prudent to keep the king in the center, possibly not castling at all. The center is often safer than the flank in closed positions. Strategy and long-term planning assume great importance . You usually have time to maneuver against your opponent's weaknesses, something hard to do when the center is open." (Bruce Pandolfini, "Weapons of Chess: An omnibus of chess strategy", 1989)

"A weakness can be tactical or positional. A tactical weakness is one based on immediate or temporary circumstances. Given time, a tactical weakness can be defended or eliminated: protection can be added, or the threatened man moved to safety. [...] When people talk about weakness, they usually mean positional, not tactical weakness. Positional weaknesses tend to have long-term ramifications. They are not subject to the shifting situation from move to move as are tactical weaknesses. It takes more than a few moves to correct a positional weakness, if it can be done at all." (Bruce Pandolfini, "Weapons of Chess: An omnibus of chess strategy", 1989)

"The strengths, often concealed below the surface, depend on the activity and interrelationship of the pieces, and on the tactical possibilities and threats that can be generated. Bringing these latent resources to the surface requires a deep and penetrating analysis and accurate calculation." (Bruce Pandolfini, "Weapons of Chess: An omnibus of chess strategy", 1989)

"[...] events on the chessboard are by no means accidental, on the contrary: everything is linked with everything and the dynamics involved in selecting a correct strategic plan reflects, delicately but also accurately, your dynamic thinking abilities!" (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"Every player appreciating the aesthetics of chess dreams about games where a single main strategical plan is carried through without distraction to a successful conclusion; these should go hand in hand with the competitive aspects of chess. Such a course is very difficult because our opponent usually has his own plan, his own strategy and intentions - which are very different from ours. A flawless game won by somebody probably does not exist, a mistake must creep into the game somewhere." (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"However, the dynamic changes and laws should be understood, not only from the point of view of strategy and tactics but also from that of the time of the development of ideas." (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"Opening presents opportunities for the most complicated but also the most fantastic strategic and tactical manoeuvres. In both cases the prerequisite is an accurate rhythm, first, a slow, wait-and-see type, then, at the right moment, a rapid one with surprising turns [...] An average dancer does not have the gusto for it - nor does a chess player without a sufficient sense of dynamics!" (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"Strategy and tactics often work hand in glove." (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"The ability to think dynamically in opening strategy may also be illustrated by a mastery of transferring experience and ideas from one opening to another. Seemingly incoherent courses of the game may sometimes have much in common. And sometimes a simple comparison brings new experience, surprising findings and thoughts!" (Vlastimil Jansa, "Dynamics of Chess Strategy", 2003)

"A tactician feels at home reacting to threats and seizing opportunities on the battlefield. When your opponent has blundered, a winning tactic can suddenly appear and serve as both means and end. […] Every time you make a move, you must consider your opponent’s response, your answer to that response, and so on. A tactic ignites an explosive chain reaction, a forceful sequence of moves that carries the players along on a wild ride. You analyze the position as deeply as you can, compute the dozens of variations, the hundreds of positions. If you don’t immediately exploit a tactical opportunity, the game will almost certainly turn against you; one slip and you are wiped out. But if you seize the opportunities that your strategy creates, you’ll play your game like a Grandmaster." (Garry Kasparov, "How Life Imitates Chess", 2007)

"Against solid strategy, diversionary tactics will either be insufficient, or flawed. If they are insufficient, you can and should ignore them, continuing along your path. If they are radical enough to force you from your path, they are likely flawed in some way - unless you have blundered. Often an opponent is so eager to get you to change your course that he fatally weakens his own position in the attempt." (Garry Kasparov, "How Life Imitates Chess", 2007)

"Effective tactics result from alertness and speed, this is obvious, but they also require an understanding of all the possibilities at hand. Experience allows us to instantly apply the patterns we have successfully used in the past." (Garry Kasparov, "How Life Imitates Chess", 2007)

"In chess we see many cases of good strategy failing due to bad tactics and vice versa. A single oversight can undo the most brilliant concepts. Even more dangerous in the long run are cases of bad strategy succeeding due to good tactics, or due to sheer good fortune. This may work once, but rarely twice. This is why it is so important to question success as vigorously as you question failure." (Garry Kasparov, "How Life Imitates Chess", 2007)

"Tactics involve calculations that can tax the human brain, but when you boil them down, they are actually the simplest part of chess and are almost trivial compared to strategy." (Garry Kasparov, "How Life Imitates Chess: Making the Right Moves, from the Board to the Boardroom", 2007)

"Whereas strategy is abstract and based on long-term goals, tactics are concrete and based on finding the best move right now. Tactics are conditional and opportunistic, all about threat and defense. No matter what pursuit you’re engaged in - chess, business, the military, managing a sports team - it takes both good tactics and wise strategy to be successful." (Garry Kasparov, "How Life Imitates Chess", 2007)

"A chess hypothesis is basically the equivalent to drawing up a strategic plan. Experimentation in chess is equivalent to the moves that are found to carry out each plan. Throughout the history of chess, both the plans (the hypotheses) as well as the moves (the experiments) have been evolving (thanks to results from the practice of the game and from analyses), and this knowledge is the patrimony of professional players." (Diego Rasskin-Gutman, "Chess Metaphors: Artificial Intelligence and the Human Mind", 2009)

"Finally, chess has a science - like special attraction since it lets the player first propose hypotheses of different strategic plans that are based on the game rules and possible moves of the pieces and then refute those hypotheses after careful investigation of the different lines of play. This process is analogous to the everyday work of a scientist." (Diego Rasskin-Gutman, "Chess Metaphors: Artificial Intelligence and the Human Mind", 2009)

"Still, it is good to realize how the synthesis between strategy and tactics is established. Therefore, we must always consider the activity of the pieces on both sides. A player may have a fantastic pawn formation, control a beautiful open file and possess a strong square, but if he goes down to a direct mating attack on the other side of the board, all his strategic advantages come to naught." (Herman Grooten, "Chess Strategy for Club Players: The Road to Positional Advantage", 2009)

"tactics and strategy hold together, as it were. With tactics, a combination is a 'random picture' in the game. For example, a piece is unprotected and this can immediately be exploited. Strategy means longer-term thinking." (Herman Grooten, "Chess Strategy for Club Players: The Road to Positional Advantage", 2009)

"The problem of identifying the subset of good moves is much more complicated than simply counting the total number of possibilities and falls completely into the domain of strategy and tactics of chess as a game." (Diego Rasskin-Gutman, "Chess Metaphors: Artificial Intelligence and the Human Mind", 2009)

"Vision is a capacity to understand a position and to generate solid strategic plans. And a good base of chess knowledge is needed to understand what it means to play with brilliance or elegance." (Diego Rasskin-Gutman, "Chess Metaphors: Artificial Intelligence and the Human Mind", 2009)

"[...] we can gather that strategy and tactics are in constant conflict with each other. For example, a strategically beautiful set-up can be spoilt by an 'accidental combination' at any moment. But it is also true that a well-built-up game in most cases needs to be crowned with a tactical turn. In practice it has turned out that many players find it difficult to combine long-term play with a timely discovery of tactical finesses. Only the strongest players manage to find a kind of balance between these two components of the game." (Herman Grooten, "Chess Strategy for Club Players: The Road to Positional Advantage", 2009)

"Strategy must show us how to fight against players of our own strength and how to make progress. It must help us play a bit better this week than last week, not just by studying opening variations because, as Petrosian pointed out: 'to study opening variations without reference to the strategic concepts that develop from them in the middlegame is, in effect, to separate the head from the body." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"A good strategy is one that takes into account not only the requirements of the position, but also the opponent's strategy and tactics. Strategy lies between science and art. It supports the ability to evaluate positions, recognize patterns and imagine adequate plans. Modern chess both offers and requires more 'move-to-move' combat." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Chess strategy must lose some of its grandness, some of its sentential character, must come back to earth and become practical, so that we can make it work for us move by move." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Discovering the truth about a specific variation is a trial-and-error process. A complete strategy must consider human reactions as part of it." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Dynamic strategy is not only a reconciliation of classical strategy with the new problems raised in competitive chess, but also an escape from the circle of slow manoeuvres which rarely produce more than half points. It aims to promote fighting and uncompromising chess. The history of chess shows clearly that players who are afraid of losing rarely obtain outstanding results. Dynamic strategy is an attempt to revise the standard, static, conservative opinion on what strategy should be (a collection of rules about the centre, fixed pawn structure, open files, minority attack, queen- or kingside majority, two bishops, weak pawns and squares, and the standard plans to cope with these occurrences) and highlight strategy as a continuous process dealing with the present, the near future, and the distant future of modern chess positions." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Some theoreticians define tactics as combinations with sacrifices." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Strategy places an arsenal of efficient weapons in our conscious as well as (hopefully) our subconscious mind; the better this arsenal, the less superficial the assessment of the actual and emergent set-ups. By associating the general aspects of a position with some concepts, our strategic knowledge builds the foundations for suitable plans." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Strategy should neither be imparted nor perceived as a surrogate for ultimate truth, but as an organizer which turns an amorphous mass of perceptual concepts into a collection." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Strategy shouldn't set rules to be followed blindly; nonetheless, any theory can declare its stepping-stone concepts." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"The main goal of dynamic strategy is to develop the personality of a player, to discover everyone's uniqueness and turn it to good account. Social dynamism and the human personality are two assets of our day, and this is reflected in chess. Tactics and dynamics are becoming predominant in chess. (Mihai Suba, "Dynamic Chess Strategy", 2010)

"The true task of strategy is to help evaluate chess positions, their present state and their future possibilities. The ability to evaluate correctly is at least as important as tactical skill. Evaluation is, to an extent, subjective (it depends on which side of the board Tal is on!) and a strategy which claims 100% objectivity is utopian. A player must be flexible enough to adapt himself to the strategy, the strategy must be flexible enough to adapt itself to the player and both, in turn, must be flexible enough to adapt themselves to the position." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"The basic functions of strategy are to help the player: to reach a superficial evaluation of the position; to understand and/ or anticipate the opponent's plan; to understand and/ or anticipate the opponent's other weapons, especially when his play doesn't seem to obey a logical plan; to establish his own plan." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"In chess we use the term strategy as opposed to tactics and we assimilate it with positional play. I must show from the beginning that the use of these terms can be confusing. Strategy is not the opposite of tactics; it is the theory of tactics. It tries to clarify the general lines of tactics as well as its directions. In other words, it gives pointers for discovering plans, and plans, in their turn, direct the moves in given positions." (Mihai Suba, "Dynamic Chess Strategy", 2010)

"Appreciation of both strategy and tactics are the 'Ying and Yang' of a strong chess player. They sit side by side, in harmony with each other and you cannot hope to improve your play without working on both aspects of the game." (Adam Hunt, "Chess Strategy: Move by move", 2013)

"Chess strategy is concerned with the correct evaluation of a position and the formulation of an effective plan based upon its characteristic features. When the word 'strategy' is mentioned, people tend to jump to the conclusion that we are talking about long-term middlegame planning, and that you have to be some sort of psychic who can see the future, but that is rarely the case. Many features of a given position which are strategically important will evolve out of the opening and finish in the endgame, so it would be foolish to completely ignore these phases of the game. Plans can be short or long term, depending on how the landscape in front of you is changing as the game progresses. This is why the subject is so complex and so much material is available on it." (Adam Hunt, "Chess Strategy: Move by move", 2013)

11 June 2016

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)

29 March 2016

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)

17 June 2011

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) 

 List of values - concatenation
 

   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', ',') 

List of values - simple decomposition    

List of values - alphabet decomposition
   
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', ';')     

List of values - double decomposition 

 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. 

11 March 2011

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

Happy Coding!

14 December 2010

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 
 
concatenation 2 

    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 
 
concatenation 3

   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.
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.