Gestallt psychology

Today i wan’t to discuss a principle of psychology which was subject of a cognitive psychology course during my study at the university. That is ‘Gestallt’ psychology or gestalism (German: essence or shape of an entity’s complete form). It’s a theory of mind and brain positing that the operational principle is holistic, parallel and analog, with self-organizing tendencies. The Gestalt effect refers to the form-forming capability of our senses. You can use the gestalt principles by the design of your reports, dashboards or whatever aid you’re using when presenting your information.

Below you can see some examples of gestallt principles:

Reification
Reification is the constructive or generative aspect of perception, by which the experienced percept contains more explicit spatial information than the sensory stimulus on which it is based.

For instance, a triangle will be perceived in picture A, although no triangle has actually been drawn. In pictures B and D the eye will recognize disparate shapes as "belonging" to a single shape, in C a complete three-dimensional shape is seen, where in actuality no such thing is drawn.

Emergence

Emergence is the process of complex pattern formation from simpler rules. It demonstrated by the perception of the Dog Picture, which depicts a Dalmatian dog sniffing the ground in the shade of overhanging trees. The dog is not recognized by first identifying its parts (feet, ears, nose, tail, etc.), and then inferring the dog from those component parts. Instead, the dog is perceived as a whole, all at once.

Pragnanz

The fundamental principle of gestalt perception is the law of prägnanz (German for pithiness) which says that we tend to order our experience in a manner that is regular, orderly, symmetric, and simple.

 
 
 
 
 
 
 
 
 


On the internet you can find all kind of these examples and tehy are quite interesting. Some examples are usable in your reports and some don't. For instance the example right here above is quite useful. By using colors boldness, italic you can implicitly suggest a relation between some information.

By ordering information which belongs together and splitting information which doesn't belong to each other you can increase readability.
 
Greetz,
Hennie
 
 

synchronize connection strings

In SSIS you have the possibility to create datasources in your project. These are design time projectlevel connectionstrings to connect to your datasources. So this seems a good idea: define once and reuse this multiple times in your packages. So when I start a project I create datasources and in the connection manager box (below) I select “New Connection from Data source”. Great! Perfect! This is what I want. I create the datasources at project level and use them in the packages whenever I need them. Perfect!

But troubles begin when you start using package configurations. Mostly i create package configurations (dtsConfig files) and use the environment variable for indirect use. When you start changing the package configurations files by hand you will receive an error when opening the specific package who uses this configuration: “Synchronize connection strings” (Grrr). The old connection string is the one changed in the file and the new one is my old connection string. Awkward! The other way around seems more logic to me.

Some info from BOL:

“When you add a data source to a package by using SSIS Designer, SQL Server Integration Services adds a connection manager that can be resolved as an OLE DB connection at run time, sets the connection manager properties, and adds the connection manager to Connections collection of the package. To associate the data source and the connection manager, Integration Services sets a property, DataSourceID. Because a data source is not created within the context of a package, multiple packages can use the same data source”

I searched the internet for a solution for this but I can’t find nothing better than: “Don’t use datasources”. Great! This is not the answer i want but i didn’t find any better solution so here is my best practice:

1. The capability of using datasources throughout your project is very handy. So at first you could create and use them until your project reaches a maturity level. After this delete the datasources.

2. Create different sets of configurations for Development, Test, Acceptance and production.

3. Use batch files for creating the environment variables (SETX XXXX_Config D:\XXXX\Configurations\XXXXXX.dtsConfig /M) for development, test, acceptance and production (DTAP).

Greetz,
Hennie

performance improvement options.

For a customer of mine I’ve developed a star model with in the middle a fact table and a couple of dimensions. Some of the dimensions are small, some not. The dimensions which are small reads data from CSV files and the large dimension reads from XML files. I created ETL processes with SSIS which reads from the XML, transforms the information in the fact table and a couple of dimensions.

One of the dimensions is a customer table. The customer cannot uniquely identified by any means what so ever, unfortunately. This will place a burden on the performance. This dimension table will grow with the same pace as the fact table (very rapidly changing dimension;-)). The number of transactions will be in the order of 20 a 30 million a year.

I already took the following measures (or proposed to):
• Creating integer surrogate keys.
• Creating (clustered) indexes on the surrogate keys of the dimension tables.
• Creating indexes on columns used to join other tables (foreign keys).
• Creating (covering) indexes on high selective fields (0-20% return of rows from the total)
• Creating indexes on fields used by ORDER BY clauses.
• Creating multiple filegroups for the indexes, logs and data for spreading them over RAID configurations.

