Chapter 5 - Procurement Flashcards
What is shrunken rollup conformed dimension with attribute subset?
Shrunken dimensions are conformed dimensions that are a subset of rows and /or columns of a base dimension. Shrunken rollup dimensions are required when constructing aggregate fact tables. They are also necessary for business processes that naturally capture data at a higher level of granularity, such as a forecast by month and brand (instead of the more atomic date and product associated with sales data). Another case of conformed dimension subsetting occurs when two dimensions are at the same level of detail, but one represents only a subset of rows.
procurement goal?
Overall procurement goal: Source appropriate materials in most economical manner
dimension table primary key
As we staunchly advocated in Chapter 3, the dimension table’s primary key is
a surrogate key rather than relying on the natural operational key. Although we
demoted the natural key to being an ordinary dimension attribute, it still has special
signifi cance. Presuming it’s durable, it would remain inviolate. Persistent durable
keys are always type 0 attributes. Unless otherwise noted, throughout this chapter’s
SCD discussion, the durable supernatural key is assumed to remain constant, as
described in Chapter 3.
Type 1 response to slo
Overwrite. Lets say a row has change typeproduct value goes from educational to games. with overright type 1 there is no way to track the change . no keys are changed. or rows added.
pluses and minuses of type 1 response
The type 1 response is the simplest approach for dimension attribute changes. In
the dimension table, you merely overwrite the preexisting value with the current
assignment. The fact table is untouched. The problem with a type 1 response is that
you lose all history of attribute changes. Because overwriting obliterates historical
attribute values, you’re left solely with the attribute values as they exist today. A type
1 response is appropriate if the attribute change is an insignifi cant correction. It also
may be appropriate if there is no value in keeping the old description.
NOTE The type 1 response is easy to implement, but it does not maintain any
history of prior attribute values.
other impacts to type 1 response
Before we leave the topic of type 1 changes, be forewarned that the same BI
applications can produce diff erent results before versus after the type 1 attribute
change. When the dimension attribute’s type 1 overwrite occurs, the fact rows are
associated with the new descriptive context. Business users who rolled up sales by
department on January 31 will get diff erent department totals when they run the
same report on February 1 following the type 1 overwrite.
There’s another easily overlooked catch to be aware of. With a type 1 response
to deal with the relocation of IntelliKidz, any preexisting aggregations based on the
department value need to be rebuilt. The aggregated summary data must continue
to tie to the detailed atomic data, where it now appears that IntelliKidz has always
rolled up into the Strategy department.
Finally, if a dimensional model is deployed via an OLAP cube and the type 1
attribute is a hierarchical rollup attribute, like the product’s department in our
example, the cube likely needs to be reprocessed when the type 1 attribute changes.
At a minimum, similar to the relational environment, the cube’s performance aggregations
need to be recalculated.
Type 2
Type 2: Add New Row
A type 2 response is the predominant technique for supporting this requirement
when it comes to slowly changing dimension attributes.
Using the type 2 approach, when IntelliKidz’s department changed on February
1, 2013, a new product dimension row for IntelliKidz is inserted to refl ect the new
department attribute value. There are two product dimension rows for IntelliKidz,
as illustrated in Figure 5-6. Each row contains a version of IntelliKidz’s attribute
profi le that was true for a span of time.
why type 2
With type 2 changes, the fact table is again untouched; you don’t go back to
the historical fact table rows to modify the product key. In the fact table, rows for
IntelliKidz prior to February 1, 2013, would reference product key 12345 when the
product rolled up to the Education department. After February 1, new IntelliKidz
fact rows would have product key 25984 to refl ect the move to the Strategy department.
This is why we say type 2 responses perfectly partition or segment history to
account for the change.
type 2 aggregate
If you need to count the number of products correctly,
then you would just use the SKU natural key attribute as the basis of the distinct
count rather than the surrogate key; the natural key column becomes the glue that
holds the separate type 2 rows for a single product together.
dates and type 2
Type 2 Effective and Expiration Dates
When a dimension table includes type 2 attributes, you should include several
administrative columns on each row, as shown in Figure 5-6. The eff ective and
expiration dates refer to the moment when the row’s attribute values become valid
or invalid. Eff ective and expiration dates or date/time stamps are necessary in the
ETL system because it needs to know which surrogate key is valid when loading
historical fact rows. The eff ective and expiration dates support precise time slicing
of the dimension;
when to expire a row
When a new profi le row is added to the dimension to capture a type 2 attribute
change, the previous row is expired. We typically suggest the end date on the
old row should be just prior to the eff ective date of the new row leaving no gaps
between these eff ective and expiration dates. The defi nition of “just prior” depends
on the grain of the changes being tracked. Typically, the eff ective and expiration
dates represent changes that occur during a day;
Type 3
add a new column.
However, sometimes you want to see fact data as if the change never occurred.
This happens most frequently with sales force reorganizations. District boundaries
may be redrawn, but some users still want the ability to roll up recent sales for the
prior districts just to see how they would have done under the old organizational
structure.
type 3 downfalls
Don’t be fooled into thinking the higher type number associated with type 3
indicates it is the preferred approach; the techniques have not been presented in
good, better, and best practice sequence. Frankly, type 3 is infrequently used. It is
appropriate when there’s a strong need to support two views of the world simultaneously.
Type 3 is distinguished from type 2 because the pair of current and prior
attribute values are regarded as true at the same time.
Type 3
The type 3 slowly changing dimension technique enables you to see
new and historical fact data by either the new or prior attribute values, sometimes
called alternate realities.
When Type 3?
Type 3 is most appropriate when there’s a signifi cant change impacting many
rows in the dimension table, such as a product line or sales force reorganization.
These en masse changes are prime candidates because business users often want
the ability to analyze performance metrics using either the pre- or post-hierarchy
reorganization for a period of time. With type 3 changes, the prior column is labeled
to distinctly represent the prechanged grouping, such as 2012 department or premerger
department. These column names provide clarity, but there may be unwanted
ripples in the BI layer.
Type 4
Type 4: Add Mini-Dimension
Thus far we’ve focused on slow evolutionary changes to dimension tables. What
happens when the rate of change speeds up, especially within a large multimillionrow
dimension table? Large dimensions present two challenges that warrant special
treatment. The size of these dimensions can negatively impact browsing and query
fi ltering performance. Plus our tried-and-true type 2 technique for change tracking
is unappealing because we don’t want to add more rows to a dimension that already
has millions of rows, particularly if changes happen frequently.
Fortunately, a single technique comes to the rescue to address both the browsing
performance and change tracking challenges. The solution is to break off frequently
analyzed or frequently changing attributes into a separate dimension, referred to
as a mini-dimension
type 4 setup
There would be one row in the
mini-dimension for each unique combination of age, purchase frequency score,
and income level encountered in the data, not one row per customer. With this
approach, the mini-dimension becomes a set of demographic profi les. Although the
number of rows in the customer dimension may be in the millions, the number of
mini-dimension rows should be a signifi cantly smaller. You leave behind the more
constant attributes in the original multimillion-row customer table
Type 5 - hybred
like 4 you have a table for more rapidly changing attributes. such as the demographic table. however, you dont put the foreign key in the fact table but another dimension table.
The type 4 mini-dimension terminology refers to when the demographics
key is part of the fact table composite key. If the demographics key is a foreign
key in the customer dimension, it is referred to as an outrigger.
Type 5
Let’s return to the type 4 mini-dimension. An embellishment to this technique is to
add a current mini-dimension key as an attribute in the primary dimension. This
mini-dimension key reference is a type 1 attribute, overwritten with every profi le
change. You wouldn’t want to track this attribute as a type 2 because then you’d be
capturing volatile changes within the large multimillion-row dimension and avoiding
this explosive growth was one of the original motivations for type 4
Semi Additive Facts
Semi-Additive Facts
We stressed the importance of fact additivity in Chapter 3. In the inventory snapshot
schema, the quantity on hand can be summarized across products or stores
and result in a valid total. Inventory levels, however, are not additive across dates
because they represent snapshots of a level or balance at one point in time. Because
inventory levels (and all forms of fi nancial account balances) are additive across
some dimensions but not all, we refer to them as semi-additive facts.
more on semi additive facts
All measures that record a static level (inventory levels, fi nancial account
balances, and measures of intensity such as room temperatures) are inherently
non-additive across the date dimension and possibly other dimensions. In these
cases, the measure may be aggregated across dates by averaging over the number
of time periods.
OLAP products provide the capability to defi ne aggregation rules within the
cube, so semi-additive measures like balances are less problematic if the data is
deployed via OLAP cubes.
Inventory Transactions
Inventory Transactions
A second way to model an inventory business process is to record every transaction
that aff ects inventory. Inventory transactions at the warehouse might include
the following:
■ Receive product.
■ Place product into inspection hold.
■ Release product from inspection hold.
■ Return product to vendor due to inspection failure.
■ Place product in bin.
■ Pick product from bin.
■ Package product for shipment.
■ Ship product to customer.
■ Receive product from customer.
■ Return product to inventory from customer return.
■ Remove product from inventory
transaction table
We recognize
some transaction types may have varied dimensionality in the real world. For
example, a shipper may be associated with the warehouse receipts and shipments;
customer information is likely associated with shipments and customer returns. If the transactions’ dimensionality varies by event, then a series of related fact tables should
be designed rather than capturing all inventory transactions in a single fact table.
Inventory Accumulating Snapshot
The fi nal inventory model is the accumulating snapshot. Accumulating snapshot
fact tables are used for processes that have a defi nite beginning, defi nite end, and
identifi able milestones in between. In this inventory model, one row is placed in the
fact table when a particular product is received at the warehouse. The disposition
of the product is tracked on this single fact row until it leaves the warehouse. In
this example, the accumulating snapshot model is only possible if you can reliably
distinguish products received in one shipment from those received at a later time;
Fact Table Types
There are just three fundamental types of fact tables: transaction, periodic snapshot,
and accumulating snapshot. Amazingly, this simple pattern holds true regardless
of the industry. All three types serve a useful purpose; you often need two complementary
fact tables to get a complete picture of the business, yet the administration
and rhythm of the three fact tables are quite diff erent. Figure 4-7 compares and
contrasts the variations.
Transaction
Bus Archetecture
By defining a standard bus interface for the DW/BI environment, separate
dimensional models can be implemented by different groups at different times.
The separate business process subject areas plug together and usefully coexist if
they adhere to the standard
conformed dimension
Drilling Across Fact Tables
In addition to consistency and reusability, conformed dimensions enable you to combine
performance measurements from diff erent business processes in a single report,
drill across
model separately and then outer-join the query results based on a common dimension
attribute, such as Figure 4-12’s product name. The full outer-join ensures all
rows are included in the combined report, even if they only appear in one set of
query results. This linkage, often referred to as drill across, is straightforward if the
dimension table attribute values are identical.
Identical Conformed Dimensions
all same attributes and names even uppeer or lower case
Shrunken Rollup Conformed Dimension
with Attribute Subset
Shrunken dimensions are conformed dimensions that are a subset of rows and /or columns of a base dimension. Shrunken rollup dimensions are required when constructing aggregate fact tables. They are also necessary for business processes that naturally capture data at a higher level of granularity, such as a forecast by month and brand (instead of the more atomic date and product associated with sales data). Another case of conformed dimension subsetting occurs when two dimensions are at the same level of detail, but one represents only a subset of rows.
shrunken conformed
Dimensions also conform when they contain a subset of attributes from a more
granular dimension. Shrunken rollup dimensions are required when a fact table
captures performance metrics at a higher level of granularity than the atomic
base dimension
accumulating snapshot.
Remember that an accumulating snapshot is meant to
model processes with well-defi ned milestones.
