Maand: november 2019

New multi layer datawarehouse

We are currently discussing a new structure for our data warehouse. The current situation is that we have roughly 2 layers. Staging and the layer that contains partially denormalized tables, the T or end tables. I always say that the T stands for Terminus, Latin for “Endpoint” but I now believe that this means “limit.” …My latin is no longer what it used to be … 😉 We have had a Dim fact model here and there but never implemented it consistently. We use these T tables for ad hoc questions and for building reports. Of course it is true that one T table can feed the other. This is currently difficult for newcomers to follow. The current setup is around 15 years old. And is fine but is not transparent enough for the business and external accountability.

In detail, a ‘T table’ is therefore a commonly used combination of staging tables joined together, for example, we have a T_production daily that contains all production. Wide in number of columns and with many rows. Nowadays the servers have no more problems with this. YES!! 1 table to rule them all and in darkness bind them… (Ban of the Ring) That’s the idea.

Current levels datawarehouse (very simple version)=>

Proposed multilayer model=>

Staging

Staging level seems obvious to me, this contains a copy of the production databases. There is a desire to do this incrementally and / or in real time. I leave this aside for a moment.

Business layer

We have a discussion about this. The proposal is very similar to what we are doing now in the T tables layer, only redesigned. This layer is seen as a preparation for the dim/fact layer that is more intended for the end user.

People from our department will be able to directly work on the business layer because our experience shows that for SQL people, dimfacts are not really pleasant schema’s to work with. Nevertheless, we wonder whether we should do this, since this is not mentioned in the standard holy bibles….Kimball and others…

Dim / fact layer (self service)

The source system of production is very complicated. I also worked with competing systems and they were a lot easier. It strikes me that people with years of background in this system when they start modeling dimensionally, they tend to follow that complexity. What then arises are fact tables with 15 dimensions. My proposal is to reduce this as much as possible. No less than 5 dimensions per facttable 😉

Unfortunately, a system to be designed and accepted by the project members depends on a compromise between the members. I do not yet know which way it is going. The tendency is now to avoid disagreements so after repeating opinions several times they are not repeated again. But if the goal is to build something better, everyone should talk and listen to each other. Welcome to the human factor … I have experienced a project several times that would rebuild the data warehouse and they all fail by human factors. But not yet!!!

Staged rollout AlarmMeRSI

In my normal work as bi analist I have the experience of maintaining some databases with a userbase of 10 000 people. Disruptions are deadly for users’ confidence. With large user populations with a shocked trust you constantly receive questions and comments that can take up a large part of your daily working time if things go wrong. Of course I prefer not to have that at all in my private time. I do Android in my private time…and other things….

At work to reach a large group of users and know the wishes of the business is at least physically easy. It is clear who is approachable, you know where the management is and where your users are. With google play, that is of course impossible for a beginner to know. You know nothing at all outside your own circle.

Translated to the Android platform, I am assuming that a negative experience is far more important. After all, a user does not have to use your app, so negative experiences count more than positive ones. For the time being I opt for a staged rollout in the countries with a Dutch-speaking community plus Denmark because some old friends live there. In order to have room for handling issues and to see if there is any interest at all, I posted on Facebook and Linkedin that my app has been launched in recent days. At work, I also made this known to the group of close colleagues in a relatively small circle for now.

Android App in play!

Last week Archimedes launched his first app in Google play! AlarmMeRSI is an app which we use to prevent RSI from happening (repetive strain injury, for more information go here .)

It is a periodic alarm after which you can for instance stand up from behind your desk and do your exercises. (or get yourself some coffee)

This is the link to the app in google play store: Dutch version or the English version