SQL Server DML Update Trigger for Multiple Rows

Background

Currently I am working on a tool creating cable labels according to some specification. In this tool  I need to perform a complex concatenation of values from about 10 columns depending on the fill status of these rows and update the label column with the concatenated value in SQL Server 2012. The concatenation should be performed in case of a single row update and of a multi-row update via UPDATE <table> SET col1=value1, ..., colN=valueN
The weapon of choice was a DML database trigger AFTER UPDATE. As I hard some hard time to achieve my goal I want to share the code with the community, maybe you can get something out of it for your own project.

Table Structure

The cable label consists mainly of location information of the start and end points of a cable (components/devices connected by the cable and the room where the device can be found). My table has a structure like this, with fields not required for the trigger operation omitted:

• KeyField int, primary key
• CONCAT_VALUE varchar(1000)
• [...]
• NUMBER int
• START_COMP_ID varchar(100)
• START_COMP_IS_NOMEN bit
• START_AREA_ID varchar(50)
• START_ROOM_NO varchar(200)
• START_LEVEL_ID varchar(10)
• START_BLD_ID varchar(50)
• END_COMP_ID varchar(100)
• END_COMP_IS NOMEN bit
• END_AREA_ID varchar(50)
• END_ROOM_NO varchar(200)
• END_LEVEL_ID varchar(10)
• END_BLD_ID varchar(50)
• FUNCTION_ID varchar(10)
• [...]

Database Trigger

The database trigger consists of two main parts: one executed if a single line has been changed (@@ROWCOUNT = 1, mostly for manual input in the DB editor) and one for multi-row update (@@ROWCOUNT > 1, e.g. in the case of UPDATE table SET <some column> = <new value>).

In the first case one can use IF statements checking if some column is NULL directly via selection by the primary key column. An example:

DECLARE @Buffer varchar(255) 

[...] 

