สรุปเตรียมสอบ Bi กับ Data Warehouse ของ อ.วัชรา

รูปแบบของการจัดการข้อมูลในองค์กร (Formats of Data Management in Organizations)

- กระดาษ (Paper) ข้อมูลที่อยู่ในรูปของกระดาษ ทำาให้ไม่สามารถประมวลผลข้อมูลโดยโปรแกรมคอมพิวเตอร์ ได้ อีกทั้งยังยุ่งยากและเสียเนื้อที่มากในการจัดเก็บข้อมูล - แฟ้มข้อมูล (Files) ข้อมูลที่อยู่ในรูปของแฟ้มข้อมูลคอมพิวเตอร์ ทำาให้สามารถประมวลผลข้อมูลด้วยโปรแกรม คอมพิวเตอร์ได้ การจัดเก็บข้อมูลทำาได้ง่ายขึ้นและยังช่วยประหยัดเนื้อที่ในการจัดเก็บข้อมูล แต่ก็ยังมีปัญหาเนื่องจากแฟ้มข้อมูลที่มีอยู่จำานวนมากนั้น ไม่มีการจัดการจัดเก็บรวมกันอย่าง เป็นระบบ -ฐานข้อมูล (Databases) ข้อมูลที่อยู่ในรูปของฐานข้อมูล มีการจัดการข้อมูลอย่างเป็นระบบโดยการจัดการข้อมูลต่างๆ ทีเ่ ดิมอยู่อย่างกระจัดกระจายในแฟ้มข้อมูล โดยนำามาจัดเก็บรวมกันอย่างเป็นระบบไว้ใน ฐานข้อมูลเดียวกัน มีการจัดการพจนานุกรมข้อมูล (Data Dictionary หรือ Metadata) ทำาให้ ง่ายในการปรับปรุงข้อมูล การประมวลผล และการเรียกใช้ข้อมูล รวมทั้งยังมีการรักษาความ ปลอดภัยของข้อมูลด้วย แต่ฐานข้อมูลก็ยังไม่เหมาะในการเรียกใช้ (Retrieve) และวิเคราะห์ ข้อมูล (Analyze) โดยผู้บริหาร - คลังข้อมูล (Data Warehouse)

ข้อมูลที่อยู่ในรูปของคลังข้อมูล มีการจัดการข้อมูลให้เหมาะแก่การเรียกใช้และวิเคราะห์ ข้อมูลโดยผู้บริหาร ข้อมูลในคลังข้อมูลจะถูกจัดเก็บตามเรื่อง (Subject) ทีผ่ ู้บริหารต้องการ วิเคราะห์ โดยในแต่ละเรื่องสามารถทำาการวิเคราะห์ได้ในหลากหลายมุมมอง (MultiDimensions)

มาร์ทข้อมูล (Data Marts) มาร์ทข้อมูล คือ คลังข้อมูลย่อยๆของระดับแผนก ในองค์กรอาจจะมีคลังข้อมูลขององค์กร และในแต่ละแผนกอาจจะมีมาร์ทข้อมูลของแผนกนั้นๆด้วย

คลังข้อมูลคืออะไร (What Is Data Warehouse?) A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process… W. H. Inmon

Subject oriented: There is a shift from application-oriented data (i.e., data designed to support application processing) to decision-support data (i.e., data designed to aid in decision making). If designed well, subject-oriented data provides a stable image of business processes, independent of legacy systems. In other words, it captures the basic nature of the business environment.

Integrated: The database consolidates application data from different legacy systems which use different encoding, measurement units, and so on, and eliminates inconsistencies in the data. Time-variant: Informational data has a time dimension: each data point is associated with a point in time, and data points can be compared along that time axis unlike operational data which is valid only at the moment of access capturing a moment in time. Nonvolatile: New data is always appended rather than replaced. The database continually absorbs new data, integrating it with the previous data. คลังข้อมูล vs. มาร์ทข้อมูล (Data Warehouses vs. Data Marts)

ฐานข้อมูลปฏิบัติงาน vs. คลังข้อมูล(Operational Database vs. Data Warehouse)

ระดับการใช้ข้อมูลสารสนเทศของผู้ใช้ (Levels of Data/Information Usage)

คุณภาพของข้อมูล/สารสนเทศ (Quality of Data/Information) ตรงตามวัตถุประสงค์ของผูใ้ ช้ (Objective) มาจากแหล่งข้อมูลที่เชื่อถือได้ (Source) ความถูกต้องของข้อมูล/สารสนเทศ (Accuracy) ความทันสมัยของข้อมูล/สารสนเทศ (Currency) ความครบถ้วนของข้อมูล/สารสนเทศ (Coverage/Completeness) มีคำาอธิบายหรือพจนานุกรมข้อมูลที่ชัดเจน (Definition/Data Dictionary) มีความเป็นมาตราฐาน (Standard) ห่วงโซ่มูลค่าการจัดการข้อมูล(Data Management Value Chain)

