Universit Joseph Fourier / PolyTech Grenoble / RICM3/ 2003-2004 / SID / Examen1/ 1 Universit Joseph Fourier PoIyTech GrenobIe Anne Universitaire 2003-2004 RICM 3 Systmes d'Information DcisionneIs Examen Concepteurs :Didier DONSEZ Date :Mars 2004 Dure :2 heures Remarques : Calculatrice RECOMMANDEE et tout document autoris Conseil :Lire le sujet jusquau bout. Annexe : Rsultats du benchmark TPC/H 0RGpOLVDWLRQGpFLVLRQQHOOHGHVDFWLRQVGHWpOpVSHFWDWHXUVLQWHUDFWLIV+ CORRECTION LoprateurBreeFox1proposesesabonnsunboiterrouteurADSLquigroupeunaccs IP,letlphoneetunbouquetdechanesdetlvisioninteractives.Nousnenous intresserons qu cette dernire fonction dans notre entrept de donnes. Les chanes de tlvision proposes par BreeFox dans son bouquet sont des chanes nationales et des chanes page. Seulement, les interruptions publicitaires (des chanes nationales et page)peuventtrepersonnalisesenfonctionduprofildufoyer(oudesadultesdufoyer quand le contrle parental2 est dverrouill). Le tlspectateur peut ragir (interactivement) de plusieurs manires aux missions et aux publicits quil reoit au moyen de sa tlcommande (ie ]DSHWWH: A tout moment, il peut zapper vers une autre chane A tout moment, il peut teindre son poste Pendantunepublicit,ilpeutzapperlapubsuivantesansattendrelafindela publicit en cours (la dure de visualisation est importante). Pendant une publicit, il peut demander plus dinformation sur le produit prsent (la dure de visualisation est importante) puis revenir lmission en cours . 1 Toute ressemblance avec une socit existante est purement fortuite ! 2 Pour viter que les enfants du foyer apprennent trop jeunes la Biologie ! Universit Joseph Fourier / PolyTech Grenoble / RICM3/ 2003-2004 / SID / Examen2/ 2 Lentreptdedonnesestcentrsurlesactionsdutlspectateur.Lobjectifdecetentrept est de profiler au plus prcis le foyer (ou plutt le tlspectateur qui dtient la tlcommande) afin de maximiser la demande dinformation sur les produits prsents par les publicits. En effet, loprateur peroit plus dargent de la part de lannonceur quand le spectateur demande plus dinformation au moment au lannonce est passe ! Le schma de lentrept est constitu des tables suivantes (les cls primaires sont soulignes) 'DWH(ClDate,Anne,Mois,JourDeMois,JourDeSemaine,TrancheHoraire,Heure,Minute, DrapeauVacances, Drapeau Manisfestation) )R\HU(ClFoyer, NomAbonn, AnneNaissanceAbonn, Rgion, Dpartement, District, Ville, Quartier, SituationFamille,RevenuFoyer,CatgorieSocioProfessionnel, SousCatgorieSocioProfessionnel, DomaineActivit, NombreAdulte, NombreEnfant) (PLVVLRQ(ClEmission, Chane, DateDbut, DureSeconde, TypeEmission, Catgorie, Annonceur) TypeEmission= Programme, AnnoncePublicitaire Catgorie= Mto, Journal, Varit, Jeu, JeuAvecPognion, Film, Foot, Annonceur=seulement pour les annonces publicitaires $FWLRQ(ClDate,ClFoyer,ClEmission,TypeAction,DureAction,DureRestante, DrapeauCtrlParentalDverrouill) TypeAction= ZapAutreChane , Eteindre , Allumer , ZapPubSuivante , Demande Information , Passif (ie pas daction sur la zappette) 'XUpH$FWLRQ=Durecouleentreledbutdelmissionetledbutdelaction.4XDQG7\SH$FWLRQHVWGLIIpUHQWGH'HPDQGH,QIRUPDWLRQ'XUpH$FWLRQ = Dure coule entre le dbut de lmission et la fin du temps supplmentaire dinformationavantquelespectateurregardeavantderetournersonmission,4XDQG7\SH$FWLRQHVWpJDOGH'HPDQGH,QIRUPDWLRQDureRestante=Durerestanteentrelactionetlafindelmission(=0siTypeAction= Passif DrapeauCtrlParentalDverrouill indique le contrle parental a t dverrouill Rtro-Conception Q1: Quelle est la table de fait dans cet entrept ?. Justifiez en 2 lignes ! Action (car au centre des dimensions, attributs additifs ou numriques) Q2: Que pensez vous de lattribut TypeAction de Action ? Nest pas une mesure ! Cest une dimension dgnre Q3:Avotreavis,ilyatildesdimensionsdouteusesdanscetentrept ?Rappelezla dfinition et justifiez en 3 lignes Foyer !. Q4:Donnezlesnouvellestablessiondcidedediminuerlatailledela table Foyerpar une mini-dimension dmographique Universit Joseph Fourier / PolyTech Grenoble / RICM3/ 2003-2004 / SID / Examen3/ 3 OncreunetablededimensionDemographieaveclesattributsdeFoyer(Rgion, Dpartement,District,Ville,Quartier,RevenuAssur,RevenuFoyer, CatgorieSocioProfessionnel,SousCatgorieSocioProfessionnel,DomaineActivitoudautres ventuellement) On supprime ces attributs de Foyer On ajoute une cl de mini-dimension la table de fait et la table de dimension Foyer Dimensionnement Q5: Donnez le nombre de faits prsents dans la table de fait. Nombre de foyers abonns3 Millions Nombre de actions par foyer et par heure10 Un foyer regarde la tlvision 320 jours par an, 5 heures par jour Nombre de tranche horaire24 Nombre dannes3 Taille des cls 4 octets Taille des attributs numriques4 octets Taille des attributs discrets (comme les types !)1 octet Taille des attributs boolens (comme les drapeaux !)1 octet Nombre dactes=3.000.000*10*320*5*3= 144 000 000 000 actions ou enregistrements Donnez la taille dun enregistrement de la table de fait ? (4*3 cls + 4*2 attributs numriques + 1*1 attributs type + 1*1 attributs boolens)=22 octets par fait Donnez la taille (en Octets) de stockage de la table de fait. Taille de la table de fait= 3168000000000octets soit 3 To Configuration Matrielle Q6:ApartirdesrsultatsdubenchmarkTPC/H (http://www.tpc.org/tpch/results/tpch_results.xls)donnenannexe,choisissezla configuration matrielle et logicielle (complte) qui est la plus adapte votre infocentre pour une performance minimale de 25000 QphH ? Quels sont vos critres de choix ? Remarque : vous ngligerez la taille des tables de dimensions. On choisira un SF=3000 (3000Go) HPHP Integrity Superdome Enterprise Server23000452481094922070US $OracleDatabase10gEnterpriseEditionHP UX 11.i 64-bit Rapports Q7: Donnez la requte SQL qui donne le temps cumul pour chaque type dmission et pour chaque tranche horaire SELECT D.TrancheHoraire, E.TypeEmission, SUM(DureeAction) AS TempsCumul FROM Emission E JOIN Action A USING (ClEmission) JOIN Date D USING (ClDate) GROUP BY D.TrancheHoraire, E.TypeEmission SORT BY D.TrancheHoraire ASC, TempsCumul DESC Q8:DonnezlarequteSQLquidonneletop10destypedmissionlesplusregardesen temps cumul SELECT E.TypeEmission, SUM(DureeAction) AS TempsCumul FROM Emission E JOIN Action A USING (ClEmission) GROUP BY E.TypeEmission SORT BY TempsCumul DESC TOP(10) Universit Joseph Fourier / PolyTech Grenoble / RICM3/ 2003-2004 / SID / Examen4/ 4 Q9:Donnezlerapportmensueldeprogressiondunombrededemandedinformation(ie TypeAction = Demande Information ) et de la dure associe regarde SELECT D.Anne, D.Mois, COUNT(*) AS NombreTotal, SUM(A.DureAction) AS DureTotale FROM Action A JOIN Date D USING (ClDate) WHERE A.TypeAction= Demande Information GROUP BY D.Anne, D.Mois SORT BY D.Anne ASC, D.Mois ASC Q10:Donnezlerapportprcdentmaisavecunemoyenneglissanteavecles2mois prcdents. SELECT R.Anne, R.Mois, AVG(R.NombreTotal) OVER ( ORDER BY D.Anne D.Mois ASC ROWS 2 PRECEDING) AS MoyGlissanteNombreTotal, AVG(R.DureTotale) OVER ( ORDER BY D.Anne D.Mois ASC ROWS 2 PRECEDING) AS MoyGlissante DureTotale FROM( SELECT D.Anne, D.Mois, COUNT(*) AS NombreTotal, SUM(A.DureAction) AS DureTotale FROM Action A JOIN Date D USING (ClDate) WHERE A.TypeAction= Demande Information GROUP BY D.Anne, D.Mois SORT BY D.Anne ASC, D.Mois ASC ) R GROUP BY R.Anne, R.Mois SORT BY R.Anne ASC, R.Mois ASC Conception physique Q11 :RappelezleprincipedindexB-TreeetceluidunindexBitMap ?Lequelestil gnralement mieux adapt aux entrepts de donnes ?(en 10 lignes) Q12 : Pourquoi est il intressant d'avoir des enregistrements de taille fixe pour la table de fait (justifiez en 5 lignes) On peut y appliquer des indexs bitmaps ! Q13 :Estiljudicieuxd'utiliserlesindexsbitmapsurlesfichiers(stockagephysiquedes tables) dont les enregistrements ont un taille variable (justifiez en 5 lignes) Non, car pas daccs alatoire possible. Q14 : Pourquoi est il intressant de partitionner la table de fait sur des disques diffrents ? (2 raisons justifier en 8 lignes) 1.pour les perfs 2.pourlabusinesslogiquededureedevie(validite)desdonnees.Permetdepurgerles donnes anciennes (hors du champs des tudes)