Legacy Environment
The legacy environment consists of
- Smart BI (a BI product sold by Delaware, a Belgium data company)
- additional data sources, namely salary statistics
Technically, the solution runs on:
- a SQL Server 2016 database
- SSAS
- SSIS
Unfortunately, it is not easy to inspect a running version of Smart BI. Also, we cannot debug.
This is how we can get a glimpse:
- look at the tables in SQL Server via SSMS (on KDGERPVS02)
- look at the code in SSIS (on a local version of VS 2019)
Installation of VS 2019 / SSIS
-
Install VS 2019 (Pro or Enterprise)
- Install "Data Storage and Processing" workload
-
From Extensions
- Install Data Tools (SSDT)
- Install Microsoft Analysis Services Projects
Infrastructure
| Name | Type | Description | Details |
|---|---|---|---|
| KDGERPVS02 | Server | WS 2019 | This is the server that we can use to get access to the KdG environment. We can use SSMS 2014 to access the legacy pipelines and the SQL server |
| KDGERPVS01 | Server | WS 2012 | This is a legacy WS 2012 instance. They cannot give us remot access to it. |
| F&O | Web App Sandbox | https://kdg-test.sandbox.operations.eu.dynamics.com/ | This is the new, target environment. We can log in with our sonical user. |
| kdgerpbi01 | SQL Server 13 | Hosts the DWH, SSAS, SSIS, SSRS | |
| SMART_BI_2012_R3_DWH | DWH | ||
| ReportServer | SSRS | Only used for financials. Does not need to be migrated | |
| BI | SSAS | Data Source: SMART BI 2012 DWH | Cube with measures (will be done in DAX) |
SSIS
These can be found in the solution Git\kdg\legacy\BI\Technical Solution\SSIS 2008 R2\SMART BI\ETL SMART BI.sln.
Staging
These pipelines can be found in the project Load Data in staging
There is one huge pipeline that loads all the data from the source tables into staging. It uses a stored procedure to load the data from AX:
DWH
These pipelines can be found in the project Load Data in DWH
The load from staging to DWH essentially copies, and looks up the IDs of the dimensions.
DWH Tables
Dimensions
| Name | Area | Fact Tables | Description | Number of Records |
|---|---|---|---|---|
| DimAcademiejaar | Studies | Finance Facts *) | Academic year | 13 |
| DimActiviteit | ? | Finance Facts *) | Babykunde, Lego, Samenwerken, with an ActivityCode and Id | 683 |
| DimAfstudeerrichting | Studies | Finance Facts *) | Field of study | 55 |
| DimBudget* | Finance | GeneralLedgerBudget | TODO: understand budget process | |
| DimCommittedCostType | CommittedCost | TODO: What is this? | 12 | |
| DimCompany | General | 2 | ||
| DimCurrency | General | 175 | ||
| DimCustomer | General | 70'000 | ||
| DimDeliveryMode | Delivery | PurchaseOrder, SalesOrder | Shipped, Air, etc. TODO: do they really need this? |
6 |
| DimDeliveryTerms | Delivery | PurchaseOrder, SalesOrder | COST AND FREIGHT, etc TODO: really needed? |
14 |
| DimDirectie_OG | General | Finance Facts | Department (HR, IT, etc.) | 23 |
| DimDocumentState | Finance | PurchaseOrder | Rejected, Approved, Finalized, etc. | 8 |
| DimDocumentType | Workflow | Workflow | Aankooporders, Leveranciersfacturen, Purchase Orders, etc. | 8 |
| DimDossiersoort | Finance | Finance Facts | Studiekosten, Psychotherapie TODO: ? |
12 |
| DimEmployee | General | PurchaseOrder, PurchseRequisition, Workflow | 3676 | |
| DimExchangeRateType | Finance | Finance Facts | Needed for budgets, e.g. Default average rate, Default budget rate, etc. | 9 |
| DimFinancieringsbron | Finance | Finance Facts | Financing Source | 62 |
| DimForecastModel | Finance | FactSalesBudget | General, Origina forecast vs budget, Remaining budget | 4 |
| DimGLAccount | Finance | Finance Facts | AccountCode, Main / Group / etc. | 2218 |
| DimGLAccountHierarchy | Finance | GL, GeneralLedgerBudget, SalesOrder | Assigns accounts to reporting groups and categories (similar to our BC excel) | 2582 |
| DimOpleiding | Studies | Finance Facts | Study programme / degree, e.g. Bachelor of Chemistry | 132 |
| DimOutstandingPeriod | Finance | AccountsPayable, AccountsReceivable | <0, 0-7, 8-15, 16-30, 31-60, etc. Probably used for DSO and similar | 8 |
| DimPaymentTerms | Finance | PurchaseOrder, SalesOrder | 8 days, 30 days, etc. | 24 |
| DimProduct | General | PurchaseOrder, PurchaseRequisition, SalesBudget, SalesOrder | Tuition Fees, etc. | 10'865 |
| DimProject_Fin | Finance | Finance Facts | Erasmus+, Parental, Ecolight, etc. | 534 |
| DimPurchaseCategory | Finance | PurchaseOrder, PurchaseRequisition | Sponsoring, Recruitment, etc. | 774 |
| DimPurchaseOrderStatus | Finance | PurchaseOrder | OPEN, RECEIVED, INVOICED, etc. | 7 |
| DimRatio | Ratio | FactRatioTarget | linked to FactRatioTarget | 22 |
| DimSalesOrderStatus | Finance | FactSalesOrder | Open, Delivered, Invoiced, Canceled | 5 |
| DimSamenwerking | General | Finance Facts | Partnerships? | 4 |
| DimSourceDocument | Workflow | FactWorkflow | FactWorkflow | 170'000 |
| DimSupplier | General | AccountsPayable, GL, PurchaseOrder, PurchaseRequisition | AccountsPayable, PurchaseOrder, G/L | 14'000 |
| DimTrackingContext | Workflow | FactWorkflow | Task, Step, Condition, Decision | 15 |
| DimTrackingType | Workflow | FactWorkflow | Submission, Activation, Denied | 41 |
| DimWorkflowCOnfiguration | Workflow | FactWorkflow | Controle aanvraag tot bestelling | 30 |
*) Finance Facts: FactAccountsPayable, FactAccountsReceivable, FactBudgetPlan, FactGeneralLedger, FactGeneralLedgerBudget, FactPurchaseOrder, FactPurchaseRequisition, FactSalesOrder
Facts
| Name | Area | Dims | Description | Number of Records |
|---|---|---|---|---|
| FactAccountsPayable | Finance | 351'000 | ||
| FactAccountsReceivable | Finance | 167'000 | ||
| FactAccountsReceivable_PaymentPlans | Finance | 688'000 | ||
| FactBudgetPlan | Finance | 135'000 | ||
| FactGeneralLedger | Finance | 2'638'000 | ||
| FactGeneralLedgerBudget | Finance | 56'000 | ||
| FactPurchaseInvoice | Finance | 81'000 | ||
| FactPurchaseOrder | Finance | 87'000 | ||
| FactPurchaseRequisition | Finance | 89'000 | ||
| FactRatioTarget | Ratio | 132 | ||
| FactSalesBudget | Finance | 65'000 | ||
| FactSalesInvoice | Finance | 65'000 | ||
| FactSalesOrder | Finance | 58'000 | ||
| FactWorkflow | Workflow | 2'945'232 | ||
| FactWorkflow_Telling | Workflow | 2'945'232 |
You can check out which Dim is used by which Fact by looking at the Cube in SSAS:
SSAS
Cube Structure
The Cube Structure is reflecting the DWH very closely. Aggregations are mostly sums.
There are about 20 measures that actually calculate something:
(Similar to DAX measures in Power BI)
Questions
| Question | Answer |
|---|---|
There is a table called DimRatio. There is also an Excel sheet Git\kdg\legacy\BI\IN\ETL\FinancialRatios.xls. What is this? |
|
There are many entries in FactWorkflow. But we never talked about this. What is it? |