วงจรข้อมูล-สารสนเทศ-การตัดสินใจ (Data–Information–Decision Cycle)

ธุรกิจอัจฉริยะคืออะไร (What is Business Intelligence?) "Business Intelligence is a process for increasing the competitive advantage of a business by intelligent use of available data in decision making. It is about access, analysis, and uncovering new opportunities” “ธุรกิจอัจฉริยะ (BI: Business Intelligence) คือ กระบวนการสำาหรับเพิ่มความได้เปรียบในการ แข่งขันของธุรกิจโดยการใช้ข้อมูลที่มีอยู่ได้อย่างอัจฉริยะ ธุรกิจอัจฉริยะ คือ การเข้าถึงการวิเคราะห์ และ การค้นพบโอกาสใหม่ๆ”

Financial Intelligence Financial Intelligence provides your finance managers with the ability to evaluate and improve financial performance, review a wide array of financial activities, and optimize internal processes. Financial Intelligence will provide your organization with a comprehensive report on its financial health covering everything from the profit and loss sheet, revenue plan and forecast, balance sheet, and general ledger. By managing your revenue cycle, you can make informed cash and revenue forecasts. Plus you can ensure that your decisions contribute to strong business performance. Customer Intelligence While it’s important to retain customers, it’s even more important to retain the right customers. But how do you know who the right customers are? The Customer Intelligence makes it possible for your organization to identify your most profitable customers, optimize sales activities around those customers, identify successful marketing campaigns, and take action.

In addition, the application gives managers insight into call center activity and changing customer behavior, allowing them to take action before it’s too late. Product and Service Intelligence Optimizing product profitability requires you to make decisions based on a variety of factors including product mix and pricing, product promotions, sales channel performance, and customer behavior. Product and Service Intelligence provides key insights into product behavior that impacts brand strategy, promotional and merchandising mix, and product lifecycle management issues. Product and Service Intelligence allows you to optimize product profitability and identify cross-sell and up-sell opportunities. Supply Chain Intelligence Flexibility, responsiveness, and reliability are the critical elements that make up a successful supply chain in today’s marketplace. Supply Chain Intelligence allows you to evaluate, monitor, and improve your supply chain performance and efficiency. With this application, you can improve supplier management, increase manufacturing efficiency, optimize delivery and return management, and more. Supply Chain Intelligence provides key performance indicator (KPI) benchmarks and best practice methodologies to help your users analyze supply chain cycle times, inventory holding costs, and demand forecasts. Human Resource Intelligence Organizations are faced with more complex employee issues than ever before from designing compensation and incentive plans around the company’s highest performers to dealing with independent contractors, foreign visa requirements, and a myriad of payroll and federal reporting requirements. Human Resource Intelligence can provide insights to your human resources (HR) managers by supplying the answers to help you maximize employee recruitment,

retention, and results. Whether they are dealing with employee retention and turnover rates, or sales commissions and bonus payouts, your HR team needs to have confidence in the security, confidentiality, and accuracy of their data. Human Resource Intelligence allows your HR managers to access the data they need when they need it, so they can make highly informed personnel decisions. Financial Intelligence Areas การวิเคราะห์ประสิทธิภาพทางการเงิน (Financial Performance Analytics) การวิเคราะห์ต้นทุน (Cost Analytics) การวิเคราะห์ค่าใช้จ่าย (Expense Analytics) การวิเคราะห์รายได้ (Revenue Analytics) การวิเคราะห์บัญชีลูกหนี้ (Accounts Receivable Analytics) การวิเคราะห์บัญชีเจ้าหนี้ (Accounts Payable Analytics) Dimensions Time Dimension Person Dimension Sale Channel Dimension Financial Category Dimension Line of Business Dimension Product Dimension Cost Center Dimension Profit Center Dimension Measures Profit and Loss Turnover

Cost of sales Gross profit

Operating expenses Operating profit Other costs/income Profit before interest and taxation Net interest receivable (payable) Profit on ordinary activities before taxation Balance Sheet Intangible Assets Tangible assets Investments Total Fixed Assets Stock Debtors due within one year Short-term investments Cash at bank and in hand Total Current Assets Creditors: Amounts falling due within one year Net current assets (liabilities) Total assets less current liabilities Others % of Forecast Forecast vs. Budget Expenses per Head T&E per Head

Tax on profit on ordinary activities Profit on ordinary activities after taxation Equity minority interests Profit for the financial period Dividends Retained profit

Creditors: Amounts falling due after more than one year Provisions for liabilities and charges Net assets Called-up share capital Share premium Other reserves Profit and loss account Equity shareholders' funds Minority interests Total capital employed Weighted average number of shares in issue in the period Current Headcount Invoice Entered Invoices Paid Paid Late

