際際滷

際際滷Share a Scribd company logo

First Steps Snapshot vs Transaction Grain Statements

Ryan Casey
Ryan Casey

Snapshot grain statements identify the snapshot period as daily, monthly, or quarterly and the dimensions sampled at the end of each period, such as end of day snapshots of in-stock products by location or end of day snapshots of accounts by branch. These statements can include facts that summarize the snapshot period to prevent having to refer to the transaction star and should also be used to store period-to-date measurements, calculating averages by summing snapshot values and dividing by the number of days rather than using SQL AVG, which may issue if snapshots exclude zero-valued levels.

1 of 4
Snapshot Grain Statement Snapshot Grain Statements have 2 parts: Identifies the Snapshot Period (Daily, Monthly, Quarterly) Identifies 1 or more dimensions that will be sampled at the end of each period Can include Facts that summarize the snapshot period can prevent the need to refer to the transaction star when working with the snapshot. Should also be used to store Period-to-Date Measurements. ** Instead of using SQL AVG(), sum up the snapshot values and then divide by the number of days in the period such as months. SQL AVG may have an issue if you have a snapshot that excludes zero-valued levels The grain statement of a star should make it clear whether rows are to be recorded for zero- valued levels. For an inventory star, the grain can be stated as End of Day Snapshots of In-Stock Products by Location. (excludes zero-valued levels) While a bank will likely want accounts with zero balances to show up on reports. For a balances star, the grain can be stated as End of Day Snapshots of Accounts by Branch (includes zero-valued levels)
First Steps Snapshot vs Transaction Grain Statements
First Steps Snapshot vs Transaction Grain Statements
First Steps Snapshot vs Transaction Grain Statements

More Related Content

First Steps Snapshot vs Transaction Grain Statements

  • 1. Snapshot Grain Statement Snapshot Grain Statements have 2 parts: Identifies the Snapshot Period (Daily, Monthly, Quarterly) Identifies 1 or more dimensions that will be sampled at the end of each period Can include Facts that summarize the snapshot period can prevent the need to refer to the transaction star when working with the snapshot. Should also be used to store Period-to-Date Measurements. ** Instead of using SQL AVG(), sum up the snapshot values and then divide by the number of days in the period such as months. SQL AVG may have an issue if you have a snapshot that excludes zero-valued levels The grain statement of a star should make it clear whether rows are to be recorded for zero- valued levels. For an inventory star, the grain can be stated as End of Day Snapshots of In-Stock Products by Location. (excludes zero-valued levels) While a bank will likely want accounts with zero balances to show up on reports. For a balances star, the grain can be stated as End of Day Snapshots of Accounts by Branch (includes zero-valued levels)