IF NOT (SELECT START_COMP_ID FROM table WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

   @Buffer = <concatenation> 

[...]

UPDATE table SET CONCAT_VALUE = @Buffer WHERE KeyField =(SELECT KeyField FROM inserted); 

In case of multi-row updates I used an approach with DB cursor which loops over the INSERTED table. The IF statement has then to be based on the value coming from the cursor:

DELCARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR SELECT KeyField FROM inserted 

[...]

OPEN tableCursor 

FETCH NEXT FROM tableCursor INTO @KeyField

WHILE @@FETCH_STATUS = 0 

  BEGIN 

    IF NOT (SELECT START_COMP_ID FROM table WHERE 

    KeyField = @KeyField) IS NULL

    [...] 

    UPDATE table SET CONCAT_VALUE = @Buffer WHERE              KeyField = @KeyField; 

    END 

Complete Code of Trigger

Here is the complete code of the trigger if you want to study it in detail:

USE [myDB]

GO

/****** Object:  Trigger [dbo].[TriggerName]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[TriggerName] ON [dbo].[myTable] 

AFTER UPDATE 

AS 

DECLARE @START_COMP_ID varchar(100);

DECLARE @START_LOCATION varchar(255);

DECLARE @START_RACK varchar(255);

DECLARE @END_COMP_ID varchar(100);

DECLARE @END_LOCATION varchar(255);

DECLARE @END_RACK varchar(255);

DECLARE @FUNCTION varchar(255);

DECLARE @Buffer varchar(255);

DECLARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR

SELECT KeyField FROM inserted

BEGIN

IF @@ROWCOUNT = 0

    RETURN

SET NOCOUNT ON;

SET @Buffer = '';

-- Execute only if one line has been changed

If (SELECT COUNT(*) FROM inserted) = 1

  BEGIN

-- ID part for component/technical place

    IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted) IS NULL

-- Component ID is not according to nomenclature

      IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))= 1

        SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + '>';

      ELSE

        SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KABEL_ID=(SELECT KABEL_ID FROM inserted));

    ELSE

    SET @START_COMP_ID = '';

    IF NOT (SELECT ACCNOMEN_FUNCTION FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @CONCAT_VALUE = '[' + (SELECT CONCAT_VALUE FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE

      SET @ACCNOMEN_FUNCTION = '[-';

    IF NOT (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @CONCAT_VALUE = @CONCAT_VALUE + '.' + (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + ']';

    ELSE

      SET @CONCAT_VALUE = @CONCAT_VALUE + ']';

-- ID part of location/building

    IF NOT (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))        IS NULL 

      SET @START_LOCATION = (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE

      SET @START_LOCATION = ''; 

    IF NOT (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_RAUM_NR FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_ROOM_NO FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_FLAECHE_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_AREA_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

-- Rack and stuff

    IF NOT (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_RACK = '#' + (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE 

      SET @START_RACK = '';

      IF NOT (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

        SET @START_RACK = @START_RACK + '.' + (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_RACK = @START_RACK + '.b' +                                                          (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

----------------------------------------------------------

-- END component

----------------------------------------------------------

[...] similar statements as above, omitted for better reading

-- Final assembly of label tag string and DB update 

SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' + @END_KOMP_ID + @END_LOCATION + @END_RACK;

-- Insert statements for procedure here

IF @Buffer <> ''

  BEGIN                                                                                             UPDATE CABLES_LIST SET CONCAT_VALUE = @Buffer WHERE KeyField=(SELECT KeyField FROM inserted);                                                                                   END

END                                                                                             ------------------------------------------------------------------------------------------------- Multi Row Part                                                                             ------------------------------------------------------------------------------------------------

ELSE

  BEGIN

    OPEN tableCursor

    FETCH NEXT FROM tableCursor INTO @KeyField

    WHILE @@FETCH_STATUS = 0

      BEGIN

      -- ID part for component/technical place

        IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField = @KeyField) IS NULL

      -- component ID is not according to nomenclature

          IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField = @KeyField) = 1

            SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField =                  @KeyField) + '>';

          ELSE

            SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KeyField =                        @KeyField);

        ELSE

          SET @START_COMP_ID = '';

        IF NOT (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField) IS NULL

          SET @FUNCTION = '[' + (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField);

        ELSE

                                                                                            [...] the structure of the omitted code follows the code for the 1 row update. Statements of the type KeyField=(SELECT KeyField FROM inserted) are always replaced by KeyField = @KeyField   

SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' +

  @END_COMP_ID + @END_LOCATION + @END_RACK;

-- Update table and loop to next row of INSERTED table

IF @Buffer <> ''

Begin

UPDATE CABLES_LIST SET ID_ACCNOMEN = @Buffer, QR_CODE_TEXT = 'CAB:' + @Buffer WHERE KABEL_ID = @CableID;

END

FETCH NEXT FROM tableCursor INTO @CableID

END

END

END

Mia and Me Orakelsprüche

Meine Kinder haben momentan nur EIN Thema: Mia and Me. Auf ihre Anregung hier eine Sammlung von Orakelsprüchen aus den verschiedenen Folgen.

Staffel I

Folge 1 - Ankunft in Centopia: "ICH BIN MIA"

Folge 2 - Eine neue Hoffnung: "AUCH WENN VOR QUAL DIE OHREN KLINGEN, WIRD NEUE HOFFNUNG EIN SCHRILLER TON DIR BRINGEN"
Folge 3 - Ein seltsames Orakel: "WENN AUS EINEM VIELES WIRD VERFOLG DEIN ZIEL DANZ UNBEIRRT."
Folge 4 - Der verlorene Trumptus: "DAS GESICHTSLOSE BÖSE GANZ GESCHWIND VERSTREUT DIE HOFFNUNG IN ALLE WIND(E)"
Folge 5 - Das goldene Einhorn: : "WIND UND WASSER, GOLDENES HORN, MIT ONCHAO WIRD NEUE HOFFNUNG GEBOREN
Folge 6 - Onchao und das Paradies:
Folge 7 - Beeren für Phuddle: "FEST VERKNÜPFT DURCH DES KÖNIGS HAND DEN BLICK SENK/WÄG (?) AB AUF'S KLEINE LAND"
Folge 8 - Ziggos freier Tag: "VON HÄNGENDER SCHÖNHEIT LIEBEVOLL BEWACHT UND VON FREUND UND FEIND IN GEFAHR GEBRACHT."
Folge 9 - Kleiner Drache Baby Blue: "DUNKEL IST DER BAUCH UND KEIN ENDE SICHT. FINDEN EINEN FREUND UND ENTDECKE DAS LICHT."
Folge 10 -  Der große Blütenbaum: : "EINHÖRNER KOMMEN UND EINHÖRNER GEHEN, DIE BELOHNUNG WERDEN SIE AM ENDE VOR SICH SEHEN"

Folge 11 - Polytheus im Goldrausch: "SCHÄTZE VERSTECKT IN DES DSCHUNGELS GRAB SUCHE HILFE BEI DEM DER NOCH NIE HILFE GAB."
Folge 12 - Phuddles große Stunde: "IN DER HÖHLE UNTER WEIN DER SCHATZ WIRD SEIN UND DER KLEINSTE IM KREIS ERLANGT DEN PREIS.

Folge 13 - Das Feuereinhorn: "ES KOMMT DIE ZEIT DIE VOLLENDUNG VERSPRICHT UND FREUNDE DICH BRAUCHEN, ERFÜLL DEINE PFLICHT."
Folge 14 - Der verdorrte Wald: "WO ALTE, KAHLE ÄSTE SICH RECKEN UND KLAGEN, FOLGE DEM PFAD DIE ERLÖSUNG ZU WAGEN"
Folge 15 - Die geheimnisvolle Laterne: "EIN SCHATZ KANN LÜGEN UND KANN BETRÜGEN SUCH DEN PFAD (/FARN?) WO DIE JUNGEN SICH VERGNÜGEN
Folge 16 - Die Einhornfalle: "GEH HINTER WASSER, VERWANDLE DIE LIST. KÄMPFE FÜR ONCHAO DER SCHUTZLOS IST."
Folge 17 - Die Blütenfestprinzessin: "DU SUCHST DEN PFAD, DER EINSICHT BRINGEN SOLL. FINDE DIE PFLANZE DEREN MAGEN NIE VOLL."
Folge 18 - König für einen Tag: "HINTER DER BRÜCKE, VERGOSSENE TRÄNEN. VERBOTENES HANDELN ERETTET DIE SCHÖNEN."
Folge 19 - Phuddles neueste Erfindung: "ES IST NICHT EINFACH DAS GOLD ZU ENTDECKEN, IM TIEFEN TAL NUR KANN ES SICH VERSTECKEN"
Folge 20 - Die Höhle der Wahrheit: "UM ZU BRINGEN DAS RETTENDE STÜCK ANS LICHT FÜRCHTE DIE DUNKELHEIT DER HÖHLE NICHT"
Folge 21 - Wiedersehen mit Baby Blue: "WIE DIE ELFEN ZU FLIEGEN IST WICHTIG UND LEICHT DIE ANTWORT LIEGT DORT WO LAND DEN HIMMEL ERREICHT."
Folge 22 - Die Vollmondnacht: "UNTER DER MASKE, UNTER DEM MOND, DAS IST DER ORT WO DIE SUCHE SICH LOHNT"
Folge 23 - Die defekte Wasserkuppel: "IM WASSER ZU LANDE SUCH MIT BEDACHT, DURCH LIST WIRD DER FEIND ZUM RETTER GEMACHT"
Folge 24 - Invasion der Pane:
Folge 25 - Pantheas letztes Angebot:"GANZ NAHE AN DES UNHEILS QUELLE
DER LETZTE BLIEB AN DIESER STELLE"
Folge 26 - Die große Entscheidung:

Staffel II

Folge 1 - Das Geheimnis der vier Ringe: "VOR DER MISSION EIN RÄTSEL STEHT, DAS DIR JEMAND ZUHAUS VERRÄT"
Folge 2 - Der würdige Sohn: "NUTZE DEN WIND, SPRECHE MIT STEIN UND DIE BELOHNUNG WIRD SICHERLICH DEIN."
Folge 3 - Tanz mit den Sternen: "SEI BEREIT DICH ZU ENTFERNEN, TANZE GEMEINSAM MIT FUNKELNDEN STERNEN"
Folge 4 - Der ungebetene Gast: "DER DIEB IST WO ER HINGEHÖRT"
Folge 5 - ??: "SUCHE DEN RING UN ER WIRD DEIN"
Folge 6 - ??: "DIE DUNKELELFE HAUSTE DORT, NUTZ IHREN GEIST AN DIESEM ORT"
Folge 7 - Das Fest des Bolobo: "WO NIEMAND MEHR FLÖTET UND STILLE ENTSTAND, SUCHE DEN KÜNSTLER DER EINSTMALS VERBAND"
Folge 8 - Die Brücke am Jadehorn: "DURCH DIE AUGEN DES EINHORN WIRST DU ERFAHREN, DASS DER DEN SU SUCHST NUR SCHLÄFT TROTZ GEFAHREN"
Folge 9 - Ein doppeltes Spiel: "WENN DEINE FEINDE DEINE FREUNDE FANGEN, WIRST DU VOM ERHABENEN HILFE ERLANGEN"
Folge 10 - Die Blume der Erinnerung: "Ein alter Feind den König sah, nur die Natur weiß wie's geschah"
Folge 11 - Die Feuerblume: "DIE BLUME KOSTBAR UND TEUER BEKOMMST DU NUR DURCH FEUER"
Folge 12 - Die Reise zum Nordhorn: "HAB KEINE ANGST WENN FEIDE SIND NAH, DENEN DU HILFST DIE SIND FÜR DICH DA"

FOLGE 13 - Der König der Einhörner: "WENN EUCH GEFAHR DROHT, VERZAGET NICHT, WAS EUCH VEREINT SCHLÄGT DEN BÖSEWICHT"
Folge 14 - Die Regenbogenquelle: "UM ZU BEKOMMEN WAS DU GLAUBT DAS SEI DEIN, FOLGE DER SPUR DER DINGE DIE HIER NICHT DAHEIM"
Folge 15 - Ein weiser Entschluss: "WAHRE FREUNDE SIND IMMER NAH, SAGEN ADE UND BLEIBEN DOCH DA"
Folge 16 - Das wahre Gesicht: "EINER AUS DEINEM FREUNDESKREIS LÄSST DICH IM STICH UND NOCH MEHR ER WEIß"
Folge 17 - ?? : "UM ZUR DURCHQUEREN HEIßESTES FEUER BEFREIE DAS GRÖSSTE DER UNGEHEUER"
Folge 18 - Der Bann ist gebrochen: "WILLST DU DEN ZAUBER BRECHEN SCHNELL, FÜLLE DEN TRANK IN DIE QUELLE SCHNELL"

Staffel III

Folge 8 - Ein überraschender Handel: "ZU BEKOMMEN WAS SIE SUCHT SO LANGE, FINDET WAS SIE BRAUCHT DIE SCHLANGE."
Folge 9 - Die Nacht wird zum Tag: "WO SCHUPPIGE SCHWINGEN HALTEN WACHT, SUCHT NACH DER SONNE IN DER NACHT."
Folge 10 - Der Kreislauf des Lebens: "WO DIE SIEBENTE WELLE STEHT IN STILLE, ZUM LETZTEN KIND KOMMT ER DER WILLE."
Folge 11 - Verloren im Nebel: "IN VERNEBELTEM GEWIRR UND ZERKLÜFTETEM FELS, DAS EINHORN WACHT ÜBER DAS WAS DU WILLST."
Folge 12 - Auf der Suche nach Simo: "UM DEINE FEINDE ZU BESIEGEN UND SEIEN SIE IN SCHAREN, SUCH NACH DEM DER ÜBER DAS MEER GEFAHREN."
Folge 13 - Im schwarzen Wald: "UNBEWEGLICH UND PARALYSIERT, GEWINNEN KANN NUR WER HARMONIERT."

Noch ohne Zuordnung zu einer Folge:

: "AUFOPFERUNG"

Blender Tricks: Using Freestyle for Highlighting Objects Part 2 - Material Options

Summary

This article describes the creation of illustrations mixing elements with line art and "photorealistic" art in one image using Blender with the Cycles renderer and the options of the Freestyle line renderer. Here an example:

Background

This is a follow up to an earlier post of mine "Blender Tricks: Using Freestyle for Highlighting Objects Part 1 - Layer Options". In this article I explained how to use Blender with Cycles renderer to create illustrations with highlighted objects by assigning Freestyle line options per render layer and exploiting the "Alpha Over" render-layer node.
This layered technique is - from my point of view - a good choice for scenes with non-overlapping/interfering object, but has some drawbacks concerning artifacts of the per layer rendering process if object intersect.
Just recently (in the process of creating the image above) I discovered a better approach exploiting the Freestyle options on Material level. Similar to my previous example I want to put green lines around the red cube (only) in the image below:

All my examples here are based on Blender 2.74. So - let's get going...

Step 1: Enable Freestyle

To activate Freestyle line art rendering in Blender/Cycles tick on the "Freestyle" option on the "Render" menu:

Now all Freestyle options are available and without any changes Blender puts black lines around both cubes:

Step 2: Freestyle Line Settings in Material

With Freestyle enabled, a new Freestyle options tab becomes available in the "Materials" tab. There one can influence the line color and the line transparency/alpha:

To hide Freestyle lines in the output, put the alpha value (marked by the red ovals in screenshot above) in the Freestyle options of the corresponding material to zero.

In my example I put the Freestyle line color of the red material to green and alpha to 1. The blue material is adjusted to color = black (default) and alpha = 0.

However - this is where I struggled for quite some time - it is not enough to adjust the Freestyle settings in the material. If you only change color/alpha here, nothing will change in the output.

Step 3: Switching Freestyle Control from Global to Material

To put control of the Freestyle color/alpha options into the hand of the material one has to add two modifiers on the 

• "Render Layers" -> "Freestyle Line Style" -> "Color" options AND (!) the
• "Render Layers" -> "Freestyle Line Style" -> "Alpha" options:

for both option tabs add a "Material" modifier.

Now the Freestyle line color and visibility is controlled via the material. The result looks then like this - as desired:

Comparison to Layer-Approach

I presented two possible approaches to put differently colored lines around objects in Blender/Cycles. Which one is preferable now?

My opinion is the following: if you have scenes with separated objects casting not too many shadows onto each other, the layered approach is preferable. Furthermore it offers the possibility to control Freestyle for many objects/materials at once (= by layer). The drawback of the layer technique is that you have to have a scene with transparent background because object with lines have to be rendered in front of a transparent background. If you want more control over the lines, scenes where background transparency cannot be used and you want better results in your output the material based is the weapon of choice. Nevertheless material based is more time consuming as it requires adjusting Freestyle options for each of the materials in the scene individually.

This was it, I hope you liked this post and it helps you with your own project - keep on geekin'!

Cheers 

WolfiG

Office Kaizen: 5S of Electronic Documents Using MS Sharepoint

Summary

Sharepoint can efficiently be used to get a grip on unstructured document repositories with several thousand documents. Furthermore, 5S provides a standardized framework to tackle the cleaning up of physical working environments. How to combine these two to an efficient tool to clean up virtual working environments is described in this blog.

Background

5S and Kaizen

Kaizen (jap. "Change for the Better") - the continuous improvment process - is one key element of Lean Manufacturing and Lean Management. One of the basic techniques or implementations of Kaizen is the "5S" methodology to tidy up, structure the working environment. The goal is here - like in any "Lean" activity - to clean th system from "Muda", the "waste" (I prefer "non value adding") in a production system.
Kaizen and 5S has been developed and perfectioned in the automotive industry and then adopted in many other manufaturing systems. One advantage in these environments is the repeatability of process steps: we are optimizing a production line consisting of a sequence of production steps / production units, which work in a repeating mode, producing the same (or at least similar) part over and over again. In these environment one can straight forwardly apply statistical methods (like the six sigma toolset) to find weaknesses in the process, identify Muda and to improve.
5S as such is a concept providing a well defined set of activities to clean up and standardize a working environment. Goal of these activities is to create (and preserve) a working environment (e.g. a workshop) where the work can be done in the most effective and efficient way. The main instrument to achieve this goal is to put tools to well defined, clearly visible and "self explaining" places and to arrange these places in the best way to support the working/assembly sequence.
5S defines the following activities to achieve the goal "optimized workshop":

• "Sort" (jap. Seiri): sort things in "shall be kept / is required", "remove / throw away", "not sure yet"
• "Systematic Arrangement" (jap. Seiton): arrange things / tools in an order supporting an optimized workflow (lean concept "generate flow")
• "Shine" (jap. Seiso): tidy up/clean/renovate the workplace, remove all shabbiness
• "Standardize"(jap. Seiketsu): Use the same conecpts for all workplaces. Provide guidelines how things are to be kept in order and do not allow exceptions
• "Sustain"/"Self Discipline" (jap: Shitsuke): make all processes / changes established a habit. Do not allow degradation of already achieved standards.

Office Kaizen

Stemming from manufacturing, Lean and Kaizen is influencing other areas of business activities as well. One area of application Lean ideas and concepts is the so-called "Office-Kaizen"/"Office Excellence" movement, trying to transfer Lean to optimize processes and work in office environments. One technique is - in coherence with the 5S idea - to clean up offices and to put working material in well defined and standardized places: e.g. inbox/outbox containers are clearly visible, standardized in shape and color and - if possible - in all offices at the same place. Another example would be how the organizations deals with documents: well defined storage locations, labeling, etc.

Electronic Documents

Saying the word "documents" we immediately come to the virtual world of electronic data storages and what I personally am working on. Almost any organization is fighting with a mayhem ("well - it evolved over the years...") of unstructured data repositories littered with all kinds of electronic documents. Common "concepts" of the work in these data repositories are the following:

• folders names are used as keywords. Effect: documents are copied to more than one folder to put them in different contexts (muda of storage, transportation, (confusion))
• document versions are indicated via (undefined) prefixes or suffixes like: *_date, date_*, *_v<version number>, *_new, *_final, *_reallyFinal, .....
  This practice does not only create waste (confusion), but also violates ISO9001, Chapter 4.2.
• No central guidance for the folder structure
• Documents are put wherever the user thinks it is useful

5S of Electronic Documents Using Sharepoint

This wouldn't be a technical blog if I didn't describe my own implementation and experiences in a real-world tool. The practices described here may be implemented in other tools as well, but I can only share experiences with MS Sharepoint.

It all began when my team lead approached me with the task to "improve the document repository for our team - we are loosing track over what is where". So I suddenly was confronted with a file share (roughly 3 years old) with about 11000 files stored in roughly 700 folders. In the folder structure all kinds of file types were in: text documents, presentations, excels, photos, technical drawings in native formats, technical drawings scanned to pdf, manuals, scientific papers, etc. etc.
Based on previous experiences my strategy was clear: introduce Sharepoint and move all the available documents to there. But it is important to be careful withthe sequence of steps and the things which need to be prepared upfront and which can be done "on the way".

Preparation of Seiri

Before you start with sorting documents it is important to spend thoughts about the categories the documents will be sorted. The ultimate goal is to separate those documents to be kept from those being obsolete. However, it is almost impossible to do this "keep/throw" classification without a pre-classification of documents into context categories. To do the latter, I introduced roughly 10 categories (sets of keywords) to categorize a document. As we are dealing with a large machine I defined categories. I tend to guide the users as much as possible by the use of lookup values in fields, this makes it possible to filter documents more reliably than for freetext fields. Here some examples as inspiration:

• Flag: "Keep/Throw"
• Document Type: technical drawing, meeting protocol, manual, product catalog, ... Not to be mixed up with the document file type (!). Field with lookup values
• Machine section: our machine is structured into sections, here a document can be related to one (or many) of these sections. Field with lookup values.
• Serial number: Freetext field to enter the serial number of an asset related to the document, e.g. for quality test protocols, etc.
• Technical place ID: ID of the technical place a document relates to. Field with lookup value
• Supplier/Author: who provided the document? Field with lookup values
• Document Date: this field is useful/required to indicate wher a document has been created initially on the file share - e.g. for meeting minutes. This initial date gets lost in the uploading process.
• ...

If this interlectual exercise has been done and has been implemented in Sharepoint as Document Library with custom columns, one can make the first mayor step and upload the documents from the file share to Sharepoint. I uploaded the whole folder structure "as is" to Sharepoint. as this preserves the folder structure as important element of user orientation. This is essential especially for the "pathfinders" which navigate along folder structures. I distinguish these from the "Googlers" who want to type in search phrases everywhere (I belong to the Googlers).

Step 1: Seiri

After the uploading of files took place, one can start with the actual "Seiri" exercise: sort the documents into categories, sort into "keep" / "throw". This exercise needs to be accompanied by proper communication to the relevant stakeholder: team leads, users, other. At first your users will be overwhelmed by the new tool and will try to get around it. I advice the following:

1. suppress the continuation of us of old tools by restricting access - switch the old file share to "read only" mode
2. have a coach close by providing immediate help with the new tool if needed
3. do categorization of files centrally - I am working together with a works student, who does an awful lot of "sand shoveling" ni the system. This will lead to partly wrong results in terms of categorization and keywording, but will immediately open the door for Googlers and filtering mechanisms
4. In the Sharepoint Document Library create one view I call "Flat List". This view has the attribute "view without folders" in the view definition. It is the key to create transparency and to identify duplicates and similar documents in your library.

Important is here that you quickly reacha relatively high degree of categorization in key document types. If you stall here, users will use trust in the power of categorization and will bail out.

Step 2:  Seiton

The custom categories are not only required for the sorting of documents, but also the key for the "Systematic Arrangement" of files. On the one hand, the systematic arrangement is achieved automatically by sorting things into categories, because they can now be filtered according to their categories/keywords. But Sharepoint offers the tool to go one step further in "arranging" files: the creation of views (= pre-defined filtered views on the document library) according to business context. I advice the following steps:

1. Define the relevant views together with key users. Most the views we use are based on the "Flat List", i.e. documents are shown without their folder structure. Example: We defined a couple of key views like "all technical drawings to section xy", "all meeting protocols" (these are scattered over several folders), "all manuals", etc. Do not over-engineer here, but start with something simple and grow the field during use.
2. Implement the views in Sharepoint
3. Communicate the availability of views and how they can use them to your users. Unexperienced users will hardly use this feature without training

Step 3:  Seiso

I strongly believe that every enterprise content system needs one layer / user interface, where the "documents" (and the library), data sources and other sources of "raw data" are made accessible under one hub. This hub needs to provide "metadata" information on the content and end-user friendly navigation and help where to find what. Sharepoint offers the set of tools to integrate all content under one platform, namely it offers Wiki functionality whic can be used to create central pages providing links to documents together with information about the meaning, context, etc. of these documents.
What does this have to do with "Seiso"/"Shine"? The creation of user-friendly web pages collecting documents and providing information about these is nothing than the virtual counterpart of a toolbox. Additionally, these web pages are a central tool to implement Lean's "visual management": tools to create a self explaining/transparent working environment.Here a note: I did not come across ideas how to implement "visual error states" of the system which is part of visual management.
Furthermore, if you put a little love into these web pages (make them "shine"), users will more likely like and use them and will get around more easily. One example of making pages "shine" is to integrate ways to navigate along visual web assets like clickable image maps or SVGs (see as well my other blog post).
One key element in the Seiso step is to link views (which is a feature of Sharepoint) created in the Seiton step in the web layer. This creates a lot of user friendliness and is very attractive from a user point od view, because the use and technical details of the document library is abtracted to the user.

Step 4: Seiketsu

If you arrive at step 4 already a good part of the work has been done. To standardize I mostly use email: where we send documents as attachments before, I send now emails with links to documents in Sharepoint, so they are "forced" to deal with the tool.

Step 5: Shitsuke

One key element not to let things degrade is a central team taking care of the Sharepoint webspace and the server. From time to time mini 5S-events to clean up are useful. Ultimately, the tool will stay attractive if users find it something supporting their work.

Dear reader - this was it, I hope you liked my post and it helps you with your own project - keep on geekin'!

Your WolfiG

Math Gems: the Snail Shell

Previous post: the Breather

Gem

Snail Shell faceted (facets have been post processed with Blender, not Mathematica output)

Snail shell smooth

The pearl material has been based on Kaluura's Post on Blenderartists

Math

The snail shell is given by (see e.g. virtualmathmuseum.org)

The shape above is the result of setting the factors to:

• aa:=0.1
• bb:=0.1
• cc:=0.2
• dd:=0
• ee:=-1 

The corresponding Mathematica script looks like this:

aa := 0.1;
bb := 0.1;
cc := 0.2;
dd := 0;
ee := -1;
r := s (aa + bb Cos[u]);
vv := v + (v + ee)^2/16;
s := Exp[\[Minus]cc*vv];
x = r*Cos[vv]
z = r*Sin[vv]
y = dd*(1 \[Minus] s) + s*bb*Sin[u]
thePlot =
 ParametricPlot3D[{x, y, z}, {u, 0, 2 Pi}, {v, 0, 2 Pi},
  NormalsFunction -> None, Boxed -> False, Axes -> False]

Export["Snailshell.ply", thePlot, VertexNormals -> Automatic]

Math Gems: the Breather

Previous Math Gem: the Klein Bottle
Next Math Gem: the Snail Shell

Gem

This image was rendered with Blender 2.74

For the gold shader used in this scene I want to thank E. M. Malo and his Gold Shader

Math

The Breather is a surface parameterized in 3 dismensions by (see e.g. Wikipedia):

 

 The following Mathematica script produces output in the PLY 3d-format which can be readily imported into Blender for rendering/postprocessing:

r := 1 - b^2;
w := Sqrt[r];
denom := b ((w Cosh[b u])^2 + (b Sin[v w])^2);
x = (2 r Sinh[b u] Cosh[b u])/denom - u
y = (2 w Cosh[b u] (-(Sin[v] Sin[v w])
     - w Cos[v] Cos[v w]))/denom
z = (2 w Cosh[b u] (Cos[v] Sin[v w]
     - w Sin[v] Cos[v w]))/denom
breather = {x, y, z}
thePlot = ParametricPlot3D[
  Evaluate[breather /. b -> 0.4],
  {u, -13.2, 13.2}, {v, -37.4, 37.4},
  PlotRange -> All, PlotPoints -> {60, 150},
  Axes -> None,   Boxed -> False, PlotPoints -> 40,
  Mesh -> None, NormalsFunction -> None]

Export["Breather.ply", thePlot, "VertexNormals" -> Automatic]

Math Gems: the Klein Bottle

Next Math Gem: the Breather

Background

In my previous post I discussed rather technically how Blender can be used as render backend for Graphics3D output created with Mathematica. Dealing with that matter, I thought it could be nice to create a couple of image of mathematically described 3D surface objects, which I call "Math Gems", because they look like jewelry.

The Klein Bottle

Klein Bottle faceted

Klein Bottle smooth with mesh from edges of surface

Description in Mathematica

I my previous post I had some problems creating usable Mathematica output for the the parametrized immersion in 3 dimensions, so called "Klein Bottle" based on the equations given in Wikipedia. Finally I found the proper way to create the shape in Mathematica.
The Klein bottle can be parametrized in the following way (see e.g. Paul Bourkes article):

In Mathematica formulation:

r = 4 (1 - cos(u)/2)
x = Piecewise[({
    {r cos(u) cos(v) + 6 (sin(u) + 1) cos(u), 0 <= u < \[Pi]},
    {r cos(v + \[Pi]) + 6 (sin(u) + 1) cos(u), \[Pi] <= u <= 2 \[Pi]}
   })]
y = Piecewise[({
    {r sin(u) cos(v) + 16 sin(u), 0 <= u < \[Pi]},
    {16 sin(u), \[Pi] <= u <= 2 \[Pi]}
   })]
z = r sin (v)
thePlot =
 ParametricPlot3D[{x, y, z}, {u, 0, 2 \[Pi]}, {v, 0, 2 \[Pi]},
  Axes -> None, Boxed -> False, PlotPoints -> 50, MaxRecursion -> 10,
  Mesh -> None, NormalsFunction -> None]
Export["KleinBottle1.ply", thePlot, "VertexNormals" -> Automatic]

The resulting PLY file can directly imported to Blender. Doing some Blender post processing yields in the images above.