Invoice to Payment Days Payments % Discount Offered % Discount Taken Open Payable Amount Invoices Due Amount Number Invoices Due Financial Ratios Gross Profit Margin (%) Operating Profit Margin (%) Net Profit Margin (%) Retained Profit Margin (%) Profit Mark Up (%) Profit Before Interest and Taxation (%) Profit Before Taxation (%) Profit for the Year (%) Operation Cost (%) Interest Cost (%) Creditor’s Turnover Fixed Asset Turnover Current Asset Turnover Capital Employed Turnover Working Capital Turnover Earning Per Share Dividend Per Share Dividend Yield Dividend Cover

Weighted Average Days Due Invoices Past Due Amount Number Invoices Past Due Weighted Average Days Past Due Discount Remaining Amount Invoices on Hold Amount % Invoices on Hold ROCE (Return On Equity Employed) (%) ROTA (Return On Total Asset) (%) ROFA (Return On Fixed Asset) (%) ROWC (Return On Working Capital) (%) Current Ratio (Working Capital Ration) Quick Ratio (Acid Test Ratio) Total Asset Turnover Stock Turnover Debtor’s Turnover Price/Earning EBIT EBITDA

ห่วงโซ่มูลค่าของธุรกิจอัจฉริยะ (Business Intelligence Value Chain)

ทำาไมต้องมีธุรกิจอัจฉริยะ (Why Business Intelligence?) Business intelligence is fast becoming a strategic differentiator for today’s leading organizations. Managers need a consolidated view of their key enterprise metrics and performance indicators in order to make intelligent decisions. Business Intelligence can provide your organization with the most comprehensive approach to analytics on the market today. In today’s competitive markets, enterprises need to manage and reduce operational costs. One key benefit of BI is that it gives executives, mid-level or line managers, and employees the information they need to drive operational efficiencies. BI is also a key factor in improving top line revenue growth. As competition increases, the ability to understand and target particular customer segments with appropriate and profitable products and services becomes a key differentiator. BI helps the drive towards higher service levels and increased revenues by bringing to light the latest trends in customer behavior, determining which customer segments are the most profitable, and identifying cross-selling opportunities. A BI strategy is a fundamental foundation for enterprise performance management

(EPM), a process that connects goals, metrics, and people in order to drive improved management, analysis, and action across the organization. IT budgets are tight. But IT is still spending in some areas. Business Intelligence (BI) is one of them. Why? Because BI projects: Leverage existing information investments Are relatively low cost and low risk Deliver proven high return on investment Because of this, the BI market continues to show continued strong growth. This in turn means that most large organizations are in the process of initiating new BI projects. 5 ขั้นตอนของธุรกิจอัจฉริยะ(The Five Key Stages of Business Intelligence) Data sourcing Business Intelligence is about extracting information from multiple sources of data. The data might be: text documents; photographs and images; sounds; web pages; files; database; data mart and data warehouse. The key to data sourcing is to obtain the information in electronic form Data analysis Business Intelligence is about synthesizing useful knowledge from collections of data. It is about estimating current trends, integrating and summarizing disparate information, validating models of understanding, and predicting missing information or future trends. Situation awareness Business Intelligence is about filtering out irrelevant information, and setting the remaining information in the context of the business and its environment. The user needs the key items of information relevant to his or her needs, and summaries that are syntheses of all the relevant data (market forces, government policy etc.). Situation awareness is the grasp of the context in which to understand and make decisions. Algorithms for situation assessment provide such syntheses

automatically. Risk assessment Business Intelligence is about discovering what plausible actions might be taken, or decisions made, at different times. It is about helping you weigh up the current and future risk, cost or benefit of taking one action over another, or making one decision versus another. It is about inferring and summarizing your best options or choices. Decision support Business Intelligence is about using information wisely. It aims to provide warning you of important events, such as takeovers, market changes, and poor staff performance, so that you can take preventative steps. It seeks to help you analyze and make better business decisions, to improve sales or customer satisfaction or staff morale. It presents the information you need, when you need it.

Data Warehouse Implementation Approach Options - Enterprise Data Warehouse

- Dependent Data Mart

- Independent Data Mart

- Federated Warehouse

The Structure of the Data Warehouse

Metadata Meta data is descriptive information about data elements or data types, such as files, reports, workflow processes, and so forth. It is typically used for technical activities such as database design and application development. But, in a data warehousing environment it also becomes very important to end users. It is particularly important for those who plan to access the data directly and develop their own information applications. They need to understand what data is available for them to access, exactly what that data represents, how current it is, and so on. As a data warehouse is built, there is a requirement to capture both the data and the meta data. Meta data is found in data dictionaries, database catalogs, programs, and copy libraries, and is typically used only by professional programmers. However, with data warehousing there is now a requirement to transform the meta

