...Blog we discussed about Dimensional Modelling and some of its components. Today we will go through different Schema that can be used during Dimensional Modelling to create a Data Warehouse. Before we start with today's topic , For my viewers those who are new to this field i would like to revisit some of the key points of my previous blogs: 1) Business Intelligence is mainly divided into three parts as per my understanding a) Data Warehouse design and Implementation (ETL process) b) Data Analysis (Using OLAP cubes) c) Reporting and Dashboard Creation For further details revisit my First blog 2) Important Components involved in Dimensional Modelling or Data Warehouse Designing a) Fact Tables (Additive Facts, Semi-Additive Facts, Non- Additive Facts) b) Dimension Table c) Grain For further details revisit my Second blog After a thorough revision of previous concepts lets start our today's discussion about different Schema involved in Dimensional Modelling or Data Warehouse Designing. First of all i would like to explain the meaning of the topic i.e Snow Covered Wagon Hitched to a Star = SnowFlake Schema and Star Schema are two types of Schema that are used while designing a Data Warehouse, Hence they can be explained as follows: Star Schema: A Star Schema is one of the simplest and easiest schema to understand. A schema which consists of Dimension...
Words: 557 - Pages: 3
...diagram, the following: Primary Data Warehouse and Data Mart. In this connection, explain the difference between ROLAP and MOLAP. A Primary Data Warehouse is a central repository of a database of a complete organization. It holds multiple subject areas and very detailed information. A Data Mart is a subset or an aggregation of the data stored to a primary data warehouse. It often holds only one subject area – for example, a specific department, finance or sales. It may hold more summaried data, and is typically smaller than a warehouse because of its employment on a different grain. Figure 1.1 illustrates the difference between data mart and a primary data warehouse. Since the data mart typically holds one subject area, it is much smaller than a primary data warehouse. These data marts can be viewed as small, local data warehouses replicating the part of primary data warehouse which is required by a specific domain or department. Data Warehouse Data Mart Data Warehouse Data Mart Figure 1.1 A data warehouse does not necessarily use a dimensional model, since it is partly normalized RDBMS, but data marts are multidimensional cubes. This connection gives arise to two concepts, ROLAP and MOLAP. ROLAP is an implementation based on a relational database, in our case which is a primary data warehouse, and MOLAP is an implementation based on a multidimensional database which are data marts. ROLAP tools use the relational database to access the data and generate SQL queries to calculate...
Words: 685 - Pages: 3
...Data Warehouse Design: Dimensional Modeling II Data Technology Chularat Tanprasert, Ph.D. Recap Dimensional modeling Popular, useful, and pragmatic approach Based on Kimball Fact table Dimension tables Design process in steps Database Schema Design Star Schema (With Attributes) Example Designs A useful way to learn about data warehouse design principles is by using examples – reuse. Kimball – Data warehouse lifecycle toolkit Adamson & Venerable – Data warehouse design solutions Let’s take a look at inventory, shipments, and financial services. Inventory An inventory system serves as a “middleman” between the manufacturer and the retailer – value adding process. There are threee types of inventory model Inventory snapshot Delivery status Transaction Inventory Snapshot Model For specific time periods, inventory levels are measured and recorded. Delivery Status Model Create one record for each complete shipment of a product to a warehouse. Transaction Model Record every transaction that affects the inventory. Shipments The shipments process is where the product leaves a company and is delivered to a customer. Typically, accompanying each shipment is a shipment invoice. Each line item on the shipment invoice corresponds to an SKU. Shipments Shipments Shipments Financial Services Typically a large bank. Services...
Words: 1220 - Pages: 5
...Coffing Data Warehousing Education Outline 02/17/05 TERADATA EDUCATION OUTLINE Coffing Data Warehousing has provided quality Teradata education, products and services for over a decade. We offer customized solutions to maximize your warehouse. Toll Free: 1-877-TERADAT Business Phone: 1-937-855-4838 Email: mailto:CDWSales@CoffingDW.com Website: http://www.CoffingDW.com In addition to the course material listed in this outline, we also offer Teradata classes in Teradata Basics, Implementation, SQL, Database Administration, Design and Utilities. Please contact us so we can customize a course to fit your specific needs. © 2006 Coffing Data Warehousing – All rights reserved. Confidential. 1 Coffing Data Warehousing Education Outline 02/17/05 PURPOSE Coffing Data Warehousing has been providing quality Teradata education for over a decade. We offer customized courses to maximize the effectiveness of each class. The purpose of this proposal is to build a lasting relationship with your company. To this end, we have combined our comprehensive Teradata education services in a unique package that we feel best suits the diverse needs of your company while offering our high quality product at competitive pricing. Coffing Data Warehousing is excited to offer you, our preferred partner, an innovative new way to look at training at the CoffingDW Teradata University (CDW-TU). This approach provides the ability to maximize learning potential. Our goal is to make your employees...
Words: 1512 - Pages: 7
...Mid Sweden University The Department of Information Technology and Media (ITM) Author: Katarina Lundqvist, kalu9700@student.miun.se Study program: Master of Science in Engineering – Computer Engineering, 270 higher education credits Examiner: Tingting Zhang, tingting.zhang@miun.se Tutor: Aron Larsson, aron.larsson@miun.se Tutor: Mats Olsson, mats.olsson@sogeti.se Scope: 20102 words inclusive of appendices Date: 2010-03-17 M.Sc. Thesis in Computer Engineering AV, 30 higher education credits Tools for Business Intelligence A comparison between Cognos 8 BI, Microsoft BI and SAP BW/NetWeaver Katarina Lundqvist Abstract 2010-03-17 Tools for Business Intelligence A comparison between Cognos 8 BI, Microsoft BI and SAP BW/NetWeaver Katarina Lundqvist Abstract The aim of the thesis was to conduct a general study of Business Intelligence and BI systems followed by a comparison of Cognos 8 BI, Microsoft BI and SAP BW/NetWeaver. The goal was to distinguish similarities and differences between the tools regarding technique, cost, usability and educational need and to provide a mapping for different customer situations. The method consisted of a theoretical study followed by a practical part including development, testing and interviews. The comparison showed that SAP and Microsoft both use the client/server model while Cognos is an integrated web-based system built on SOA. SQL Server can only be installed on Windows while BW and Cognos also support...
Words: 25350 - Pages: 102
...Week 3 - The Big Data Challenges May 2014 Case Study: The Big Data Challenges Glendoria Early Strayer University CIS 500 – Information Systems for Decision Making Dr. Vince Osisek May 12, 2014 Case Study: The Big Data Challenges Week 3 - The Big Data Challenges Page 2 What if your car could talk? Well Volvo CIO's had to wonder the same thing as well. Volvo, a Swedish multinational manufacturing company, that not only produce cars, but light and heavy weight trucks as well as buses. Volvo Car Corporation has a history of using innovation to produce premium automobiles. The manufacture chose to rely on information technology to gain the innovative edge to help the company grow by integrating the cloud infrastructure into their networks. The Volvo Data Warehousing Program, started in 2006 (Data in the Driver's Seat), it draws insight from this multi-terabyte resource to create clear business advantage by integrating information from four primary sources: a system for managing vehicle and hardware specifications, one for managing on-board software specifications, the system that collects vehicle diagnostic data from service centers worldwide, and the warranty administration system. (Tobey, 2010) Joining all the various pieces of data together, Volvo can be warned about potential mechanical issues that may show up in the early part of a car's lifecycle. Volvo can spot patterns from the data that may indicate a potential flaw in a particular part well before a...
Words: 577 - Pages: 3
...CRM的核心是客户价值管理，它将客户价值分为既成价值、潜在价值和模型价值，通过一对一营销原则，满足不同价值客户的个性化需求，提高客户忠诚度和保有率，实现客户价值持续贡献，从而全面提升企业盈利能力。 4. Give an example of an experience that you have had with a company that uses a CRM system? IBM全球企业咨询服务部 IBM商业价值研究院 招商银行借咨询之力， 以CRM系统推进转型 尽管银行对批发业务的CRM(客户关系管理)需求旺盛，但是银行 界却鲜有成功案例。招商银行借助IBM的帮助，采用咨询与开发 同步的创新模式，成功地构建了批发业务CRM系统，并为其批发 业务管理的提升以及招商银行二次转型奠定了坚实的基础。不仅 如此，招商银行的创新举措再次创中国银行业的风气之先。 5. Provide an example of an organization with a CRM system that seems to increase comfort or satisfaction. Provide an example of an organization using a CRM system that may cause customer concerns over the loss of privacy. 10. What is the difference between a data warehouse, a data mart and a database? Database 1. Used for Online Transactional Processing (OLTP) but can be used for other purposes such as Data Warehousing. This records the data from the user for history. 2. The tables and joins are...
Words: 545 - Pages: 3
...Data Warehouses The basic reasons organizations implement data warehouses are: To perform server/disk bound tasks associated with querying and reporting on servers/disks not used by transaction processing systems most firms want to set up transaction processing systems so there is a high probability that transactions will be completed in what is judged to be an acceptable amount of time. Reports and queries, which can require a much greater range of limited server/disk resources than transaction processing, run on the servers/disks used by transaction processing systems can lower the probability that transactions complete in an acceptable amount of time. Or, running queries and reports, with their variable resource requirements, on the servers/disks used by transaction processing systems can make it quite complex to manage servers/disks so there is a high enough probability that acceptable response time can be achieved. Firms therefore may find that the least expensive and/or most organizationally expeditious way to obtain high probability of acceptable transaction processing response time is to implement a data warehousing architecture that uses separate servers/disks for some querying and reporting. To use data models and/or server technologies that speed up querying and reporting and that are not appropriate for transaction processing There are ways of modeling data that usually speed up querying and reporting (e.g., a star schema) and may not be appropriate for transaction...
Words: 1160 - Pages: 5
...supermarket chain in South Africa, needed to sharpen its business strategy to maintain its leadership position in the retail sector. They required a highly cost-effective, scalable reporting solution that could integrate data from many operational systems into a single source of management and decision support information. They chose Sybase IQ running on Linux as part of an ongoing business intelligence operation. CUSTOMER PROFI LE SYBASE TECH NOLOGY ■ Sybase IQ on Linux KEY BEN EFITS ■ With dynamic international growth, Pick 'n Pay is South Africa's leading grocery retailer - one of the largest and most consistently successful retailers of food, clothing, and general merchandise. Since 1967, Pick 'n Pay has been dedicated to superior customer service, convenience, shopping efficiency, breadth of inventory, and employee growth. BUSI N ESS CHALLENGE Significant growth in the number of new Pick 'n Pay stores over the past few years resulted in increased data volumes and increased concurrent user query and reporting demands. Pick 'n Pay's 32 processor T eradata warehouse was not able to provide acceptable performance at an acceptable cost. SOLUTION Pick 'n Pay selected a management information reporting system (MIS) based on Sybase IQ data warehousing architecture to assure faster and more cost-effective delivery performance for corporate reporting. WHY SYBASE? Pick 'n Pay has a history with Sybase that goes back more than 10 years. With Sybase's business intelligence...
Words: 520 - Pages: 3
...Objective To become Data warehousing Techno-Functional Consultant Current Role: Team member Synopsis of Experience: ➢ Over 2+ years of IT experience in Application Development, Testing of Data Warehousing Projects. ➢ Worked extensively on ETL tool (Informatica 9.1). ➢ Working knowledge on BI Tool (BOXI) ➢ Good exposure to data analysis, data extraction and data loading. ➢ Extensively worked on Oracle 10G. ➢ Strong understanding of Dimensional Modeling, Star and Snowflake Schemas. ➢ Having good inter-personal, analytical and communication skills. Work Experience: ➢ Have been working since Dec 2011 to Till Date as Developer in Vertiv solutions (INDIA) pvt ltd. Education: B.Tech from Jawaharlal Nehru Technological University Technical Skills: Operating System : MS-DOS, Window 7, UNIX Database : Oracle 9i, MS-SQL Server ETL Tool : Informatica Power Center 9.1 OLAP Tool : Business Objects Scheduler : Autosys Other Utilities : TOAD 8.0 Project Information: Project#1 Vertiv solutions (INDIA) pvt ltd. Client: Hewlett Packard, Houston USA Project: E-WARRANTY MANAGEMENT SYSTEM Duration: Dec 2011 – Mar 2013 Environment: Windows XP, UNIX, Informatica 7.1.2, Business Objects 6.5, Oracle9i, TOAD8.0, MS-SQL Server2000, MS-DTS About the Client: Hewlett Packard...
Words: 593 - Pages: 3
...Best Practices in Data Modeling Dan English Objectives • • • • • Understand how QlikView is Different from SQL Understand How QlikView works with(out) a Data Warehouse Not Throw Baby out with the Bathwater Adopt Applicable Data Modeling Best Practices Know Where to Go for More Information QlikView is not SQL (SQL Schemas) SQL take a large schema and queries a subset of tables. Each query creates a temporary “Schema” of only a few tables. Query result sets are independent of each other. Query 1 Query 2 QlikView is not SQL (QV Schemas) QlikView builds a smaller and more reporting friendly schema from the transactional database. This schema is persistent and reacts as a whole to user “queries”. A selection affects the entire schema. QlikView is not SQL (Aggregation and Granularity) Store Table Store A B SqrFootage 1000 800 Sales Table Store A A A B B Prod 1 2 3 1 2 Price $1.25 $0.75 $2.50 $1.25 $0.75 Date 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006 Select * From Store, Sales Where Store.Store = Sales.Store will return: SqrFootage 1000 1000 1000 800 800 Store A A A B B Prod 1 2 3 1 2 Price $1.25 $0.75 $2.50 $1.25 $0.75 Date 1/1/2006 1/1/2006 1/1/2006 1/1/2006 1/1/2006 Sum(SqrFootage) will return: 4600 If you want the accurate Sum of SqrFootage in SQL you can not join on the Sales table in the same Query! QlikView is not SQL (Benefits) • QlikView allows you to see the results of a selection across the...
Words: 1267 - Pages: 6
...Dealing with Missing Information in a Data Warehouse Today businesses are investing many resources in building data warehouses and data marts to obtain timely and actionable information that will give them better business insight. This will enable them to achieve, among other things, sustainable competitive advantage, increased revenues and a better bottom line. In the early '90s, data warehousing applications were either strategic or tactical in nature. Trending and detecting patterns was the typical focus of many solutions. Now, companies are implementing data warehouses or operational data stores which meet both strategic and operational needs. The business need for these solutions usually comes from the desire to make near real-time actions in a constantly changing environment while receiving information from both internal as well as external source systems. Dealing with missing or unknown data is critical in these types of environments. Unknowns skew metrics and results to produce incorrect decisions. Knowledge of the unknown allows at least for further examination of any conclusions drawn from incomplete data. Furthermore, in a well-designed business intelligence environment, these unknowns are often resolved later as data that is more complete is entered into the operational systems. Irrespective of the nature of the applications, missing information has always been a problem for data warehouses. As business intelligence environments become more mature, real time and...
Words: 988 - Pages: 4
...Importance of Data Warehousing Brenda L Bach The Digital Firm and Business Communications/BU 204-8A November 15, 2014 Ron Rosalik Kenneth and Jane Laudon state that a data warehouse is a database that stores current and historical data that can be of potential interest to decision makers throughout the corporation (Laudon, 2011. p.225). They go on to explain that the data can originate from many core operational transaction systems and could include data from Web site transactions (Laudon, 2011 p.225). Data warehouse extract current along with historical data from all operational systems within an organization. The data warehouse makes the data it collects and stores available to anyone and can be accessed and viewed as needed but cannot be altered in any manner. These data warehouses also provide a large range of ad hoc as well as analytical tools and graphical reports that represent the data. Companies often build enterprise data warehouses and either uses a central data warehouse or a smaller decentralized warehouse called a data mart to preserve the data they collect through its many sources. A data mart is a subset of a data warehouse that summarizes on a highly focused portion of the organization’s data and is placed within a separate data based for a very specific population of users. For example, a car dealer that deals with car sales as well as service may use a data mart to develop marketing and sales data that are specifically focused on the data of the customer...
Words: 774 - Pages: 4
...What is a Data Warehouse • A data warehouse is a relational database that is designed for query and analysis. • It usually contains historical data derived from transaction data, but it can include data from other sources. Finance, Marketing, • Data warehouse can be: Subject Oriented Integrated Nonvolatile Time Variant Inventory SAP, Weblogs, Legacy Identical reports produce same data for different period. daily/monthly/quarterly basis Why Data Warehouse • • • • Provide a consistent information of various cross functional activity. Historical Data. Access, Analyze and Report Information. Augment the Business Processes Why is BI so Important Information Maturity Model Return on Information BI Solution for Everyone BI Framework Business Layer Business goals are met and business value is realized Administration & Operation Layer Business Intelligence and Data Warehousing programs are sustainable Implementation Layer Useful, reliable, and relevant data is used to deliver meaningful, actionable information BI Framework Business Requirements Data Sources Data Sources Data Acquisition, Cleansing,& Integration Data Acquisition, Cleansing, & Integration Data Stores Data Stores Information Services Information Delivery Information Delivery Business Analytics Business Analytics Business Applications Business Applications Business Value Business Value Development Data Resource Administration ...
Words: 3637 - Pages: 15
...Executive for Insurance, Finance, Investment and Banking Sectors. * Expertize in Analysis, Design, Development, Testing, Implementation, Administration and Support using Data Warehouse/Data Mart Design, ETL, BI and Client/Server applications. * Certified in ICS PowerCenter Data Integration Developer Specialist 9.0 and Teradata 12 TE0-121 Certified Professional Program. * Extensively worked on Data Extraction, Transformation and Loading of data from various sources like Oracle, SQL Server, Flat files and COBOL Sources. * Worked extensively with Slowly Changing Dimensions. * Working knowledge of Teradata as a Developer with proficiencies in Teradata SQL Assistant utilities like Fastload, Multiload, BTEQ, FastExport, and Tpump. * Experience in Teradata Data Warehouse architecture. * Proficient in application development and support, for batch processes hosted on an IBM mainframe, with a core focus in COBOL, DB2, JCL and VSAM. * Hands on experience in performance tuning, resolving on-going maintenance issues and monitoring of production runs. * Data modeling using Erwin, Star Schema, Snowflake, FACT and dimension tables. * Hands on experience in UNIX shell scripting. * Excellent communication and interpersonal skills. TECHNICAL SKILLS Frameworks | Informatica Data Director (IDD), Informatica System Integration Framework (SIF), Teradata version 12, Mainframe z/OS | Application Modules | UNIX, Informatica Application Designer, Workflow...
Words: 1062 - Pages: 5
