Data Warehouse et intégration

Data Warehouse et intégration

“Data integration involves combining data residing in different sources and providing users with a unified view of these data”.

Si on considère que pour couvrir tous ses domaines métiers, une entreprise a souvent besoin de plusieurs systèmes opérationnels (CRM, Finance, Logistique, HR, …). Il m’est difficile de croire qu’une plateforme décisionnelle puisse exister sans volonté de reconstituer dans un entrepôt toutes les composantes des différents cycles de vie et processus.

Je pense qu’il n’y a pas de Data Warehouse sans intégration.

Si on compare toutes les méthodologies de data modelling pour la couche Data Warehouse (B.Inmon, R.Kimball ou D.Lindstedt), on retrouvera cette idée comme concept commun et central. Tout comme l’historisation, l’homogénéisation ou la non-redondance des données.

DWH

Prenons l’exemple de Kimball, il n’a pas inventé le modèle en étoile (Nielsen dans les années 70) mais bien le moyen de construire un Data Warehouse en utilisant le modèle en étoile. Ceci avec comme concept central, les « conformed dimension » (en vert sur le schéma), le résultat de l’intégration des données depuis des sources hétérogènes dans une structure unique.

La « vraie vie » m’a démontré que ce type de Data Warehouse existe et qu’il n’est clairement pas impossible à mettre en place.  Il est vrai que la construction des dimensions va être un peu plus compliquée par rapport à du «Reporting opérationnel amélioré» (amélioré grâce aux hiérarchies) qui va simplement construire un Data Mart indépendant (ou plusieurs Data Warehouse ????) par système source et des dimensions différentes (client, produit, employé) par source opérationnelle.

Le coût des « Conformed Dimensions » est vite récompensé car il permet deux choses qui me semblent essentielles pour l’utilisateur:

  • Avoir les attributs, provenant de tous les systèmes autour d’un concept regroupés, et donc la multiplication des possibilités d’analyse
  • La possibilité de créer des indicateurs complexes entre tables de faits

Cette reconstitution révèle souvent des problématiques d’intégration entre systèmes opérationnels, et donc de potentiels problèmes dans le suivis des processus métiers. C’est selon moi une autre valeur ajoutée que doit apporter la mise en place d’une plateforme de business Intelligence, le reporting des erreurs et différences entre système opérationnels dans le but d’améliorer l’ensemble des interactions systèmes au niveau qualitatif. Ce reporting ne représente pas une grosse charge de travail, il ne contient ni plus ni moins que les tests effectués lors du développement des objets intégrés du Data Warehouse.

Je pense qu’il n’y a pas de besoin qui justifie de ne pas intégrer les données, car si je pars sur un mode de « reporting opérationnel amélioré », le chemin vers une vision globale sera long et fastidieux. Si on regarde au niveau des coûts projets, les quelques jours supplémentaires de développement passeront sans trop de problème en cours de projet. Autant armer suffisamment la plateforme décisionnelle en lui permettant de couvrir plus de besoins, il me semble que c’est le compromis gagnant.

Les « Conformed Dimensions » peuvent être complexes à maintenir et à faire évoluer. Cette complexité s’accroit avec le nombre de sources qui les alimentent. Il est évident que si l’entreprise ne possède qu’un seul système source pour gérer toute son activité (très rare), il est aisé de les reconstituer.

C’est exactement à ce moment dans la réflexion, qu’intervient le Data Vault ! Et c’est précisément, ce critère qui me semble être le plus déterminant au choix du Data Vault dans une plateforme BI. Plus les sources sont nombreuses et l’intégration incertaine, plus le Data Vault va être intéressant.

La possibilité qu’offrent les satellites (plusieurs satellites par HUB) dans le Data Vault, facilite l’intégration et minimise les impacts. Le nombre de tables induites par le modèle peut effrayer mais , c’est cet éclatement par type de données et fréquence de changement qui donne sa flexibilité au modèle.

