Conception de l'entrepôt de données – comment concevoir des mesures liées au time, telles que les 7 derniers jours, etc.

Je travaille à la création d'une database de type rapport / entrepôt de données. Nous avons actuellement une table qui a des données au grain de l'hôtel (c'est-à-dire HotelID plus beaucoup de mesures, y compris des mesures comme Last7DaysGross, Last28DaysXXX, etc.).

Je pense qu'il serait préférable de passer à une table de faits qui est au grain Hotel / StayDate. Cependant, le regroupement sur l'HotelID et l'inclusion de mesures liées à la date telles que Last7DaysGross doivent être très performants.

Quel genre de structures travaillerait ici? Je ne pense pas que je serais capable d'utiliser des vues indexées comme je l'avais espéré, à cause des multiples ressortingctions (pas de sous-requêtes, etc.) Pour avoir une performance raisonnable, devrai-je créer une nouvelle table au Niveau de l'hôtel (agrégé depuis le niveau HotelStayDate?) C'est le niveau auquel les gens vont le plus souvent interroger. Ai-je besoin de créer des champs tels que Last7DaysGross? Cela ne semble pas être un bon design, mais j'ai du mal à en find un autre.

Désolé cette question est un peu vague. Y a-t-il autre chose qui me manque ici? Je sais que le plus souvent ces mesures liées à la date seraient effectuées au niveau frontal (c'est-à-dire dans un outil tel que Business Objects). Cependant, pour ce projet, nous aurons besoin de l'avoir dans la database.

merci, Sylvia

MODIFIER:

Merci pour tous les commentaires réfléchis! J'ai accepté David Marwick réponse à cause de son idée d'avoir une dimension de date étendue. Cette idée ne m'avait même pas traversé l'esprit, et cela me parait intéressant d'essayer.

En développant un peu les pensées de David Marwick, j'ai eu cette idée. Je pourrais essayer et voir comment cela fonctionne réellement:

DateDimension DateKey DateKeyBeginLast28Days DateKeyEndLast28Days Fact DateKey GrossTransactions 

Puis, lors de l'interrogation:

 Select DateKey ,SumLast28Day = sum(GrossTransaction) from Fact join DateDimension on Fact.DateKey >= DateDimension.DateKeyBeginLast28Days and Fact.DateKey <= DateDimension.DateKeyEndLast28Days group by DateKey 

Je pense que votre design d'avoir une table au [Hotel, Date] grain puis rouler dans l'Hôtel sonne bien. Comme le souligne Damir, vos requêtes de lecture restnt simples et il est facile d'append / de supprimer des mesures globales (en gardant à l'esprit que c'est généralement une mauvaise idée de concevoir autour des exigences que vous pourriez avoir à l'avenir).

Pondlife fait aussi de bons points. Vos exigences qualitatives peuvent dicter la faisabilité d'une table agrégée, par exemple la fréquence à laquelle le système doit être mis à jour (quotidien, horaire, 15 minutes, time réel?), La précision des mesures (les users ont peut-être besoin d'un sens approximatif du bon fonctionnement de chaque hôtel), combien il est coûteux de lire datatables de transaction source, comment datatables de la transaction source sont disponibles à long terme (sont-elles archivées), etc.

Si vous choisissez d'append une table de faits de grain [Hôtel, StayDate] et ne maintenez pas un agrégat alors peut-être vous pouvez explorer quelques astuces dans vos dimensions pour gagner du time. Peut-être quelque chose comme une dimension de date de 7 jours contenant [date, date_in_last_7_days] (donc 7 loggings pour chaque date) dans le cas où une jointure directe vs une plage interrogeant les 7 derniers jours vous fait gagner du time. Ce pourrait être un exemple stupide, mais quelque chose dans ce sens. Les dimensions de date sont petites.

Enfin, considérez les optimizations matérielles comme le déplacement des tables dans la memory (en particulier les dimensions ou les tables de faits non gigantesques) si vous avez besoin d'améliorer les performances.

Les tables de faits agrégées sont entièrement acceptables dans les entrepôts de données.

Si vous ne l'avez pas déjà, je recommand le livre ci-dessous

DW Toolkit

Ici, Kimball déclare que la pré-agrégation des tables de faits dans les tables de faits agrégées est correcte, mais elle indique qu'elles devraient être similaires à la table de faits "de base" à un niveau agrégé.

L'introduction de champs de rapport que je soupçonne devrait être dans votre outil de rapport frontal ou Cube Viewer.

Comme le dit David, il n'y a rien de mal à pré-agréger certains totaux (pendant le process ETL) afin de faire tourner plus rapidement les requêtes importantes. C'est une technique courante même dans les bases de données opérationnelles et cela a beaucoup de sens dans les entrepôts de données si vous savez que certains agrégats sont fréquemment utilisés.

Donc, vous pouvez certainement créer une table appelée FactHotelRevenueSummary (ou tout ce qui correspond à votre convention de nommage existante) avec HotelID, Last7DaysGross, Last28DaysGross et autant d'autres agrégats que vous le souhaitez.

Les principaux points à considérer d'abord à mon avis sont les suivants:

  • La pré-agrégation est vraiment nécessaire en raison de problèmes de performance observables, c'est-à-dire que vous ajoutez de la complexité à la database pour résoudre un problème réel et non parce que vous avez le vague sentiment que cela pourrait être utile
  • Votre process ETL a des vérifications pour s'assurer que datatables agrégées correspondent exactement aux données 'brutes', sinon vous obtiendrez des nombres différents selon la table de faits que vous interrogez et qui a un effet très négatif sur la confiance de l'user

Cela dépend, une requête habituelle (pour les 7 derniers jours) ressemblerait à quelque chose comme

 select HotelName , sum(SaleAmount) as Sales from factSale as s join dimDate as d on d.DateKey = s.DateKey join dimHotel as h on h.HotelKey = s.HotelKey where DaysAgo between 1 and 7 group by HotelName ; 

Toutefois, supposons que vous ayez un rapport qui inclut les sums en cours (sur les périodes) et leurs modifications. La layout du rapport peut ressembler à ceci:

 | Date | 1-Day | Change-1-Day % | 7-Day | Change-7-Day % | 28-Day | Change-28-Day | 90-Day | Change-90-day % | 

Pas si simple maintenant. Ainsi, il est beaucoup plus facile de créer une table agrégée avec des champs pré-calculés pour des périodes standard et d'exécuter une requête sur celle-ci.

Ainsi, la table d'agrégat (de fait) peut ressembler à quelque chose comme

 factRunningSum ---------------------------- DateKey integer (PK) HotelKey integer (PK) Sale_1_Day decimal(19,2) Sale_7_Day decimal(19,2) Sale_28_Day decimal(19,2) Sale_90_Day decimal(19,2)