Skip to content

Solution Overview

Background

We have built the following:

  • a DWH that feeds data from various systems such as ...
  • a Management Report
  • etc.

Data Sources

Source System Version Access Refresh Other Remarks
Business Central On prem On prem, 21.0 Via HCM Daily, some tables are incremental This is managed by Christiaens
Invoice Line History Excel on Teams Via Power BI Connector Daily Contains the legacy INVOICE_LINE
Client History Excel on Teams " " Contains the legacy CLIENT

Data Lineage

graph LR

   BC[fa:fa-cloud BC]
   ILH[fa:fa-file-excel Invoice Line History]
   CH[fa:fa-file-excel Client History]

   BS[fa:fa-folder Blob Storage]
   DWH[fa:fa-database DWH<br>SQL Server]

   AF((fa:fa-chevron-circle-right<br>Azure<br>Function))
   ADF((fa:fa-chevron-circle-right<br>Azure<br>Data<br>Factory))
   SMS((fa:fa-star-of-life<br>Semantic<br>Model<br>Sales))
   SML((fa:fa-star-of-life<br>Semantic<br>Model<br>Stock))
   RS1(fa:fa-chart-pie Sales<br>Report)
   RS2(fa:fa-chart-pie Supplier<br>Report)

   PDFS(fa:fa-file Stock<br>PDF)
   RL(fa:fa-chart-pie Stock<br>Report)

   ILH-->BS
   CH-->BS

   BC-->AF-->ADF

   BS-->ADF-->DWH

   DWH-->SMS
   DWH-->SML

   SMS-->RS1
   SMS-->RS2
   SMS-->PDFS

   SML-->RL

   style DWH fill:#007fff
   style BS fill:#007fff
   style AF fill:#007fff
   style ADF fill:#007fff
   style SMS fill:#FFD700,color:#000
   style SML fill:#FFD700,color:#000
   style RS1 fill:#FFD700,color:#000
   style RS2 fill:#FFD700,color:#000

   style PDFS fill:red,color:#fff
   style RL fill:#FFD700,color:#000

Perspectives

Perspective KPQ
Project Which projects are profitable?

Entities

Entity System Description Count
COUNTERPARTY BC Counterparties are both clients and suppliers. They are discriminated by the Type flag 3700
ARTICLE BC Articles have a SKU code which identifies them. A challenge is that articles are replaced by newer models, and then we would like to report on a consolidated basis. 1000, plus 2000 historic
PRICE_LIST BC Price Lists are virtual objects that are combined by business logic from a variety of sources.
OFFER BC There is no sale without an offer. Offers have multiple versions.
ORDER BC Each Order is linked to an Offer version.
INVOICE BC Typically, there is a single invoice per Order, but there are exceptions. Sometimes, there are multiple invoices per order. Sometimes, an invoice is sent on multiple orders. ~15k INVOICE_LINES/mth
CHANNEL BC A channel is similar to a distributor, but not exactly the same thing. They are modelled as financial dimensions in BC.

Workspaces

A table containing the workspaces and who is using them.

Workspace Users Access
Project Managers Project Managers, e.g. Philipp Users added to WS individually
Accounting Accounting, e.g. Manuel AD Group Accounting

Semantic Models

A table containing all the semantic models.

Semantic Model File Name Workspace Perspective
Project vns_project.pbix Power Partners Project

Reports

A table containing Power BI Reports and Paginated Reports.

Report File Name Key Question Perspective Workspace Semantic Model
Project vns_project.pbix Which projects are profitable? Project Power Partners Project