data definitions into business terms for end users and provide a mechanism to make it easy for end users to search for and use this meta data. Meta data guides the extraction, cleaning, and loading processes, as well as makes query tools and report writers function smoothly. Meta data is used as: a directory to help the DSS analyst locate the contents of the data warehouse, a guide to the mapping of data as the data is transformed from the operational environment to the data warehouse environment, a guide to the algorithms used for summarization between the current detailed data and the lightly summarized data and the lightly summarized data and the highly summarized data, etc. Data Warehouse Implementation Considerations Methodology Ensures a successful data warehouse Encourages incremental development Provides a staged approach to an enterprise-wide warehouse Safe Manageable Proven Recommended Design and Modeling • Warehouses differ from operational structures: • Analytical requirements • Subject orientation • Data must map to subject oriented information: • Identify business subjects • Define relationships between subjects

• Name the attributes of each subject • Modeling is iterative. • Modeling tools are available. ETL (Extract, Transform, and Load)

Purchase specialist tools, or develop programs • Extract - select data using different methods • Transform - validate, clean, integrate, and time stamp data • Load - move data into the warehouse Data Management Efficient database server and management tools for all aspects of data management Imperatives Productive Flexible Robust Scalable Efficient Hardware, operating system and network management

Data Access and Analysis

OLAP - Online Analytical Processing • On-line Analytical Processing (OLAP) is a software technology that enables analysts, managers, and executives (sometimes called knowledge workers) to access data using an easy and efficient query analysis tool. • OLAP complements data warehouses.

Storage Mode MOLAP (Multidimensional OLAP)

ROLAP (Relational OLAP) Analysis against relational data Presentation of data in multiple dimensions Less functionality Greater data type choice HOLAP (Hybrid OLAP) The HOLAP storage mode combines attributes of both MOLAP and ROLAP. Like MOLAP, HOLAP causes the aggregations of the partition to be stored in a multidimensional structure. HOLAP does not cause a copy of the source data to be stored. For queries that access only summary data contained in the aggregations of a partition, HOLAP is the equivalent of MOLAP. Queries that access source data, such as a drilldown to an atomic cube cell for which there is no aggregation data, must retrieve data from the

relational database and will not be as fast as if the source data were stored in the MOLAP structure. Warehouse Data Numerical measures of the business Accessed by dimensions Point-in-time data snapshots Element of time and dates Multipart primary key Indexed primary keys Non-indexed columns Many fact tables Avoid reorganizing facts Fact Data Tables Tables can be large. Data is introduced according to refresh cycles. Data is date stamped. Data allows navigation through history.

Database Keys

Database Keys and Indexes Primary keys on fact and dimension table columns Foreign keys on fact table columns Indexed for speed Primary keys may be maintained in a Composite index Single column index Indexes may be ignored Generalized keys (or surrogate keys) may be employed Granularity Affect on warehouse Size of the warehouse database Degree of analysis

Flexibility Level of detail of the data Individual transactions Daily snapshots Monthly snapshots Yearly snapshots Any other time period Fact Table Attributes

Dimension Data Dimension data qualifies and drives user query constraints. Design is imperative. Dimension data is linked to fact data by keys.

Dimension Data Data must be of good quality. Data is often expanded for the warehouse. Dimension data is changed not refreshed. It is not usual to completely overwrite the dimension table with a new snapshot of data. The change is normally managed in a selective way.

Dimension Data Tables Textual data Smaller volumes

Contain highly denormalized data Quality data is important

Dimension Data Tables

Normalization Normalized data contains no Redundancy. Repeating data. Denormalized data often Improves efficiency in OLAP systems. Exists in data warehouse databases. Comprises derived or summary data. Reference Data and Tables Supports management of dimension data Reduces warehouse volume Provides lookup for encoded data

Summary Data Provide fast access to precomputed data Reduce use of I/O CPU

Memory Distill from Lightly summarized data Highly summarized data

Summary Data Average Maximum Total Percentage

Summary Data Tables Important design consideration Based on facts calculated by dimension data Usually exist in summary fact tables May be many hundreds Some tools are not summary table aware Metadata Vital to the warehouse Data about data Used by everyone • ETL metadata - physical design, sources, mapping rules • User metadata - navigation aid, business information, rules • Operational metadata - scheduling, analysis

Data Warehouse Models - Warehouse Model - Star

- Warehouse Model - Snowflake

Direct use by some tools More flexible and suited to requirements Provides for speedier data loading May degrade performance More complex metadata

- Warehouse Model - Constellation

Modeling Dimensions Model according to data content Model with aggregation needs in mind Model to satisfy drilling requirements May be fully denormalized - star May be fully normalized - snowflake Construct hierarchies within query tool limits Categorical dimensions may be included