But there will be a need for future improvements in performance and this blog is about the different options available for further performance improvement. The base for this is SQL Server. Hardware and network improvement is not taken into account. Also maintenance scripts, profiler, index tuning wizard are left overs for future blogs. So what will be design options for further improvement?

Further improvement in design could be found in the following options:

1. I’m thinking about using the Checksum Function (yes, I know it’s not perfect) to aid searching for specific values in some large dimensions. Creating an index on this column could be fast. So during ETL process I will create for highly used columns multiple checksum columns in the customer table. When the front end is querying the table it will convert the fields into checksum values too. Using this in a covering index could be fast.

2. Table partitioning. Table partitioning means nothing but splitting a large table into smaller tables so that queries has to scan less amount data while retrieving (Divide and Conquer).

• Horizontal partitioning. So it’s expected to have 20 a 30 millions of rows per year for my customer. So my advice could be for splitting the fact and some dimensions into a yearly partition schema. A base for this could be date column or an identity column (the surrogate column).
• Vertical partitioning. Suppose, we have seen that some columns in the table (fact or dimension) are frequently used and some or infrequently used. We can split this table into frequently used table and a less used table. Each table has a subset of the former table. This should be done carefully because when fields are needed from both tables serious performance degradation has to be taken into account.

So, putting them on separated disk will enhance performance.

3. Partitioned views. A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view (BOL). This could be an option when one server is not enough anymore. If you have multiple servers it’s possible to approach this in a federated way. The view is used as if it is from one server but underneath the view is based from different servers or virtual environments.

This is it for now.

Performance management

Performance management seems to be the new buzzword in the field of business intelligence. Performance management is about measuring your business processes (% of wasted materials, revenue, etc), comparing the outcome of the business processes with your desired results (10 % wasted materials, 100 K revenue) and trying to adjust the business processes with performance measures (more efficient use of materials, sales promotions).

The above example sounds very analytical or like a system approach as thermostat works: temperature-sensitive automatic device that regulates and keeps the temperature within determined limits; If limits are exceeded it will adjust the temperature. As i said this is very system driven and this could be adapted in the years of industrialization but now organizations are more organic, needs more flexible and creative. Does plain performance management really motivate people? As Frank Buytendijk states in his book Performance leadership organisations could recieve undesired results by just adopting performance management without thinking what effect it could have on human behaviour. He distinguish two basic types of unintended behaviour:

• People impacting on measurement. People are trying to play the numbers so that they don't have to alter their actions.
• Measurement impacting on people. The metrics put in place drive dysfunctional behaviour. This is what i call "the system drives the people" and is defined by Frank Buytendijck as 'Measure fixation'. The numbers will become more important than running a successfull business.

So what is a proper performance management system influencing the right behaviour? That will be input for a  blog for later.

Greetz,

Hennie

Goal of BI

In a couple of days i've a small presentation for a starting up business unit Business intelligence at the Future group and some people are presenting their ideas, definitions, methodlogies, frameworks, etc. I'll be talking about what Business Intelligence should be, what are the goals of BI, etc. Just a plain presentation in 10 minutes that will feed some discussion. So i started googling 'Business Intelligence': 38.100.000 hits. Hmmmm quite a lot. I searched some Wikipedia's, sites etc that would give some direction for a presentation. After a half a hour (and years of experience off course;-) ) i concluded: there's no red line in Business Intelligence. Discussion about Business Intelligence spreads all kind of directions. So i decided that i try to tell what Business Intelligence is from my point of view. Here is my opinion.

So what is Business Intelligence all about? I think there a couple of goals of Business Intelligence:

• Decision support. This is the most familiar goal of business intelligence. Helping the business making decisions better. What is a better decision? Good question. Some cultures in organisations will enhance decision making with a good ground. Some won't. For some organisations a highly skilled BICC will have great impact and for some it won't. Sure it also depends on the information shown. A Red traffic light blinking on your screen make sure it will attract your attention. So information ergonomics is important here. Principles of colour, Gestallt, use of UI components are important. In a later blog i will go deeper into that.
• Keeping record of what has been happened in the past for purposes like learning (why did this happen?), auditing (did we do the right thing?) and trends (where are we going?).
• Influencing the behaviour of people (Frank Buytendijk). Currently reading his book 'Performance leadership' and i will go into detail later on.

Ok that  was it for now..

Greetz,
Hennie