FIT5195 Study Guide - Final Guide: Savings Account, Overdraft, Referential Integrity
2 Jul 2018
School
Department
Course
Professor
1) Discuss Todman’s Generalised Conceptual Model for designing customer-oriented data
warehouses. Why are customer-oriented data warehouses problematic to design?
Todman proposed a “General Conceptual Model” for customer centric data-ware houses. The
four generalised entities that relate to the customer are as follows:
Customer characteristics
Customer changing circumstances
Customer behaviour, and
Customer derived segment
Customer Behaviour: - Comprise the transaction history and /or contact history of the
customer. These histories mirror the customer’s behaviour towards the enterprise. In
multi-dimensional data context, usually the sales and contact fact tables represent these
histories.
Changing Customer Circumstances: -These usually are the attributes in customer master
data example, address, age, marital status or bank account. This data category refers to
the concept of slowly changing dimensions. Alterations in these data are not as frequent
as in the customer behaviour data, but they have profound impact on the semantics of
queries. Example change of customer address may imply a move into another sales
region, but the former sales volumes of this customer must not be transferred to new
sales region, otherwise this would distort the sales totals per sales region.
Deriver Customer segment data: - The data which have been calculated from attributes
of the above data categories and which serve to segment the customer base example
the customer lifetime value, propensity to churn or the cancellation probability. If an
enterprise targets to monitor the development of its customers over their lifecycle, it
has to track these derived data over time. But this kind of data need not necessarily be
derived only from data within the enterprise, as third-party vendors offer data of
individuals or geographical areas with demographical and marketing relevant
find more resources at oneclass.com
find more resources at oneclass.com
information. These external data can be integrated into customer database and be
utilized as guiding or planning values.
Challenges with customer-oriented data warehouses:
1) When implementing a customer-oriented perspective, enterprises aim to align their
activities with customer parameters that are derived from the collected customer
data. These data are generated by sales over multiple sales channels (e.g. direct and
indirect sales) and by contacts over multiple customer touch points (e.g. phone, fax,
mail, e-mail). Thus, the data are scattered in different databases and applications
within the enterprise, such as call centre, databases of marketing, sales and service,
accounting and ERP systems. An implication is that, in most cases, this partial
information is not integrated and therefore highly inconsistent. If these data are not
carefully processed before loading into the data warehouse, then the quality of the
data warehouse is put at risk. A serious consequence is that the enterprise lacks a
“single view” on their customer, i.e. an integrated and consistent set of customer
data.
2) To be able to analyse and understand their behaviour, customers ideally have to be
tracked over their complete lifecycles, from acquisition to retirement. But this would
require enterprises to collect and store all customer related transaction data on the
highest possible detail level, with the effect that the amount of analysable data is
predestined to grow exponentially. The sheer data volume has negative effects on
the performance and the response time of the data warehouse.
2) Why do some say that snowflake schemas a bad idea? Why do some say that they are not?
In your opinion, should snowflake schemas be used? Justify your answer.
//Snowflake schema isn’t good:-Multiple joins, speed, understandability of the model is
complexity of the data model because more tables, more joins….
When a hierarchical relationship in a dimension table is normalized, low-cardinality attributes
appear as secondary tables connected to the base dimension table by an attribute key. When
this process is repeated with all the dimension table’s hierarchies, a characteristic multilevel
structure is created that is called a snowflake. Although the snowflake represents hierarchical
data accurately, you should avoid snowflakes because it is difficult for business users to
understand and navigate snowflakes. They can also negatively impact query performance. A
flattened denormalized dimension table contains exactly the same information as a snow-
flaked dimension.
find more resources at oneclass.com
find more resources at oneclass.com
*Mini v/s snowflake
Mini dimension also adds in an extra table…key difference being mini is directly linked to
the fact table…. Add in an extra key to the fact table… and that extra key becomes indexed
by the dbms….speed efficiency….essentially same, physical faster query processing….much
reduced value in search table….. Create overhead and complexity though – Only use when
users are mostly querying at the aggregate rather than base level of a dimension hierarchy.
3) What physical modelling techniques can data warehouse developers use to improve data
warehouse performance?
a) Heterogeneous facts and dimensions
b) Mini dimensions
c) Slowly changing dimensions
d) Aggregate storage and navigation a.k.a storing precalculated storage and
aggregate(precalculated summaries) navigation…..
a) Each account type has a number of facts that aren’t associated with other account types
– Savings • Interest paid
– Cheque • Overdraft limit
– Credit cards • Credit limit
Logically - one fact table
>>The logical design of the fact table in this situation would see us include all the extra
facts and dimension attributes in the tables
>> Could easily be a dozen or more attributes for each type of account or product type
find more resources at oneclass.com
find more resources at oneclass.com
