Use Case: A record in HDWPRODS DWGL_PERIOD_CHILD_BALANCES VIEW for my code combination for each period in this year, when I run a query for the same code in DWCENPRDs DW_PERIOD_BALANCES_F_SV, I see 1 record for the current month. Balances for the code combination=0
The FinStar loads specifically exclude rows where the fact values in all columns equal zero. HDWPROD, because it is really the Oracle transaction system base table, reflects the way Oracle handles the balances, which allows all balances in a row to be zero.
How, you might be wondering, do there come to be records in the Oracle GL_BALANCES source table where all the balances could be zero? Usually it happens because of a transaction that hits and is reversed within the same period such that the net effect is $0. At the time the first journal posts, Oracle creates a record for the code combination in GL_BALANCES where the period net is equal to the amount of the first journal. When the reversing transaction posts, it sets the period net for the code combination to $0. Once the GL_BALANCES record is created though, Oracle treats it the way it treats all other balance records; it continues to carry the $0 balances through for each subsequent period forever.
In order to save database space, the DW_PERIOD_BALANCES_F table in FinStar does not carry records with all zero balances except during the time a period is open. There is a load job that at the start of the month takes all the balances for the new period from OLTP. While the period is open, the FinStar balances are updated as part of the nightly load based on the last_update_date on the record in the GL_BALANCES table in OLTP. Once the period closes, however, there is another job that basically deletes all of the records associated with the closed period and rebuilds them from OLTP. The third job specifically excludes any GL_BALANCES rows where all balances equal $0.
If you look at the DW_PERIOD_BALANCES_F_SV in DWCENPRD today, you will see only one record for your code combination. It's for the FEB-06 period. Assuming that no transactions post to the code combination in FEB-06, when the MAR-06 period opens on March 1, that night's load will create a second balance transaction for MAR-06. Both will be visible until the FEB-06 period closes, when the FEB-06 period balances for the code combination will not be included because all the facts equal $0.