Category Archives: Data Warehouse

Data-warehouse Design 4 -final

The last stage of designing a data warehouse is to create a design scheme of data warehouse , which is a collection of database objects such as table, view , index, and other objects that describe a data warehouse.


Datawarehouse Design (3)

Designing conceptual models of data warehouses is the next stage which should be implemented after the phase determination of measure and dimension . At this stage, created a model that can describe any data or tables that will be stored in a data warehouse, the following connectedness among others.

The data or tables in the data warehouse can be modeled by using modeling tools such as ER diagramstar schemaSnowflake schema , or FCO-IMFully Communication Oriented Information Modelling ). But in general, are tools used star schema or Snowflake schema .

The star schema is the simplest style of data warehouse schema. The star schema consists of a few fact tables (possibly only one) referencing any number of dimension tables.

Star schema will describe the fact table , the table that represents the measure , as a “data center”. This table will be connected with the tables that describe the dimensions to measurethe ( dimension table ). For example, the star schema for data warehouse sale is:

Star Schema


Snowflake schema architecture is a more complex variation of a star schema design. The main difference is that dimensional tables in a snowflake schema are normalized. one or more of the existing hierarchy in the dimension tables are normalized (decomposition) into several smaller table, so they have a typical relational database design.

Snowflake Schema

Snowflake Schema


Datawarehouse Design (2)

The next phase should be done is to determine the measure and dimensionfor all the necessary information management Measure the numerical data to search for traces of its value, while the dimension is a parameter or point of view of the measure so as to define a transaction.

For example, for information on “goods of the most widely sold in certain locations throughout the year”,

  • Measure : total sales
  • Dimension : goods, year (time / period), location

As for the information “goods where most revenue throughout the year”,

  • Measure : Total revenue
  • Dimension : goods, year (time / period)

Dimension has a hierarchy. Determination of the hierarchy for dimension is entirely dependent on the drill down and roll-ups who want dilakukaan while doing OLAP (OnLine Analythical Processing) later.

For the above example, hierarchy of each dimension are:

  • Returns: name of goods, sub-categories, category
  • Period: week, month, year
  • Location: village, district, city

Datawarehouse Design (1)

The first stage of designing a data warehouse is to define what information is needed by management . In order for this requirement can be defined precisely, then an understanding of the role and management tasks that require such information absolutely must be done first. If it has been understood, then we just stayed “answer” to the following questions:

  • Who needs information from the data warehouse?
  • What information is needed it?
  • What kind of layout and content of that information?
  • When will this information be used?
  • For what purposes?
  • What is the source for that information?

For example, suppose that would be made a sales data warehouse (or data mart sales exact) for a trading company.

  • Who needs information from the data warehouse?
    Marketing Manager
  • What information is required Marketing Manager?
    What items most sold in certain locations throughout the year
    What goods where most revenue during the year
  • What kind of layout and content of that information?
    Goods of the most widely sold in certain locations throughout the year

Goods where most revenue during the year

  • For what purpose such information?
    Basis for determining the strategy of selling goods
  • When will this information be used?
    Initial sales period
  • When will this information be used?
    Initial sales period
  • The database is what is the source for that information?
    Sales data base with the following scheme:
    1. Goods (# code, name, groups, units, price)
    2. Category (# group, sub_kategori, category)
    3. Customer (# cust_code, name_cust, address, city, zip_code, phone)
    4. Location (# zip_code, village, district)
    5. Sales (# no_faktur, # goods_code, qty)
    6. Payments (# no_faktur, date, total, discount, # cust_code)

To be continued …

Kebutuhan data pendukung pengambillan keputusan bisnis

Penggunaan IT pada setiap organisasi untuk mendukung jalannya operasionalnya adalah suatu hal yang tidak bisa dipungkiri, terutama organisasi-organisasi bisnis. Dari mulai toko kecil alfamart/idomart/ceriamart di pasar hingga gerai besar semacam Giant, Carrefour menggunakan aplikasi POST (Poin Of Sale Transaction). Dibidang airline dengan aplikasi reservasi, hingga bisnis support semacam loyalty program tidak lepas dari software untuk menjalankan bisnisnya. Implikasi dari adanya software-software yang bersifat data centric (aplikasi tersebut menyimpan data dan biasanya dalam database) adalah tumpukan data transaksi yang tidak informatif dan tidak penting bagi stakeholder alasannya karena stakeholder memerlukan informasi yang agregate dan menggambarkan kondisi organisasi bisnis saat ini yang memerlukan data tidak hanya dari satu sumber (satu aplikasi operasional) tapi sebisa mungkin dari hampir setiap departement/bagian organisasinya sehingga bisa dijadikan dasar untuk pengambilan keputusan.

Banyak jalan menuju Mekah, banyak cara untuk memberikan informasi bagi stakeholder diantaranya dengan mengandalkan reporting dari setiap aplikasi. Hanya saja tidak feasible karena stakeholder perlu menggabungkannya setiap report yang digenerate dari setiap software yang dimiliki organisasi tersebut. Cara yang paling feasible menurut saya adalah dengan membangun data warehouse.

Data warehouse adalah salah satu tahapan dalam penggunaan data yang ekstensive untuk mendapatkan keunggualan kompetitif yang sukar ditiru oleh organisasi lain. Ingat, saat suatu keunggulan mudah ditiru, maka keunggulan tersebut akan cepat menjadi komoditas dan tidak bisa dijadikan kekuatan dalam persaingan bisnis. Tahap setelah data warehouse tentunya adalah data mining.