ADTPArchitecture Frameworks SENG 691P
Popular in Course
verified elite notetaker
verified elite notetaker
verified elite notetaker
CHEM 333 001
verified elite notetaker
verified elite notetaker
BIOL 160 - 018
verified elite notetaker
Popular in Information technology
This 46 page Class Notes was uploaded by Jaydon Gorczany on Saturday September 12, 2015. The Class Notes belongs to SENG 691P at West Virginia University taught by Staff in Fall. Since its upload, it has received 12 views. For similar materials see /class/202680/seng-691p-west-virginia-university in Information technology at West Virginia University.
Reviews for ADTPArchitecture Frameworks
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/12/15
Data Warehousing Jeffrey T Edgell Class Schedule As aresult ufafew cuncems regarding the class schedule there is nu eissstemgnt and me prepesed schedule is being altered Nutethatthepmpusedscheduleis subjecttu change Ifyuu have cuncems Dr quesnuns abuutitletme knuw and we can adjust Data Warehousing 101 Model concepts 7 Fact tables A table containing multiple measurable descriptors relating to a speci c area orbusiness Each fact can be viewed calculated and aggregated against various derming areas orthe business time geography customer Data Warehousing 101 Model concepts 7 Dimension Tables Retains information product description geography description customer description that is descriptive and remains moderately constant over me Data Warehousing 101 Data Warehouse Modeling 7 Special modeling techniques must be applied to provide rapid response of queries on large Volumes of data 7 OLTP systems are built with update operations in mind resulting 1n normalization and grea reduced browse performance Data Warehousing 101 Common data model techniques are as follows 7 star schema 7 snow ake 7 fact constellation 7 relational Data Warehousing 101 Sample Star Schema Mm lel Data Warehousing 101 Sample Snnw ake Mm lel Data Warehousing 101 Sample Fact Cnnslallztinn Mm lel Data Martng 101 Data marting is A mctiunal segment of an enterprise restricted for purposes of security locality performance or business necessity using modeling and information delivery techniqum idmtical to data wareh quotusing Data Martng 101 Why build a data mart 7 Allows an organization to visualize the large but focus on the small and attainable 7 Provides a platform for rapid delivery of an operational system 7 Minimizes risk 7 A corporate Warehouse can be constructed from the union of the enterprise data marts Data Martng 101 Data From Xi Trahsaotroh Sources D5 lwarehouse 39 Update From the Warehouse The data warehouse populates the data rharts Data Marting 101 The data mans populate the data warehouse 5 Update From the I Marts Da a From Transatron Sources Data Marting 101 Data is moved through the abstract layer on demand The data warehouse layer manages the data marts 1quotquot as a warehouse OLAP 101 39 OLAP is a powerful graphicsoriented tool used to access the data warehouse 39 OLAP supports Business analysis queries Data Visualization Trend analysis Scenario analysis User de ned queries OLAP 101 0 Drill Down 7 Move from summary to detail 0 Roll Up 7 Move from detail to summary 0 Slice and Dice 7 Look at a speci c interest ofthe business OLAP 101 Pivot and Rotate 7 Looking at data from varying perspectives 0 Drill Through 7 Move to a near transaction level of detail OLAP 101 The avors of OLAP 7 Multidimensional OnLine Analytical Processing MOLAP 7 Relational OnLine Analytical Processing ROLAP 7 Hybrid OnLine Analytical Processing HOLAP OLAP 101 MOLAP 7 Produces a hypercube 7 Preaggregated and precalculated 7 Rapid response times 7 Limited in the amount of data that can be managed OLAP 101 ROLAP 7 Data remains in a relational format 7 Some degree ofaggregation 7 Slower response times 7 Scales to large amounts ofdata OLAP 101 HOLAP 7 Can manage data both as ROLAP and MOLAP 7 Currently evolving 7 MOLAP Vendors are nding it easier to moVe into the HOLAP market space Data Mining 101 As defined by the Gartner Group in 1995 data mining is theprocess of discovering meaning l new correlations patterns and trends by sifting through large amounts of dam stored in a repository using pattern recognition technologies and statistical and mathematical techniques Data Mining 101 0 Data mining requires an analyst who is familiar with the domain to appropriately model scenarios 0 Data mining assists analysts in uncovering nontrivial data relationships 0 Analysis must be conducted to determine the meanings of these newly identified relationships Why Use a Data Warehouse 0 Data warehousing is a must for anyone who uses multiple data sources to make decisions and understand business trends forecasting 0 Those who do not move to warehousing will not be capable of responding to problems and business conditions thus falling behind the competition Why Use a Data Warehouse 0 For organizations wanting to minimize c053 and maximize productivity warehousing is a must 0 Individuals who spend time gathering data instead of analyzing data require the assistance of a warehouse 0 Organizations that collect data but have difficulty determining meanings and impacts need a data warehouse Making the Warehouse a Reality 0 Think big but work small Match technology to requiremenm Build for the future scalability 0 Work closely with the users 7 Requirements 7 Rapid Application Development RAD 7 Periodic releases to the user community Real World Success Stories 0 Radio Shack 7 Sales and stocking analysis 7 Marketing regionalized mailings WalMart 7 Sales and stock analysis 7 Trend analysis 7 Vendor analysis Real World Success Stories 0 Naval Surface Warfare Center NSWC 7 Procurement 7 Supply 7 Workload 0 Harris Semiconductor e Yield 7 Product 7 Personnel productivity Real World Success Stories 0 Defense Logistics Agency DLAManTech 7 Trend analysis 7 Problem identi cation 7 Procurement support 7 Enterprise data analysis A Few Observations About Data Warehouses Industry and our experience indicate t houses that succeed average arr ROI of400 u wrth the top end being as much as 600 m the first year 7 The rrrcrerrrehtai approach rs most successful buud the warehouse a functional area at a trrrre e The average trrrre to gather requrremerrts perform a desrgh and deploy a warehouse rrrcrerrrart rs six rrrorrths 7 New tools may be requrred that differ from the trarrsactrorr envr Su ware arrerted tdward rrrteurgerrt analyer and query arthe data warehduse Hardware arrerrted tr suppdrt the rrrassve stdrage reaurrerrrerrts arrd arraryuear queries Keys to Success Do you understand why you are building the warehouse Have you identified both technical and business professionals that you will need to build the warehouse Do you have a strong management sponsor Are you managing the expectations of the users Careers in Data Warehousing System I DBA AdmmiStm on I Application Developer DW Architect Data cleansing Data Architect Transformation DW Manager AnalYSt DW Administrator 39 Business AnalYSt Decision Support 39 Management nalysts Data Warehousing Jeffrey T Edgell The Basic Structure Data ta in ma Corporate View wage 50 Dam at frles fastest RDBMS gt her Extract Extract pruner Comblne 82gt rern St Pl Jk clean oye dupllcatlon andardlze export to data rnarts no user quart Selvlquot 5 Populate ruta M an m OLAP ROLAP MOLAPHOLAP dlmenslonal access er r n replicate refresn frequenCy recoy er conforms to the Bus DW Bus igt DamMan 2 DW Bus 39gt Data Man 3 The Basic Structure U Corporate Staging Area Data Mm l OLAP ROLAP MOLAPHOLAP dlmenslonal access subject orrented conforms to the Bus DW Bus Dada Marl 2 DW Bus Dam Man 3 39gt Data Feed Data Feed gt Data Feed ser Access Ad Hoc Query Tools Rep ortlng Tools and Wrrters Customlzed Appllcatlons Models forecastrng sconng allocatrng data rnrnrng scenarlo analysts etc The Business Dimensional Lifecycle 0 Project Flaming Early Critical Tasks 7 readiness assessment 7 business justi cation Remaining Tasks 7 Resource requirements and identi cation 7 Schedule construction and integrations The Business Dimensional Lifecycle 0 Business Requirements Definition 7 Critical to success 7 Designers must understand the business needs 7 A plan to extract users needs and to understand them must be developed The Business Dimensional Lifecycle 0 Three project tracks follow the business requu emenls definition process 7 Data track 7 Technology track 7 Application track The Business Dimensional Lifecycle 0 Data Track 7 Dimensional modeling 7 Physical design 7 Data staging design and development The Business Dimensional Lifecycle 0 Technology Track 7 Technical architecture design 7 Things to consider business requirernents current technical environment planned strategic technical directions The Business Dimensional Lifecycle 0 Application Track 7 Product identi cation selection and installation 7 End user application development Con guring the metadatarepository access Building specialized applications The Business Dimensional Lifecycle 0 Deployment 7 The integmtion of all the pieces of the puzzle 7 The best Warehouse Will fail if deployment is not properly plann d 7 Plan required prior to deployment are education user support feedback enhancementmaintenance The Business Dimensional Lifecycle 0 Maintenance and Growth 7 Work never stops 7 Critical to support and stay connected to the users to ensure the Warehouse meets their needs 7 Watch performance and plan ahead the backroom 7 Collect and analyze metrics regarding use and operation The Business Dimensional Lifecycle 0 Maintenance and Growth cont 7 If you are successful change is inevitable Plan and prioritize future initiatives With user buyin 7 Always plan for expansion and growth With each new increment or change The Business Dimensional Lifecycle 0 Project Management 7 Monitor project status 7 Track issues 7 Control change 7 Project communication 7 Project marketing 7 Project politician 7 Project visionary The Business Dimensional l ifecvcle Data Sta m Design amp Dwelupmen E ssv Appllcatlun Develupmen Depluvmem Dlrmnslunal thslcal Mudehm Deslun Endossv Appllcatlun specmcauun Pv lect Plannln Pvulect Management Project Flaming amp Management I Who Wants the Warehouse 7 A single visionary user desirable because the focus remains manageable requires political leverage to make itwo the need must have broad and de nable impacts to show worth a Multiple demands Many organizations want a data ma Focus is spread therefore politics and planning warehouse play a vltal role Project Flaming amp Management 0 Who Wants the Warehouse cont 7 No identi ed need Organization Writing to get in the warehouse ame More effort on the warehouse team to identify the nee It is highly likely there will be one Project Flaming amp Management I Determine Warehouse Readiness 7 Do you have a strong business sponsor vision Politically sayyy Connected In uential History ofsuccess Respected Realistic Undastands the need and the process and can communicate it Project Flaming amp Management 0 Determine Warehouse Readiness cont 7 Without this person you Will fail 7 Try to recruit multiple sponsors 7 Is there areal and identi able business need 7 Does a strong partnership exist between IT and the business groups 7 What is the current analytical environment How are things done now What culture shock will be created Project Planning amp Management 0 Determine Warehouse Readiness cont 7 What is the feasibility Is the data dirty beyond recovery Is the target sources to dispersed and dynarnic to achieve early and signi cant results Project Flaming amp Management I Take the Readiness Litmus Test 7 The test looks at r i Current Analytical Environment Feasibility sponsor is the most important to get ahigh rating from the test a Business needs and ITBusiness Partnerships are condary in importance Project Flaming amp Management 0 Addressing Readiness Issues 7 Highlevel business requiremenw analysis Identify the strategic initiatives Identify the business metrics Identify the high impact and ROI areas 7 Business Requirements Prioritization Look for high impact ROI andfeasibility 7 ProofofConcept Project Planning amp Management Develop the Initial Scope 7 Keep the scope narrow and short to retain clarity 7 The bigger the scope the more dif cult it becomes to retain focus 7 Always de ne the scope based on business requirements Try to avoid deadlines or budget cycles from driving the scope Project Flaming amp Management I Develop the Initial Scope cont cope de nition involves both IT and business representatives 7 Make the scope have signi cance but ensure it is achievable andtirn 1y 7 Stan with a single or few data sources and a single business process 7 Limit your initial userbase typically 25 35 people a ine what management expects so success can be identi ed Project Planning amp Management 0 Develop the Initial Scope cont 7 Document the scope de nition and success indicators 7 Acknowledge that the scope Will likely change 7 Develop a plan to manage the change Project Planning amp Management 0 Build the Business Justification 7 Determine the costs Identify hardware and software costs startup and ongoing Identify maintenance costs Internal staffneeds External resources consultants etc Operational support Support of growth pains Project Flaming amp Management I Build the Business Justi cation cont 7 Determine the bene ts nancia and other Increased pro t ncreased customer satisfaction Expansion ofa market or capability Increased employee productivity Re uction ofcapital investments storage requirements etc Protection against fraud and at ac Project Flaming amp Management 0 Build the Business Justification cont 7 It is important to monitor and track the business to identify and market impacts the Warehouse has made 7 Look for the tangibles and intangibles Project Planning amp Management 0 Plan the Project 7 Establish project identity 39 Create aname Create documentation describing your project Make Tsnins mugs etc Market market market 20 Project Flaming amp Management 0 Plan the Project cont 7 Staff up Project Manager DW Educator Business Lead Business Analyst Data Modeler Data Staging Programmers DW DBA Data Stemrd Data Staging System Designer DW QA Analyst End User Application Developer TeclmicaVSecnrity Architect Teclinical Support Specialists Project Planning amp Management 0 Develop the Project Plan 7 Key frequently update your plan 7 The nature of a DW project in cyclic and resembles a spiral approach 7 Identify key milestones 7 Develop a highlevel and detailed plan Project Planning amp Management 0 Manage the Project 7 Matrix man ement is o en used because of the numerous interlaced roles 7 Data issues may lay Waste to the best devised plans plan for the unexpected 7 The project Will likely increase in visibility manage expectations 7 Iterativesliding Window development requires multiple teams Work in sync communication 21 Project Flaming amp Management 0 Manage the Project cont 7 Conduct a proj ect kickoff meeting Identify the team roles and responsibilities Identify the scope Identify goals Identify the schedule Review the preliminary PMP Conduct preliminary education Project Planning amp Management 0 Monitor the Project Status 7 Frequent communication 7 Project status meetings 7 Team meetings 7 Project status reports 7 Customer reporting Collecting the Requirements 0 The old theory was not to include the users in the early stages 0 Build it and they will come 0 This proved to be the demise of many early warehouse initiatives 0 A formal requirement but exible is needed to document the users needs of the warehouse 22 Collecting the Requirements 0 This is a difficult process for many reasons 7 Key people may feel threatened and are not Willing to cooperate 7 The informal decision process is typically not Well documented and is di persed 7 People have a dif cult time thinking out of the box 7 Terminology associated to Warehousing o en creates confusion andor misinformatio Collecting the Requirements 0 Talk with the business users first 7 Strive to understand how they do business 7 Identify how decisions are made today 7 Determine how they Would like to make decisions today and tomorrow 7 Do not just ask What data do you need Collecting the Requirements 0 Talk with the IT community second 7 Wait until some common so es and themes are identi ed by the business users before approaching IT 7 Look for feasibility issues 7 Start identifying technical issues such as platforms formats access and politi 7 Talk DBAs DAs application developers an designers 23 Collecting the Requirements 0 Getting the requirements Interview VS Facilitation 7 Interviews tend to stay focused and Work Well with small r ps 7 Facilitated sessions Work with larger groups and encourage brainstorming and cross pollination of ideas Collecting the Requirements 0 Roles of the requirements team 7 Lead interviewer 7 Secondary interviewers 7 Scribe 7 Observers 7 Facilitator Collecting the Requirements 0 Preparation for the interview 7 Look at strategic plans that relate to the company or group you will talk with 7 Look at the annual report Important goals an initiatives will be identi ed and taken seriously by the company 7 Review marketing material 7 Search the Internet for information 7 Identify past attempw at similar projects 24 Collecting the Requirements I Identify who will be interviewed 7 Business Look horizontally across the organization to see the big picture I Get as much detail as possible in the current area of focus vertical Request that your sponsor identify who should be interviewed Collecting the Requirements I Identify who Will be interviewed a Technology The data gurus these people have been around a long time and know the detai 5 Application programmers Pseudo technical people within a business area DBAs Data modelers system administrators IT management to identify the future Collecting the Requirements I Develop an interview questionnaire I Build an agenda for the interview sessions I Prepare the interviewees 7 Hold a single meeting with all interviewees to discuss the project intentions e c 7 Set the tone for all interviews 7 Encourage questions a Enables you to identify good andbad candidates early now you can plan for each person Collecting the Requirements Conduct the interview 7 Remain within the roles established for the interview team 7 Validate what you have collected with the user as soon as possible 7 De ne terms with the users profit revenue sales 7 Try to talk on their level and avoid using confusing technology tenns use their business lingo whe 39 le Collecting the Requirements I Conduct the interview 7 Try to remain exible during the interview process Meetwlth unexpected people Runpast the allottedtime Dlscuss topics somewhat out ofthe focus ofthe interview 7 Schedule breaks and limit the number of interview session per day to about five 7 Continue to manage expectations Collecting the Requirements I Potential interview questions for an executive 7 what are the objectives ofyour organization what are you trying to accomplish 7 How do you measure success How do you know you are doing well How often do you measure yourself 7 what are the key business issues you fac oda what could prevent you from meeting these objectives what would be the impact 26 Collecting the Requirements 0 Potential interview questions for an analyst 7 What are your groups objectives How do you accomplish them How do you achieve it 7 What are your success metrics How do you know you are doing Well How o en do you measure 7 What issues do you currently face 7 Describe your products vendors etc Is there a natural hierarchy Collecting the Requirements I Potential interview questions for an analyst a what type ofanalysis do you perform what data is used How do you get it what do you do with it a what analysis would you like to perform a what dynamic analysis needs do you have Who drives these needs How long does it take to perform you able to conduct deeper levels ofanalysis a what analytical capabilities would you like Collecting the Requirements I Potential interview questions for an analyst 7 Where are the bottlenecks in obtaining information 7 How much historical information is needed a How will improved information access impact you and your organizati n what is the nancial impact a what reports do you currently use which data elements on the reports are important Ho 39 39 this information used Is it combined with anything else 27 Collecting the Requirements I What to discuss With IT a Request an overview ofthe operational systerns a what are the current tools and technologies used to share information a what types of analyses are performed a How are detailed analyses supported and conducted a what are the data quality issues 7 Where do bottlenecks exist Collecting the Requirements 0 What to discuss with IT 7 What concerns do you have about data Warehousing in the organization What roadblocks do you see 7 What expectations do you have of the Warehouse 7 How do you expect the Warehouse to impact you Collecting the Requirements 0 Types ofusers you will interview 7 Abused User Involved in earlier attempts Unwilling to cooperate 7 OVerbooked User To busy to meet 7 Comatose User 7 OVerzealous User 7 Nonexistent User Use technology to drive the needs 28 Collecting the Requirements Wrap Up Review the interview results with the team Prepare and publish the resulm Establish what will be done next Dimensional Modeling Jeffrey T Edgell The Dimensional Model 0 More intuitive structure for presentation and reporting 0 Likely predates the ER approach 7 General Mills amp Dartmouth University developed a fact and dimension structure 7 Nielsen Marketing Research used this on grocery and drug store auditing and scanner data in the 70s and 80s 29 The Dimensional Model 0 Dimensions are descriptive Facm are likely numeric and are measurement based 0 Additive facts are vital to allow aggregation of many records during a retrieval 0 Page 145 A typical dimensional model The Argument for the Dimensional Model Tools can utilize a standardized framework Query tools can leverage against this for performance optimization High performance entry browsing is possible All queries can be initially constrained thus significantly increasing performance The Argument for the Dimensional Model Easily adapts to unpredictable queries Extends to allow the addition of new tables or data elemenm 7 will not require rebuilding the database from scratch 7 data does not need to be reloaded 7 existing reports and query tools do not need to be redesigned or implemented 30 The Argument for the Dimensional Model 0 The model can be altered as follows without interruption 7 The addition of new facts consistent with the de ned grain 7 The addition of new dimensions 7 The Widening of a dimension table 7 Changing the detail of a dimension to a lower level The Argument for the Dimensional Model 0 The dimensional model exhibim a predefined set of approaches used to deal with common issues 7 Slowly changing dimensions 7 Heterogeneous products track different lines of business ie checking amp savings 7 Payinadvance data bases look at individual components as Well as the total 7 Event handling no facts The Argument for the Dimensional Model 0 Aggregation in a warehouse allows for query performance normally delegated to hardware to solve greatly increasing 0 A standard set of schemas for different business types and applications exist 31 The Bus 0 Supports the incremental approach 0 The data mart approach has often lead to development of warehouse absent of a corporate framewor Stovepipe decision structures result Produces a uniform global structure eliminating the pocket or stovepipe data marts The Bus 0 Look at the entire enterprise as you design and build the data marts 0 A high level architecture must be defined that explains the entire structure 0 A detailed architecture must be developed to support each data mart as they are confronted Conformed Dimensions Dimensions used to represent concepm across the enterprise must be standardized and agreed upon 7 customer 7 product 7 time 7 potentially not region sales amp management 32 Conformed Dimensions 0 Conformed dimensions must be carefully managed maintained and published to ensure consistency The conformed dimension represents the central source description of which everyone agrees 0 If the conformed dimension approach is not observed the bus will not properly function Conformed Dimensions 0 With conformed dimensions 7 One dimension table relates to multiple facw 7 Browsers are consistent with the dimension providing a uni ed View 7 Rollups and meanings remain consistent across facts Conformed Dimensions 0 Design 7 Lowest level ofgranularity possible based on the lowest level de ned 7 Use the sequential numeric key surrogate key 33 Conformed Facts 0 Occurs during the definition of conformed dimensions 0 Relates common measurements accurately 7 Unit price 0 If facm are different use different names marketing profit amp sales profit 0 As much political as technical When the Bus is not Required 0 The business you are dealing with is intentionally segmented 7 Components operated autonomously with no uni ed corporate View required 7 Products or business areas are disjoint 7 For example a company sells music and repairs train engines no business or product synergy except at the Very top The Components of the Dimensional Model 0 Facm 0 Dimensions 0 Attributes The Bus optional but highly suggested 34 Operations 0 Drill down and rollup 7 Example on page 168 Snow akes 0 What is it The removal of low cardinality elds from a dimension placed in a new table and linked back With keys 0 Complicates design detail 0 Decreases performance 0 Saves some space but normally not a significant amount 0 Bit map indexes can not be effectively utilized When a Snow ake is OK 0 When used as a subdimesnion 7 The data in the subd is related to the dimension are at different levels of granularity 7 The data load times for the data are different 7 Examples County and state District and region Ship and battle group 35 Good Descriptive Dimensions 0 Large dimension tables 0 Highly descriptive 0 Without good descriptive dimensions the warehouse is not useful 0 Use 7 full Words no missing values null QA metadata Common Dimension Techniques 0 Time 7 example gure 57 page 176 0 Address 7 example page 178 0 Commercial address 7 example page 179 Slowly Changing Dimensions 0 What to do 7 Type 0 Ignore the change 7 Type 1 Overwrite the changed attribute 7 Type 2 Add a new dimension record With new value of the surrogate key 7 Type 3 Add an old value eld 36 Slowly Changing Dimensions 0 Ignore the change 7 Not typically a good solution to the problem but is done 0 Overwrite the changed attribute 7 Valid when correcting a Value from the source 0 Add a new dimension record with a generalized key 7 Retains history of a changed product Slowly Changing Dimensions 0 Add an old value field 7 Valid when on the previous change is needed for decision making Slowly Changing Dimensions 0 Type 2 example Change in product bottle changes from platic to glass Key 0 02 Type Plastic Glass SKU 1234 1234 37 Slowly Changing Dimensions 0 Type 3 example Regional divisions of a company changes only one historical change is supported Region Gold Silver Platinum Bronze OldRegion North South East We The Monster Dimension 0 It is a compromise Avoids creating copies of dimension records in a significantly large dimension 0 Done to manage space and changes efficiently Example 1 The Monster Dimension CustomeriKey CustomeriKey name Basically constant ame address dress citystate city state binhidate hi 7 e datei istJonrcnase datei rstJonreliase I May change income DemographicsiKey numberichildren income band W thmh education number children pmhase total Tpurchases educationilevel Bmdsusedw creditiscore total Tpurchasesiband minimize Cred t mw possibilities 38 The Monster Dimension 0 Case 1 Rapid change 7 Large dimensions can be dynamic because of the amount of information contained 7 Certzi aspects must be maintainedi product lines for companies in acquisition The Monster Dimension 0 The solution to very dynamic large dimensions 7 identify the dynamic areas of the dimension 7 segment the hot areas into there own independent dimensions 7 The relative static information remains in the original dimension The Monster Dimension 0 The trade off plus 7 the Warehouse can accurately retain signi cant changes in a dimension 0 time 7 to slow the rate of change down extremely dynamic attributes should be banded to slow the rate of change 7 All possible combinations in the dimension become nite discrete and are thus manageable 39 The Monster Dimension 0 The trade off minus 7 Loss of detail in the bands no longer exact 7 Once bands are de ned they must be enforced from that point on 7 Slower browse performance required When combining the segmented table With the table original 7 Impossible to combine the data Without a single instance of a fact nothing to relate the dimensions Example 2 The Monster Dimension Employee Table address dateiofiblnh socialisecurityinum title yearsiwithicompany income division purchaseilevel Employee Table address dateiofiblrth socialisecurityinum Corporate Demogiapliies position jrade incomeiband division servicejearsiband Degenerate DimensionsKey Definition Critical data provided in the legacy environment that normally remains independent Typically the old key from the current fact information you are using with no supporting data 40 Degenerate DimensionsKey Likely found in the header of a file 0 The other items have been absorbed in other dimensions 7 customer date Vendor item 0 The remaining item has no supporting attributes but is important 7 CLIN Requisition Order 0 Useful information and should be absorbed in the fact table Degenerate DimensionsKey 0 Useful information and should be absorbed in the fact table 0 If there is other supporting attributes it becomes a typical dimension Junk Dimensions 0 Resident ags status codes and miscellaneous information persism after the dimensional design is near complete 0 Alternatives 7 Place the ags in the fact tables 7 Make each attribute a dimension 7 Remove the attributes completely 41 Junk Dimensions 0 Leave the ags in the fact tables 7 likely sparse data 7 no real browse entry capability 7 can signi cantly increase the size of the fact table 0 Remove the attributes from the design 7 potentially critical information will be lost 7 if they provide no relevance remove them Junk Dimensions 0 Make a flag into it s own dimension 7 may greatly increase the number of dimensions increasing the size of the fact table 7 can clutter and confuse the design 9Combine all relevant ags etc into a single dimension 7 the number of possibilities remain nite 7 information is retained Keys Keys Keys 0 Surrogate keys always use 7 4 byte integer 232 or two billion integers 0 Date keys should use surrogates as well 7 dates are typically 8 bytes saves 4 bytes per fact 0 Do not use smart keys with embedded meanings 0 Do not use legacy or production keys 42 Just the Facts 0 Attempt to make all facts additive 7 simpli es calculations across dimensions 7 all numbers are not additive facts 0 Semiadditive facts can be used but understand they are there 7 averages max min 0 Nonadditive facts often are avoided but may have value 7 Weather conditions nondiscrete nondiscrete discriptions Steps to Designing a Fact Table 0 Time to choose 7 data mart functional business area 7 grain of the fact table What level of detail 7 dimensions associated to the datamart 7 the facw relative to the data mart Data Mart 0 Single operational source data marts provide the least amount of risk Multiple operational source data marts typical provide more cross functional value Examples remember processes you measure 7 Marketing 7 Sales 7 Inventory 7 Productivity 43 Fact Table Grain 0 Without this dimensions can not be accurately defined Select as low of a grain as possible 7 handles unexpected queries 7 adapts readily to additional facts and dimensions 7 delivers the most comprehensive solution 7 Consumes more space 7 Performance can be an issue Fact Loads 0 By record 7 account for every transaction or activity recorded ATM Snapshot 7 A picture of the related facw at a speci c point in time monthly reporting 0 Line item 7 track and re ect the status of line item activity PO Dimensions 0 Once the grain is defined basic dimensions will be evident from the grain customer time etc 0 Addition of other dimensions and dimensions 0 All dimensions can not be at a lower level of granularity than the lowest fact table grain 44 Identifying Facts 0 The grain of the fact table dictates the facts 0 All facts must be at the same level 0 Individual transaction tables typically have 1 fact the numeric value of the transaction 0 Snap shot and line item fact tables will likely contain multiple facm in that multiple additive facm are captured 0 Keep all three types separated Fact Table Families 0 Process chain supply chain linear 7 fact table represents each step in the process 7 RFIRFPRFQContactDelivery 7 supply chain process example page 200 7 each fact is connected on the bus 0 Value Circle parallel measurement 7 health care example page 202 7 retail Fact Table Families Heterogeneous Product Schemas 7 Service offered by the business are distinct and separate 7 banking checking savings loans etc 7 Insurance life home auto etc Transaction an Snapshot Schemas 7 Snapshot periodic picture example page 210 7 Transaction activity detail example page 207 45 Aggregate Families 0 Used to improve query performance 0 Typically roll ups of facts along a dimension for anticipated reporting and querying 0 Aggregate tables can also be used to combine details from two fact tables of varying granularity Factless Fact Tables 0 Used for two reasons 7 record an activity student attendance page 213 answers what the most popular classes were what days are frequently missed 7 Coverage account for activity that may not have happened example page 215 An entry is placed in the fact table for all item of interest answers questions regarding what did and did not have activity 46
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'