The purpose of this document is to:
As it pertains to computer information systems, a dashboard is a type of graphical user interface which often provides at-a-glance views of data relevant to a particular objective or process through a combination of visualizations and summary information. The dashboard is often accessible by a web browser and is typically linked to regularly updating data sources. Dashboards are often interactive and facilitate users to explore the data themselves, usually by clicking into elements to view more detailed information.
As it pertains to us, a dashboard contains a collection of one or more widgets set on the canvas (the physical area on the dashboard), for the purpose of providing inght throug data visualisation.
KPI stands for key performance indicator, a quantifiable measure of performance over time for a specific objective. KPIs provide targets for teams to shoot for, milestones to gauge progress, and insights that help people across the organization make better decisions.
Metrics are measures of quantitative assessment commonly used for assessing, comparing, and tracking performance or production. Generally, a group of metrics will typically be used to build a dashboard that management or analysts review on a regular basis to maintain performance assessments, opinions, and business strategies.
Power BI Financial Analytics for Epicor Kinetic
"Transforming Financial Reporting for Smarter Business Decisions"
Struggling with manual financial reporting in Excel? Power BI Financial Analytics for Epicor Kinetic by Precise Business Solutions empowers finance teams with fast, interactive dashboards that bring your financial data to life.
Say goodbye to cumbersome spreadsheets and hello to real-time insights with Profit & Loss, Balance Sheet, and Cashflow Statements, as well as Debtor and Creditor Aging reports—all accessible through a powerful and user-friendly interface.
The Precice Epicor Financial Reporting (PEFR) solution is a light weight, rapid deployment, fit for purpose financial reporting solution.
Providing immediate General Ledger visibility, the Precice Epicor Financial Reporting (PEFR) solution is built to give immediate insight into your account balances.
Furthermore, our current solution architecture makes reporting equally as easy within Epicor's native dashboard/EDD environment as it does using external reporting tools such as Power BI, connecting to the API's.
Designed for finance professionals using Epicor, from CFOs to accountants, who seek greater financial visibility without the complexity of building reports from scratch.
Security You Can Trust
Powered by Microsoft Power BI, the solution incorporates world-class security measures:
At the heart of the Precice Epicor Financial Reporting (PEFR) solution are a set of BAQ's modelling Epicor tables into a neat, intuitive Star Schema of the necesary Dimensions and Fact Tables.
These BAQ's can be queried directly from within Epicor/EDD to effortlessly produce dashboards and reports.
And should your organisation wish to query the same data in an external reporting tool such as Power BI, it can access the very same BAQ data via Epicor's API's.
Over and above this flexible data solution, our offering comes bundled with a Power BI report template showing how to connect to the API's along with a series of reports showcasing the standard financial reports achievable.
Currently, we can produce:
for period balances and movements, as appropriate.
And slice data across the following attributes:
We are propsing to add:
slicable across all the same dimension attributes the Income Statement and Balance Sheet are.
Further to this, want to produce:
reports.
| Version | Release Date | Compatability | Notes |
|---|---|---|---|
| version 2025.1 | February 2025 | Up to 2024.2 | Cashflow, aged debtor & creditor capability. |
| version 2024.1 | September 2024 | Up to 2024.2 | Profit & Loss and Balance Sheet capability. |
No problem. This solution is a data solution and ultimately technology agnostic. Virtually all modern reporting products have the ability to connect to API's, meaning our solution is truely versatile.
No. The solution to date is backwards compatable. And in the event the Epicor data structure changes significantly enough to impact the solution, we will provide/make available updated BAQ's to ensure your reporting solution continues uninterupted.
Setting up your fincancial category master data correctly is crucial for good financial reporting.
The following Account Classification hierarchy aims to be an exhausive, albeit extensible, intuitive set of classifications that map nicely to the financial statements defined in the following section.
[Command Prompt] ping <IP Address>
ping 10.0.104.21
ping QAARM2024-JSM
ping QAARM2024-JSM/ARM8040
https://erparmsandpit.precise.local/Kinetic2022_2/api/v1/BaqSvc/edw_DimFiscalPeriod/
| DimCustomer | |
|---|---|
| [CustomerKey] | NVARCHAR(21) |
| [Company] | NVARCHAR(8) |
| [CustomerNum] | INT |
| [CustomerCode] | NVARCHAR(10) |
| [CustomerName] | NVARCHAR(50) |
| [Address1] | NVARCHAR(50) |
| [Address2] | NVARCHAR(50) |
| [Address3] | NVARCHAR(50) |
| [City] | NVARCHAR(50) |
| [State] | NVARCHAR(50) |
| [Zip] | NVARCHAR(10) |
| [Country] | NVARCHAR(50) |
| [PhoneNum] | NVARCHAR(20) |
| [EMailAddress] | NVARCHAR(50) |
| [CustURL] | NVARCHAR(MAX) |
| [CurrencyCode] | NVARCHAR(4) |
| [CountryNum] | INT |
| [LangNameID] | NVARCHAR(8) |
| [CustomerTermsCode] | NVARCHAR(4) |
| [CustomerTermsDesc] | NVARCHAR(50) |
| [CustomerGroupCode] | NVARCHAR(4) |
| [CustomerGroupDesc] | NVARCHAR(20) |
| [SalesRepCode] | NVARCHAR(8) |
| [SalesRepName] | NVARCHAR(30) |
| [SalesRepEmail] | NVARCHAR(50) |
| [SalesRepTitle] | NVARCHAR(50) |
| [SalesRepReportsTo] | NVARCHAR(8) |
| [SalesRepRoleCode] | NVARCHAR(20) |
| [SalesRepRoleDesc] | NVARCHAR(30) |
| [TerritoryID] | NVARCHAR(8) |
| [TerritoryDesc] | NVARCHAR(30) |
| [RegionCode] | NVARCHAR(12) |
| [RegionDesc] | NVARCHAR(30) |
| [ShipToNum] | NVARCHAR(14) |
| [ShipViaCode] | NVARCHAR(4) |
| [CreditHold] | BIT |
| [DiscountPercent] | DECIMAL |
| [Comment] | NVARCHAR(MAX) |
| [ParentCustNum] | INT |
| [TaxRegionCode] | NVARCHAR(4) |
| [WebCustomer] | BIT |
| [CustomerType] | NVARCHAR(3) |
| [CreditLimit] | DECIMAL |
| [ChangedBy] | NVARCHAR(75) |
| [ChangeDate] | DATE |
| [ChangeTime] | INT |
| [ChargeCode] | NVARCHAR(8) |
| DimFiscalPeriod | |
|---|---|
| [FiscalPeriodKey] | NVARCHAR(47) |
| [FiscalYear] | NVARCHAR(6) |
| [FYStartDate] | DATE |
| [FYEndDate] | DATE |
| [FYPeriods] | INT |
| [FPStartDate] | DATE |
| [FPEndDate] | DATE |
| [FiscalQuarter] | NVARCHAR(2) |
| [FiscalPeriod] | INT |
| DimGLAccount | |
|---|---|
| [GLAccountKey] | NVARCHAR(224) |
| [Company] | NVARCHAR(8) |
| [COACode] | NVARCHAR(10) |
| [GLAccount] | NVARCHAR(200) |
| [GLAccountDesc] | NVARCHAR(50) |
| [GLAccountAndDesc] | NVARCHAR(253) |
| [GLStatement] | VARCHAR(13) |
| [Segment1Value] | NVARCHAR(50) |
| [Segment1Name] | NVARCHAR(50) |
| [GLCategoryID] | NVARCHAR(10) |
| [GLCategoryDesc] | NVARCHAR(30) |
| [ParentGLCategoryID] | NVARCHAR(10) |
| [ParentGLCategoryDesc] | NVARCHAR(30) |
| [L1_GLCategoryID] | NVARCHAR(10) |
| [L1_GLCategoryDesc] | NVARCHAR(30) |
| [L1_GLCategoryOrder] | NVARCHAR(43) |
| [L2_GLCategoryID] | NVARCHAR(10) |
| [L2_GLCategoryDesc] | NVARCHAR(30) |
| [L2_GLCategoryOrder] | NVARCHAR(43) |
| [L3_GLCategoryID] | NVARCHAR(10) |
| [Segment2Value] | NVARCHAR(50) |
| [Segment2Name] | NVARCHAR(50) |
| [Segment3Value] | NVARCHAR(50) |
| [Segment3Name] | NVARCHAR(50) |
| [Segment4Value] | NVARCHAR(50) |
| [Segment4Name] | NVARCHAR(50) |
| [Segment5Value] | NVARCHAR(50) |
| [Segment5Name] | NVARCHAR(50) |
| [Active] | BIT |
| [EffFrom] | DATE |
| [EffTo] | DATE |
| [SysRevID] | TIMESTAMP |
| [SysRowID] | UNIQUEIDENTIFIER |
| DimGLBook | |
|---|---|
| [GLBookKey] | NVARCHAR(23) |
| [Company] | NVARCHAR(8) |
| [CompanyName] | NVARCHAR(50) |
| [CompanyAndName] | NVARCHAR(61) |
| [GLBookID] | NVARCHAR(12) |
| [GLBookDesc] | NVARCHAR(40) |
| [GLBookIDAndDesc] | NVARCHAR(55) |
| [GLBookIDAndDescAndCurrency] | NVARCHAR(62) |
| [IsMainGLBook] | VARCHAR(3) |
| [BookType] | INT |
| [Inactive] | BIT |
| [COACode] | NVARCHAR(10) |
| [COADesc] | NVARCHAR(30) |
| [COASeparatorChar] | NVARCHAR(1) |
| [COAPerBalFmt] | NVARCHAR(50) |
| [COACodeTBBalFmt] | NVARCHAR(50) |
| [CurrencyCode] | NVARCHAR(4) |
| [CurrencyDesc] | NVARCHAR(30) |
| [CurrencySymbol] | NVARCHAR(4) |
| [FiscalCalendarID] | NVARCHAR(12) |
| [FiscalCalendarDesc] | NVARCHAR(40) |
| [FiscalCalendarStartDate] | DATE |
| [FiscalCalendarEndDate] | DATE |
| [COABalFmtChg] | BIT |
| [REAccount] | NVARCHAR(200) |
| [RESegValue1] | NVARCHAR(50) |
| [RESegValue2] | NVARCHAR(50) |
| [RESegValue3] | NVARCHAR(50) |
| [RESegValue4] | NVARCHAR(50) |
| [RESegValue5] | NVARCHAR(50) |
| [RESegValue6] | NVARCHAR(50) |
| [RESegValue7] | NVARCHAR(50) |
| [RESegValue8] | NVARCHAR(50) |
| [RESegValue9] | NVARCHAR(50) |
| [RESegValue10] | NVARCHAR(50) |
| [RESegValue11] | NVARCHAR(50) |
| [RESegValue12] | NVARCHAR(50) |
| [RESegValue13] | NVARCHAR(50) |
| [RESegValue14] | NVARCHAR(50) |
| [RESegValue15] | NVARCHAR(50) |
| [RESegValue16] | NVARCHAR(50) |
| [RESegValue17] | NVARCHAR(50) |
| [RESegValue18] | NVARCHAR(50) |
| [RESegValue19] | NVARCHAR(50) |
| [RESegValue20] | NVARCHAR(50) |
| [RndTolerance] | DECIMAL |
| [RndAccount] | NVARCHAR(200) |
| [RndSegValue1] | NVARCHAR(50) |
| [RndSegValue2] | NVARCHAR(50) |
| [RndSegValue3] | NVARCHAR(50) |
| [RndSegValue4] | NVARCHAR(50) |
| [RndSegValue5] | NVARCHAR(50) |
| [RndSegValue6] | NVARCHAR(50) |
| [RndSegValue7] | NVARCHAR(50) |
| [RndSegValue8] | NVARCHAR(50) |
| [RndSegValue9] | NVARCHAR(50) |
| [RndSegValue10] | NVARCHAR(50) |
| [RndSegValue11] | NVARCHAR(50) |
| [RndSegValue12] | NVARCHAR(50) |
| [RndSegValue13] | NVARCHAR(50) |
| [RndSegValue14] | NVARCHAR(50) |
| [RndSegValue15] | NVARCHAR(50) |
| [RndSegValue16] | NVARCHAR(50) |
| [RndSegValue17] | NVARCHAR(50) |
| [RndSegValue18] | NVARCHAR(50) |
| [RndSegValue19] | NVARCHAR(50) |
| [RndSegValue20] | NVARCHAR(50) |
| [CorrAccounting] | BIT |
| [SysRevID] | TIMESTAMP |
| [SysRowID] | UNIQUEIDENTIFIER |
| [OpenBalUpdateOpt] | NVARCHAR(8) |
| [FromNatAccount] | NVARCHAR(50) |
| [ToNatAccount] | NVARCHAR(50) |
| [LevelList] | NVARCHAR(100) |
| [CurrencyAcctType] | NVARCHAR(30) |
| [RevalueOpt] | INT |
| [GLGainAcctContext] | NVARCHAR(30) |
| [GLLossAcctContext] | NVARCHAR(30) |
| [GainAccount] | NVARCHAR(200) |
| [LossAccount] | NVARCHAR(200) |
| [AccrualAccount] | NVARCHAR(200) |
| [GainSegVal1] | NVARCHAR(50) |
| [GainSegVal2] | NVARCHAR(50) |
| [GainSegVal3] | NVARCHAR(50) |
| [GainSegVal4] | NVARCHAR(50) |
| [GainSegVal5] | NVARCHAR(50) |
| [GainSegVal6] | NVARCHAR(50) |
| [GainSegVal7] | NVARCHAR(50) |
| [GainSegVal8] | NVARCHAR(50) |
| [GainSegVal9] | NVARCHAR(50) |
| [GainSegVal10] | NVARCHAR(50) |
| [GainSegVal11] | NVARCHAR(50) |
| [GainSegVal12] | NVARCHAR(50) |
| [GainSegVal13] | NVARCHAR(50) |
| [GainSegVal14] | NVARCHAR(50) |
| [GainSegVal15] | NVARCHAR(50) |
| [GainSegVal16] | NVARCHAR(50) |
| [GainSegVal17] | NVARCHAR(50) |
| [GainSegVal18] | NVARCHAR(50) |
| [GainSegVal19] | NVARCHAR(50) |
| [GainSegVal20] | NVARCHAR(50) |
| [LossSegVal1] | NVARCHAR(50) |
| [LossSegVal2] | NVARCHAR(50) |
| [LossSegVal3] | NVARCHAR(50) |
| [LossSegVal4] | NVARCHAR(50) |
| [LossSegVal5] | NVARCHAR(50) |
| [LossSegVal6] | NVARCHAR(50) |
| [LossSegVal7] | NVARCHAR(50) |
| [LossSegVal8] | NVARCHAR(50) |
| [LossSegVal9] | NVARCHAR(50) |
| [LossSegVal10] | NVARCHAR(50) |
| [LossSegVal11] | NVARCHAR(50) |
| [LossSegVal12] | NVARCHAR(50) |
| [LossSegVal13] | NVARCHAR(50) |
| [LossSegVal14] | NVARCHAR(50) |
| [LossSegVal15] | NVARCHAR(50) |
| [LossSegVal16] | NVARCHAR(50) |
| [LossSegVal17] | NVARCHAR(50) |
| [LossSegVal18] | NVARCHAR(50) |
| [LossSegVal19] | NVARCHAR(50) |
| [LossSegVal20] | NVARCHAR(50) |
| [AccrualSegVal1] | NVARCHAR(50) |
| [AccrualSegVal2] | NVARCHAR(50) |
| [AccrualSegVal3] | NVARCHAR(50) |
| [AccrualSegVal4] | NVARCHAR(50) |
| [AccrualSegVal5] | NVARCHAR(50) |
| [AccrualSegVal6] | NVARCHAR(50) |
| [AccrualSegVal7] | NVARCHAR(50) |
| [AccrualSegVal8] | NVARCHAR(50) |
| [AccrualSegVal9] | NVARCHAR(50) |
| [AccrualSegVal10] | NVARCHAR(50) |
| [AccrualSegVal11] | NVARCHAR(50) |
| [AccrualSegVal12] | NVARCHAR(50) |
| [AccrualSegVal13] | NVARCHAR(50) |
| [AccrualSegVal14] | NVARCHAR(50) |
| [AccrualSegVal15] | NVARCHAR(50) |
| [AccrualSegVal16] | NVARCHAR(50) |
| [AccrualSegVal17] | NVARCHAR(50) |
| [AccrualSegVal18] | NVARCHAR(50) |
| [AccrualSegVal19] | NVARCHAR(50) |
| [AccrualSegVal20] | NVARCHAR(50) |
| [DebitRateType] | NVARCHAR(12) |
| [CreditRateType] | NVARCHAR(12) |
| [GainAcctDesc] | NVARCHAR(50) |
| [LossAcctDesc] | NVARCHAR(50) |
| [DefaultPackage] | NVARCHAR(15) |
| DimVendor | |
|---|---|
| [VendorKey] | NVARCHAR(21) |
| [Company] | NVARCHAR(8) |
| [VendorNum] | INT |
| [VendorCode] | NVARCHAR(8) |
| [VendorName] | NVARCHAR(50) |
| [Inactive] | BIT |
| [Address1] | NVARCHAR(50) |
| [Address2] | NVARCHAR(50) |
| [Address3] | NVARCHAR(50) |
| [City] | NVARCHAR(50) |
| [State] | NVARCHAR(50) |
| [ZIP] | NVARCHAR(10) |
| [Country] | NVARCHAR(50) |
| [CurrencyCode] | NVARCHAR(4) |
| [PhoneNum] | NVARCHAR(20) |
| [EMailAddress] | NVARCHAR(50) |
| [Comment] | NVARCHAR(MAX) |
| [PayHold] | BIT |
| [TaxPayerID] | NVARCHAR(20) |
| [PurPoint] | NVARCHAR(4) |
| [VendorTermsCode] | NVARCHAR(4) |
| [VendorTermsDesc] | NVARCHAR(50) |
| [VendorGroupCode] | NVARCHAR(4) |
| [VendorGroupDesc] | NVARCHAR(20) |
| [MinOrderValue] | DECIMAL |
| [CalendarID] | NVARCHAR(8) |
| FactGLJournalDetail | |
|---|---|
| [GLAccountKey] | NVARCHAR(224) |
| [FiscalPeriodKey] | NVARCHAR(47) |
| [GLBookKey] | NVARCHAR(23) |
| [Company] | NVARCHAR(8) |
| [FiscalYear] | INT |
| [JournalNum] | INT |
| [JournalLine] | INT |
| [Description] | NVARCHAR(120) |
| [JEDate] | DATE |
| [FiscalPeriod] | INT |
| [GroupID] | NVARCHAR(9) |
| [PostedBy] | NVARCHAR(75) |
| [PostedDate] | DATETIME |
| [Posted] | BIT |
| [SourceModule] | NVARCHAR(5) |
| [VendorNum] | INT |
| [APInvoiceNum] | NVARCHAR(50) |
| [JournalCode] | NVARCHAR(4) |
| [ARInvoiceNum] | INT |
| [BankAcctID] | NVARCHAR(5) |
| [CheckNum] | INT |
| [CRHeadNum] | INT |
| [Reverse] | BIT |
| [BankTranNum] | INT |
| [BankSlip] | NVARCHAR(15) |
| [RefType] | NVARCHAR(8) |
| [RefCode] | NVARCHAR(8) |
| [ExtCompanyID] | NVARCHAR(8) |
| [ExtRefType] | NVARCHAR(8) |
| [ExtRefCode] | NVARCHAR(8) |
| [GlbJournalNum] | INT |
| [GlbJournalLine] | INT |
| [GlbJournalCode] | NVARCHAR(4) |
| [GlbVendorNum] | INT |
| [GlbAPInvoiceNum] | NVARCHAR(50) |
| [MultiCompany] | BIT |
| [Linked] | BIT |
| [CommentText] | NVARCHAR(MAX) |
| [GlbCompanyID] | NVARCHAR(8) |
| [GlbFiscalYear] | INT |
| [GlbFiscalPeriod] | INT |
| [GlbGroupID] | NVARCHAR(9) |
| [COACode] | NVARCHAR(10) |
| [GLAccount] | NVARCHAR(200) |
| [SegValue1] | NVARCHAR(50) |
| [SegValue2] | NVARCHAR(50) |
| [SegValue3] | NVARCHAR(50) |
| [SegValue4] | NVARCHAR(50) |
| [SegValue5] | NVARCHAR(50) |
| [SegValue6] | NVARCHAR(50) |
| [SegValue7] | NVARCHAR(50) |
| [SegValue8] | NVARCHAR(50) |
| [SegValue9] | NVARCHAR(50) |
| [SegValue10] | NVARCHAR(50) |
| [SegValue11] | NVARCHAR(50) |
| [SegValue12] | NVARCHAR(50) |
| [SegValue13] | NVARCHAR(50) |
| [SegValue14] | NVARCHAR(50) |
| [SegValue15] | NVARCHAR(50) |
| [SegValue16] | NVARCHAR(50) |
| [SegValue17] | NVARCHAR(50) |
| [SegValue18] | NVARCHAR(50) |
| [SegValue19] | NVARCHAR(50) |
| [SegValue20] | NVARCHAR(50) |
| [ExtGLAccount] | NVARCHAR(200) |
| [ExtSegValue1] | NVARCHAR(50) |
| [ExtSegValue2] | NVARCHAR(50) |
| [ExtSegValue3] | NVARCHAR(50) |
| [ExtSegValue4] | NVARCHAR(50) |
| [ExtSegValue5] | NVARCHAR(50) |
| [ExtSegValue6] | NVARCHAR(50) |
| [ExtSegValue7] | NVARCHAR(50) |
| [ExtSegValue8] | NVARCHAR(50) |
| [ExtSegValue9] | NVARCHAR(50) |
| [ExtSegValue10] | NVARCHAR(50) |
| [ExtSegValue11] | NVARCHAR(50) |
| [ExtSegValue12] | NVARCHAR(50) |
| [ExtSegValue13] | NVARCHAR(50) |
| [ExtSegValue14] | NVARCHAR(50) |
| [ExtSegValue15] | NVARCHAR(50) |
| [ExtSegValue16] | NVARCHAR(50) |
| [ExtSegValue17] | NVARCHAR(50) |
| [ExtSegValue18] | NVARCHAR(50) |
| [ExtSegValue19] | NVARCHAR(50) |
| [ExtSegValue20] | NVARCHAR(50) |
| [BookID] | NVARCHAR(12) |
| [CreateDate] | DATE |
| [CurrencyCode] | NVARCHAR(4) |
| [LegalNumber] | NVARCHAR(30) |
| [PerBalFlag] | BIT |
| [TBFlag] | BIT |
| [DailyBalFlag] | BIT |
| [FiscalYearSuffix] | NVARCHAR(8) |
| [FiscalCalendarID] | NVARCHAR(12) |
| [GlbFiscalYearSuffix] | NVARCHAR(8) |
| [GlbFiscalCalendarID] | NVARCHAR(12) |
| [IntermediateProc] | BIT |
| [GenID] | INT |
| [SrcCompany] | NVARCHAR(8) |
| [SrcBook] | NVARCHAR(12) |
| [SrcGLAccount] | NVARCHAR(200) |
| [SrcJrnlCode] | NVARCHAR(4) |
| [SrcJournalNum] | INT |
| [SrcJournalLine] | INT |
| [SrcType] | NVARCHAR(1) |
| [ABTUID] | NVARCHAR(36) |
| [DebitAmount] | DECIMAL |
| [CreditAmount] | DECIMAL |
| [BookDebitAmount] | DECIMAL |
| [BookCreditAmount] | DECIMAL |
| [ParentRUID] | NVARCHAR(36) |
| [HasReverseLine] | BIT |
| [BalanceAcct] | NVARCHAR(200) |
| [TrialAcct] | NVARCHAR(200) |
| [CorrAccUID] | BIGINT |
| [AllocationStamp] | NVARCHAR(20) |
| [BatchID] | NVARCHAR(20) |
| [AllocID] | NVARCHAR(12) |
| [RunNbr] | INT |
| [AllocRunNbr] | INT |
| [AllocTgtNbr] | INT |
| [AllocTgtSeq] | INT |
| [ExtCOACode] | NVARCHAR(10) |
| [MatchCode] | NVARCHAR(9) |
| [MatchDate] | DATE |
| [Reconciled] | BIT |
| [Statistical] | INT |
| [StatUOMCode] | NVARCHAR(6) |
| [DebitStatAmt] | DECIMAL |
| [CreditStatAmt] | DECIMAL |
| [PaymentNumber] | NVARCHAR(15) |
| [SysRevID] | TIMESTAMP |
| [SysRowID] | UNIQUEIDENTIFIER |
| [Sequence] | INT |
| [TranDocTypeID] | NVARCHAR(12) |
| [CustNum] | INT |
| [CloseFiscalPeriod] | INT |
| [SourcePlant] | NVARCHAR(8) |
| [Plant] | NVARCHAR(8) |
| FactGLPeriodActual | |
|---|---|
| [GLAccountKey] | NVARCHAR(224) |
| [FiscalPeriodKey] | NVARCHAR(47) |
| [GLBookKey] | NVARCHAR(23) |
| [Company] | NVARCHAR(8) |
| [BookID] | NVARCHAR(12) |
| [COACode] | NVARCHAR(10) |
| [BalanceAcct] | NVARCHAR(200) |
| [BalanceType] | NVARCHAR(1) |
| [CarryForwardBalance] | DECIMAL |
| [OpenBalance] | DECIMAL |
| [Actual] | DECIMAL |
| [ActualDr] | DECIMAL |
| [ActualCr] | DECIMAL |
| [BalanceStatAmt] | DECIMAL |
| [DebitStatAmt] | DECIMAL |
| [CreditStatAmt] | DECIMAL |
| [OpenBalanceStatAmt] | DECIMAL |
| [CarryForwardBalanceStatAmt] | DECIMAL |
| FactGLPeriodBudget | |
|---|---|
| [GLAccountKey] | NVARCHAR(224) |
| [FiscalPeriodKey] | NVARCHAR(47) |
| [GLBookKey] | NVARCHAR(23) |
| [Company] | NVARCHAR(8) |
| [BookID] | NVARCHAR(12) |
| [COACode] | NVARCHAR(10) |
| [BalanceAcct] | NVARCHAR(200) |
| [BalanceType] | NVARCHAR(1) |
| [BudgetCodeID] | NVARCHAR(16) |
| [Budget] | DECIMAL |
let
Source = (Endpoint as text, Top as number, Skip as number) =>
let
Source = Json.Document(Web.Contents(
"https://erparmsandpit.precise.local/Kinetic2022_2/api/v1/BaqSvc/",
[
RelativePath = Endpoint & "/" & "?$top=" & Number.ToText(Top) & "&$skip=" & Number.ToText(Skip)
]
))
in
Source
in
Source
let
Source = let
Buffer = 20,
GetData = (Top, Skip) =>
let
Result = try CallWebFeed("edw_DimFiscalPeriod", Top, Skip) otherwise null,
BufferedResult = Result
in
BufferedResult,
GeneratedList = List.Generate(
() => [Top = Buffer, Skip = 0, Result = GetData(Buffer, 0)],
each not (List.IsEmpty([Result][value])),
each [Top = [Top] + Buffer, Skip = [Skip] + Buffer, Result = GetData(Buffer, [Skip] + Buffer)],
each [Result][value]
)
in
Table.FromList(List.Combine(GeneratedList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(
Source, "Column1",
{
"Calculated_FiscalPeriodKey",
"Calculated_FiscalYear",
"Calculated_FiscalQuarter",
"Calculated_FiscalPeriod",
"Calculated_FYStartDate",
"Calculated_FYEndDate",
"Calculated_FPEndDate",
"Calculated_FPStartDate",
"Calculated_FYPeriods"
},
{
"_FiscalPeriodKey",
"FiscalYear",
"FiscalQuarter",
"FiscalPeriod",
"FYStartDate",
"FYEndDate",
"FPEndDate",
"FPStartDate",
"FiscalPeriods"
}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Column1",
{
{"FiscalPeriod", Int64.Type},
{"FYStartDate", type date},
{"FYEndDate", type date},
{"FPStartDate", type date},
{"FPEndDate", type date},
{"FiscalPeriods", Int64.Type},
{"FiscalYear", type text},
{"FiscalQuarter", type text}
}
)
in
#"Changed Type"
let
Source = let
Buffer = 10000,
GetData = (Top, Skip) =>
let
Result = try CallWebFeed("edw_DimGLAccount", Top, Skip) otherwise null,
BufferedResult = Result
in
BufferedResult,
GeneratedList = List.Generate(
() => [Top = Buffer, Skip = 0, Result = GetData(Buffer, 0)],
each not (List.IsEmpty([Result][value])),
each [Top = [Top] + Buffer, Skip = [Skip] + Buffer, Result = GetData(Buffer, [Skip] + Buffer)],
each [Result][value]
)
in
Table.FromList(List.Combine(GeneratedList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(
Source, "Column1",
{
"Calculated_GLAccountKey",
"Calculated_Company",
"Calculated_COACode",
"Calculated_GLAccount",
"Calculated_GLAccountDesc",
"Calculated_GLAccountAndDesc",
"Calculated_Type",
"Calculated_GLStatement",
"Calculated_GLCategoryDesc",
"Calculated_ParentGLCategoryID",
"Calculated_ParentGLCategoryDesc",
"Calculated_Segment1Name",
"Calculated_Segment1Value",
"Calculated_Segment1NameAndValue",
"Calculated_Segment2Name",
"Calculated_Segment2Value",
"Calculated_Segment2NameAndValue",
"Calculated_Segment3Name",
"Calculated_Segment3Value",
"Calculated_Segment3NameAndValue",
"Calculated_Segment4Name",
"Calculated_Segment4Value",
"Calculated_Segment4NameAndValue",
"Calculated_Segment5Name",
"Calculated_Segment5Value",
"Calculated_Segment5NameAndValue",
"Calculated_Segment1Category",
"Calculated_L1_GLCategoryID",
"Calculated_L1_GLCategoryDesc",
"Calculated_L1_GLCategoryDescAndID",
"Calculated_L1_GLCategoryOrder",
"Calculated_L2_GLCategoryID",
"Calculated_L2_GLCategoryDesc",
"Calculated_L2_GLCategoryDescAndID",
"Calculated_L2_GLCategoryOrder",
"Calculated_L3_GLCategoryID",
"Calculated_L3_GLCategoryDesc",
"Calculated_L3_GLCategoryDescAndID",
"Calculated_L3_GLCategoryOrder"
},
{
"_GLAccountKey",
"Company",
"COACode",
"GLAccount",
"GLAccountDesc",
"GLAccountAndDesc",
"Type",
"GLStatement",
"GLCategoryDesc",
"ParentGLCategoryID",
"ParentGLCategoryDesc",
"Segment1Name",
"Segment1Value",
"Segment1NameAndValue",
"Segment2Name",
"Segment2Value",
"Segment2NameAndValue",
"Segment3Name",
"Segment3Value",
"Segment3NameAndValue",
"Segment4Name",
"Segment4Value",
"Segment4NameAndValue",
"Segment5Name",
"Segment5Value",
"Segment5NameAndValue",
"Segment1Category",
"L1_GLCategoryID",
"L1_GLCategoryDesc",
"L1_GLCategoryDescAndID",
"L1_GLCategoryOrder",
"L2_GLCategoryID",
"L2_GLCategoryDesc",
"L2_GLCategoryDescAndID",
"L2_GLCategoryOrder",
"L3_GLCategoryID",
"L3_GLCategoryDesc",
"L3_GLCategoryDescAndID",
"L3_GLCategoryOrder"
}
),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each [Segment1Value] <> null and [Segment1Value] <> "")
in
#"Filtered Rows"
let
Source = let
Buffer = 200,
GetData = (Top, Skip) =>
let
Result = try CallWebFeed("edw_DimGLBook", Top, Skip) otherwise null,
BufferedResult = Result
in
BufferedResult,
GeneratedList = List.Generate(
() => [Top = Buffer, Skip = 0, Result = GetData(Buffer, 0)],
each not (List.IsEmpty([Result][value])),
each [Top = [Top] + Buffer, Skip = [Skip] + Buffer, Result = GetData(Buffer, [Skip] + Buffer)],
each [Result][value]
)
in
//List.Combine(GeneratedList)
Table.FromList(List.Combine(GeneratedList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(
Source, "Column1",
{
"Calculated_GLBookKey",
"Calculated_Company",
"Calculated_CompanyName",
"Calculated_CompanyAndName",
"Calculated_GLBookID",
"Calculated_GLBookDesc",
"Calculated_GLBookIDAndDesc",
"Calculated_GLBookIDAndDescAndCurrency",
"Calculated_IsMainGLBook",
"Calculated_GLBookType",
"Calculated_IsGLBookInactive",
"Calculated_COACode",
"Calculated_COADesc",
"Calculated_COASeparatorChar",
"Calculated_COAPerBalFmt",
"Calculated_COATBBalFmt",
"Calculated_CurrencyCode",
"Calculated_CurrencyDesc",
"Calculated_CurrencySymbol",
"Calculated_FiscalCalendarID",
"Calculated_FiscalCalendarDesc",
"Calculated_FiscalCalendarStartDate",
"Calculated_FiscalCalendarEndDate"
},
{
"_GLBookKey",
"Company",
"CompanyName",
"CompanyAndName",
"GLBookID",
"GLBookDesc",
"GLBookIDAndDesc",
"GLBookIDAndDescAndCurrency",
"IsMainGLBook",
"GLBookType",
"IsGLBookInactive",
"COACode",
"COADesc",
"COASeparatorChar",
"COAPerBalFmt",
"COATBBalFmt",
"CurrencyCode",
"CurrencyDesc",
"CurrencySymbol",
"FiscalCalendarID",
"FiscalCalendarDesc",
"FiscalCalendarStartDate",
"FiscalCalendarEndDate"
}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Column1",
{
{"FiscalCalendarStartDate", type date},
{"FiscalCalendarEndDate", type date},
{"Company", type text},
{"CompanyName", type text},
{"CompanyAndName", type text},
{"GLBookID", type text},
{"GLBookDesc", type text},
{"GLBookIDAndDesc", type text},
{"GLBookIDAndDescAndCurrency", type text},
{"IsMainGLBook", type text},
{"GLBookType", Int64.Type},
{"COACode", type text},
{"COADesc", type text},
{"COASeparatorChar", type text},
{"COAPerBalFmt", type text},
{"CurrencyCode", type text},
{"CurrencyDesc", type text},
{"CurrencySymbol", type text},
{"FiscalCalendarID", type text},
{"FiscalCalendarDesc", type text}
}
)
in
#"Changed Type"
let
Source = let
Buffer = 10000,
GetData = (Top, Skip) =>
let
Result = try CallWebFeed("edw_FactGLPeriodActual", Top, Skip) otherwise null,
BufferedResult = Result
in
BufferedResult,
GeneratedList = List.Generate(
() => [Top = Buffer, Skip = 0, Result = GetData(Buffer, 0)],
each not (List.IsEmpty([Result][value])),
each [Top = [Top] + Buffer, Skip = [Skip] + Buffer, Result = GetData(Buffer, [Skip] + Buffer)],
each [Result][value]
)
in
Table.FromList(List.Combine(GeneratedList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(
Source,
"Column1",
{
"Calculated_GLAccountKey",
"Calculated_FiscalPeriodKey",
"Calculated_GLBookKey",
"GLPeriodBal_Company",
"GLPeriodBal_BookID",
"GLBook_COACode",
"GLPeriodBal_BalanceAcct",
"GLPeriodBal_BalanceType",
"GLPeriodBal_CarryForwardBalance",
"GLPeriodBal_OpenBalance",
"GLPeriodBal_BalanceAmt",
"GLPeriodBal_DebitAmt",
"GLPeriodBal_CreditAmt",
"GLPeriodBal_BalanceStatAmt",
"GLPeriodBal_DebitStatAmt",
"GLPeriodBal_CreditStatAmt",
"GLPeriodBal_OpenBalanceStatAmt",
"GLPeriodBal_CarryForwardBalanceStatAmt"
},
{
"_GLAccountKey",
"_FiscalPeriodKey",
"_GLBookKey",
"Company",
"BookID",
"COACode",
"BalanceAcct",
"BalanceType",
"CarryForwardBalance",
"OpenBalance",
"Actual",
"ActualDr",
"ActualCr",
"BalanceStatAmt",
"DebitStatAmt",
"CreditStatAmt",
"OpenBalanceStatAmt",
"CarryForwardBalanceStatAmt"
}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Column1",
{
{"CarryForwardBalance", type number},
{"OpenBalance", type number},
{"Actual", type number},
{"ActualDr", type number},
{"ActualCr", type number},
{"BalanceStatAmt", type number},
{"DebitStatAmt", type number},
{"CreditStatAmt", type number},
{"OpenBalanceStatAmt", type number},
{"CarryForwardBalanceStatAmt", type number}
}
)
in
#"Changed Type"
let
Source = let
Buffer = 10000,
GetData = (Top, Skip) =>
let
Result = try CallWebFeed("edw_FactGLPeriodBudget", Top, Skip) otherwise null,
BufferedResult = Result
in
BufferedResult,
GeneratedList = List.Generate(
() => [Top = Buffer, Skip = 0, Result = GetData(Buffer, 0)],
each not (List.IsEmpty([Result][value])),
each [Top = [Top] + Buffer, Skip = [Skip] + Buffer, Result = GetData(Buffer, [Skip] + Buffer)],
each [Result][value]
)
in
Table.FromList(List.Combine(GeneratedList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(
Source,
"Column1",
{
"Calculated_GLAccountKey",
"Calculated_FiscalPeriodKey",
"Calculated_GLBookKey",
"GLBudgetDtl_Company",
"GLBudgetDtl_BookID",
"GLBook_COACode",
"GLBudgetDtl_BalanceAcct",
"GLBudgetDtl_BalanceType",
"GLBudgetDtl_BudgetCodeID",
"GLBudgetDtl_BudgetAmt",
"GLBudgetDtl_BudgetStatAmt"
},
{
"_GLAccountKey",
"_FiscalPeriodKey",
"_GLBookKey",
"Company",
"BookID",
"COACode",
"BalanceAcct",
"BalanceType",
"BudgetCodeID",
"Budget",
"BudgetStatAmt"
}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Column1",
{
{"Budget", type number},
{"BudgetStatAmt", type number}
}
)
in
#"Changed Type"