Accounting Star

Description

The Accounting Star schema is designed to hold data across all of your accounts. The Accounting Star contains information on Department, Account, VAT, Payment Terms, Expense, Dates, Currencies, Rate of Exchanges, Matched and PreMatched. This star predominantly uses the txx Heritage table.

There is also a possibility that your frozen data can be imported into the star, at an additional cost.

Star Schema

Example Query

Below is an example query that will select all of the available data from the fact and associated dimension tables.

 

SELECT                    dbo.DimCompany.CompanyCode, DocCcy.Ccy AS DocumentCurrencyCode, FunctCcy.Ccy AS FunctionalCurrencyCode,

                                  dbo.DimDept.Dept AS DepartmentCode, PayTerms.Terms AS PaymentTermCode, ExpTerms.Terms AS ExpenseCode,

                                  dbo.DimLedger.AccNum AS AccountCode, dbo.DimVat.VatCode

                                  dbo.FactAccounting.DocumentDate, dbo.FactAccounting.ValueDate, dbo.FactAccounting.PostingDate, dbo.FactAccounting.PostingTime,

                                  dbo.FactAccounting.PromptMonth, dbo.FactAccounting.AccountingPeriod,

                                  dbo.FactAccounting.AmountInDocumentCurrency, dbo.FactAccounting.AmountInFunctionalCurrency,

                                  dbo.FactAccounting.DocumentReference, dbo.FactAccounting.ContractReference, dbo.FactAccounting.AccountLedgerType,

                                  dbo.FactAccounting.AmountInLocalCurrency, dbo.FactAccounting.RateOfExchangeApplied, dbo.FactAccounting.RateOfExchangeType,

                                  dbo.FactAccounting.WeightInQtyCode, dbo.FactAccounting.VatTurnover, dbo.FactAccounting.ClientReference, dbo.FactAccounting.Narrative,

                                  dbo.FactAccounting.MarketReference, dbo.FactAccounting.OptionType, dbo.FactAccounting.CommodityReference, dbo.FactAccounting.TransactionIsMatched,

                  dboFactAccounting.PreMatchDocumentReference, DocCcy.Descr AS DocumentCurrencyDescr, FunctCcy.Descr AS FunctionalCurrencyDescr, dbo.DimDept.Descr AS DepartmentDescr, 

  dbo.DimCompany.SiteCode, dbo.DimCompany.CompanyName, PayTerms.Descr AS PaymentTermDescr, ExpTerms.Descr AS ExpenseDescr, dbo.DimVat.Inactive, dbo.DimVat.Descr AS VatDescr,

  dbo.DimVat.VatRate, dbo.DimLedger.RecType AS AccountRecType, dbo.DimLedger.Status AS AccountStatus, dbo.DimLedger.Priv AS AccountPriv, dbo.DimLedger.AccFlag AS AccountFlag,

  dbo.DimLedger.Type AS AccountType, dbo.DimLedger.Alias AS AccountAlias, dbo.DimLedger.Title AS AccountTitle, dbo.FactAccounting.RecStart, dbo.FactAccounting.RecEnd

 

FROM                        dbo.FactAccounting

 

INNER JOIN               dbo.DimCcy AS DocCcy ON dbo.FactAccounting.DocumentCurrencyKey = DocCcy.CcyKey

INNER JOIN               dbo.DimCcy AS FunctCcy ON dbo.FactAccounting.FunctionalCurrencyKey = FunctCcy.CcyKey

 

INNER JOIN               dbo.DimDept ON dbo.FactAccounting.DepartmentKey = dbo.DimDept.DeptKey

 

INNER JOIN               dbo.DimCompany ON dbo.FactAccounting.CompanyKey = dbo.DimCompany.CompanyKey

 

INNER JOIN               dbo.DimTerms AS PayTerms ON dbo.FactAccounting.PaymentTermKey = PayTerms.TermsKey

INNER JOIN               dbo.DimTerms AS ExpTerms ON dbo.FactAccounting.ExpenseKey = ExpTerms.TermsKey

 

INNER JOIN               dbo.DimLedger ON dbo.FactAccounting.AccountKey = dbo.DimLedger.LedgerKey

 

INNER JOIN               dbo.DimVat ON dbo.FactAccounting.VatKey = dbo.DimVat.VatKey