barcode vb.net 2010 Multi-Valued Dimensions and Bridges 203 in Software

Creation QR in Software Multi-Valued Dimensions and Bridges 203

9 Multi-Valued Dimensions and Bridges 203
Read Quick Response Code In None
Using Barcode Control SDK for Software Control to generate, create, read, scan barcode image in Software applications.
Draw QR Code 2d Barcode In None
Using Barcode generation for Software Control to generate, create Quick Response Code image in Software applications.
The specter of double-counting is enough to send many organizations retreating to the simplified relationship solution. Where the need is strong enough to warrant the bridge, however, measures can be taken to reduce the risk. Before looking at them, let s look at one adjustment that does not solve the problem. One well-intentioned impulse is to eliminate the many-to-many relationship between fact table and bridge by asserting that each group will be used only once. If the same members collaborate again, a new group will be created. While this does reduce the many-to-many relationship to a one-to-many relationship, it is a one-to-many relationship going in the wrong direction. Even if we create a second group when Ann and Henry collaborate again, the order still refers to two salespeople, and it is still in danger of being double-counted. Worse, the bridge table is likely to grow very large, and it may even contain more rows than the fact table. Luckily, other techniques will provide limited success.
Reading QR Code JIS X 0510 In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
Denso QR Bar Code Creation In C#
Using Barcode creator for .NET Control to generate, create Denso QR Bar Code image in Visual Studio .NET applications.
Adding an Allocation Factor Sometimes, it is possible to eliminate double-counting dangers by adding an allocation factor to the bridge table. The allocation factor indicates what percentage of the associated fact should be credited to each group member. The example in Figure 9-5 shows an allocation factor in the sales_group table. When Ann and Henry collaborate, 75 percent of the order_dollars are credited to Ann, and 25 percent to Henry. The key to making allocations work lies in how the facts are accessed. It is essential that the allocation factor is applied to each granular transaction, prior to aggregation. The query in Figure 9-5, for example, selects the sum (order_dollars * allocation),
QR Code 2d Barcode Creator In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create QR Code JIS X 0510 image in ASP.NET applications.
Encoding QR Code ISO/IEC18004 In .NET Framework
Using Barcode generator for .NET Control to generate, create Quick Response Code image in .NET framework applications.
ORDER_FACTS day_key product_key customer_key sales_group_key . . . order_dollars . . . SALES_GROUP sales_group_key salesrep_key allocation SALESREP salesrep_key salesperson_name region . . .
QR Encoder In Visual Basic .NET
Using Barcode creator for VS .NET Control to generate, create QR image in VS .NET applications.
Encode Bar Code In None
Using Barcode encoder for Software Control to generate, create barcode image in Software applications.
PRODUCT
Encoding DataMatrix In None
Using Barcode encoder for Software Control to generate, create Data Matrix image in Software applications.
Encode Code 39 Full ASCII In None
Using Barcode creation for Software Control to generate, create Code39 image in Software applications.
CUSTOMER
Making Barcode In None
Using Barcode generator for Software Control to generate, create barcode image in Software applications.
Draw EAN / UCC - 13 In None
Using Barcode printer for Software Control to generate, create GS1 - 13 image in Software applications.
ORDER_FACTS day_ key 2012 product_ customer_ sales_ key key group_key 3721 4791 1 order_ dollars 1,000
Paint Planet In None
Using Barcode drawer for Software Control to generate, create USPS PLANET Barcode image in Software applications.
Bar Code Maker In C#.NET
Using Barcode drawer for .NET framework Control to generate, create bar code image in .NET framework applications.
SALES_GROUP sales_ group_key 1 1 salesrep_ allocation key .75 222 333 .25
Code-39 Creator In Objective-C
Using Barcode drawer for iPad Control to generate, create Code 3 of 9 image in iPad applications.
Recognize Code 128 Code Set A In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
SALESREP salesrep_ key 222 333 salesperson_ name Ann Davis Henry Perez region East East
Encode UPC-A Supplement 5 In .NET
Using Barcode generation for Reporting Service Control to generate, create UPC-A Supplement 2 image in Reporting Service applications.
Read Code 128B In None
Using Barcode decoder for Software Control to read, scan read, scan image in Software applications.
SELECT salesperson_name, sum(order_dollars * allocation) FROM
Scan Bar Code In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
Code128 Encoder In Java
Using Barcode generation for BIRT Control to generate, create USS Code 128 image in Eclipse BIRT applications.
. . .
SALESPERSON_NAME ----------------Ann Davis Henry Perez
ORDER_DOLLARS ------------750 250
Figure 9-5
An allocation factor can help prevent double-counting
Part III
PART III
Dimension Design
rather than trying to apply an allocation factor to sum(order_dollars). As long as the allocation is computed correctly, this approach ensures that no order dollars are doublecounted. This works, even if we create a grand total that adds together Ann and Henry s orders, or query for allocated order_dollars by region. The allocated amounts will always aggregate to the total order amount. We can even ensure that the facts are calculated this way by joining the bridge and the fact table within a view. For queries that do not involve the salesrep table, however, this view will only add unnecessary overhead. Allocation looks like the perfect solution but is rarely a valid option. In most situations, an allocation rule simply does not exist. Determining an allocation factor in a sales situation may be possible, but what does one do if a healthcare charge has several associated diagnoses It is tempting to try to define an allocation rule, but it must be based on real business logic, and must be approved by representatives of the business users. Many attempts at constructing allocation factors become bogged down in politics. On the rare occasion that an allocation factor is available, it may make more sense to redefine the grain of the fact table rather than build a bridge. For example, in the case of salesperson collaboration, allocation may be factored in as part of the order entry system. A fully vetted allocation factor is not only available but is also presented to the ETL process by the operational source. Instead of designing a bridge with an allocation factor, as in Figure 9-5, it is possible to avoid bridging altogether. This is done by defining the grain of the fact table as order lines allocated to salesreps. TIP Double-counting can be avoided by adding an allocation to the bridge table and aggregating allocated facts. Ownership of allocation factors must rest with the business community, not the technical community. If a clean allocation factor exists, consider the possibility of changing the grain of the fact table instead. Some facts cannot be meaningfully allocated. Suppose that Ann and Henry s $1,000 sale was a single item, perhaps a refrigerator. If the fact table had a column called quantity_ ordered, it would contain the value 1. Allocating this fact produces fractional quantities; Ann will appear to have sold three quarters of a refrigerator. This may be confusing but is perhaps tolerable.
Supplementing the Bridge with a Primary Member Another way to avoid the danger of double-counting is to hide the bridge from inexperienced users. This is done by supplementing the bridged solution with a simplified solution recognizing only one dimension row. The bridge is hidden from everyone except carefully trained analysts. In the case of our sales example, such a solution is depicted in Figure 9-6. Notice that a new foreign key has been added to the fact table: primary_salesrep_key. Every order has only one primary salesperson, so this can be used safely to join to salsrep to order_facts in the standard one-to-many configuration. The crow s feet are included on the diagram to drive this point home.
TIP When designing a bridge between a dimension and fact table, supplement it with a direct relationship that isolates a primary role in a one-to-many relationship. This can be used without fear of double-counting, although the bridge will be required for some forms of reporting.
Copyright © OnBarcode.com . All rights reserved.