Tout ceci pour dire que commencer un projet en excluant toute intégration me semble erroné et surtout une manière de limiter la croissance de la plateforme Bi dès sa naissance. Si le reporting Bi n’offre pas cette possibilité, il y a de grande chance que le reporting décisionnel réside dans une sheet Excel construisant cette réconciliation de manière aléatoire et non-contrôlée. Ce qui personnellement me fait penser que probablement la plateforme décisionnelle est un échec.

 

MDX : YTD of the previous year on a specific hierarchy

This formula can avoid the hour spent to Search for it! This one directly calculate the aggregated members.

aggregate

({

openingperiod([Date].[Calendar Soc].[Day],ancestor([Date].[Calendar Soc].CurrentMember,[Date].[Calendar Soc].[Year Soc]).prevmember)

:

ClosingPeriod( [Date].[Calendar Soc].[Day],cousin([Date].[Calendar Soc].CurrentMember,ancestor([Date].[Calendar Soc].CurrentMember,[Date].[Calendar Soc].[Year Soc]).prevmember))

},[Measures].[…])

A duplicate attribute key has been found when processing SSAS Cube Dimension

I had a strange issue at a customer on a SSAS cube.
I was trying to migrate the cube from dev environment to test, the processing of the cube starts to fail with this error message:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
The thing is that in dev with the same data it was succeeding!!!!!!!
The error was coming from the cube collation, it was setup to accent insensitive and the source database was in Accent sensitive.
Here is the desription of the bug:
To create the agrregation of the cube, SSAS send “Select distinct” queries to the database, as the source database is in accent sensitive, here is the result:
JàRnïpâ
JaRnïpâ
–> those 2 entries are different for “select distinct” result with the option accent sensitive enabled!!
But for the cube, as it is in incensitive, those records are considered as the same!!!!
This is why you got back duplicate attribute error :
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Client’, Column: ‘Name’, Value: ‘JàRnïpâ’.
Damned!

Using .xlsx sources with SSiS (64 bit) on a 64 bit system

The only way I’ve found to run an SSIS data flow with an .xlsx as source and without changing the option ’64 runtime’ to false is to use as source an “OLE DB source” component in the data flow with this query:

select * FROM OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’

, ‘Excel 12.0;Database=C:\Hello.xlsx’

, [Sheet1$]

)

By the way, to make it works, you have to install the ACE drivers(for 64 bit) from microsoft website on the machine where you execute your package. 🙂

Storing Periods or a split by day of periods in a Data Warehouse

I had this issue at the client.

Imagine that you have to model a datwarehouse for an hotel group.

In the operationnal system, you get entries like this:

From To Name Amount journey
1/01/2010 10/01/2010 Ralph Kimball 1800 9 days
3/06/2012 15/07/2012 Jose garcia 20976 44 days

My first error was to store the data like in the operationnal system and when the client asked me, statistical on the room occupation by month, I was not able to found the correct value because I had only 2 links with the dimension date that were From_Date_ID and To_Date_ID.

The customer ‘Ralph kimball’ is not causing any problem for statistics by month but customer ‘Jose garcia’ is :

I have to calculate the occupation rate by month:

– For Customer Ralph Kimball there is no problem, it’s 9 for January

– For Customer José Garcia, I have to split the period like this : 30 days in June and 7 days in July! And as you see in the table bellow, I only have one value for the journey that is 44 and 2 dates linked to it (from and to). So If I Filter on from date for january, I will get 44 and same for To date!!!! and of course, that’s not what we want.

So As I don’t have the split by month, except by doing very complicated calulations, it’s not possible to get the correct result. It means that if the customer stays a year, the amount of days he stays will be summed to month (from–> begin of the period) or to the month (to –> end of the period).

The solution of this architecture issue is to change the granularity of your fact table and not to store periods in it but a split of each journey by day.

Then you will have as many rows in your data warehouse as days in each journey.

The query of the split will be something like this:

select From, Date, To, Name , Amount

from journey inner join

Date_dimension on From <= Date and Date >= To

This will be the result for the first journey of the person ‘Ralph Kimball’:

From Day To Name Amount days nights
1/01/2010 1/01/2010 10/01/2010 Ralph Kimball 0 1 0
1/01/2010 2/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 3/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 4/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 5/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 6/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 7/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 8/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 9/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 10/01/2010 10/01/2010 Ralph Kimball 200 1 1

SQL Server Convert from varchar to datetime, datetime format

Here are some queries ready for varchar to datetime or datetime to varchar conversion:

Think that a datetime is a fix format that depends of the date format  in your Windows OS and if you want to customize the way to show a datetime, use a varchar. 🙂

Varchar   format From   Varchar –> datetime from   date time –> ‘Varchar’
mon dd yyyy hh:mmAM convert(datetime,’Varchar’,100) convert(Varchar,’Date’,100)
mon dd yy hh:mmAM convert(datetime,”Varchar”,0) convert(Varchar,’Date’,0)
mm/dd/yy convert(datetime,’Varchar’,1) convert(Varchar,’Date’,1)
yy.mm.dd convert(datetime,’Varchar’,2) convert(Varchar,’Date’,2)
dd/mm/yy convert(datetime,’Varchar’,3) convert(Varchar,’Date’,3)
dd.mm.yy convert(datetime,’Varchar’,4) convert(Varchar,’Date’,4)
dd-mm-yy convert(datetime,’Varchar’,5) convert(Varchar,’Date’,5)
dd mmm yy convert(datetime,’Varchar’,6) convert(Varchar,’Date’,6)
mmm dd, yy convert(datetime,’Varchar’,7) convert(Varchar,’Date’,7)
HH:mm:ss convert(datetime,’Varchar’,8) convert(Varchar,’Date’,8)
mon dd yyyy hh:mm:ss:fffAM convert(datetime,’Varchar’,9) convert(Varchar,’Date’,9)
mm-dd-yy convert(datetime,’Varchar’,10) convert(Varchar,’Date’,10)
yy/mm/dd convert(datetime,’Varchar’,11) convert(Varchar,’Date’,11)
yymmdd convert(datetime,’Varchar’,12) convert(Varchar,’Date’,12)
dd mon yyyy HH:mm:ss:fff convert(datetime,’Varchar’,13) convert(Varchar,’Date’,13)
HH:mm:ss:fff convert(datetime,’Varchar’,14) convert(Varchar,’Date’,14)
yyyy-mm-dd HH:mm:ss convert(datetime,’Varchar’,20) convert(Varchar,’Date’,20)
yyyy-mm-dd HH:mm:ss.fff convert(datetime,’Varchar’,21) convert(Varchar,’Date’,21)
mm/dd/yyyy convert(datetime,’Varchar’,101) convert(Varchar,’Date’,101)
yyyy.mm.dd convert(datetime,’Varchar’,102) convert(Varchar,’Date’,102)
dd/mm/yyyy convert(datetime,’Varchar’,103) convert(Varchar,’Date’,103)
dd.mm.yyyy convert(datetime,’Varchar’,104) convert(Varchar,’Date’,104)
dd-mm-yyyy convert(datetime,’Varchar’,105) convert(Varchar,’Date’,105)
dd mmm yyyy convert(datetime,’Varchar’,106) convert(Varchar,’Date’,106)
mmm dd, yyyy convert(datetime,’Varchar’,107) convert(Varchar,’Date’,107)
mm-dd-yyyy convert(datetime,’Varchar’,110) convert(Varchar,’Date’,110)
yyyy/mm/dd convert(datetime,’Varchar’,111) convert(Varchar,’Date’,111)
yyyymmdd convert(datetime,’Varchar’,112) convert(Varchar,’Date’,112)
yyy-mm-ddThh:mm:ss convert(datetime,’Varchar’,126) convert(Varchar,’Date’,126)