Como efetuar um recarregamento total dos dados?

Este documento descreve como efetuar um recarregamento total dos dados sem ser necessário reinstalar a solução.

Pré-Requisitos

• Microsoft Sql Server Management Studio;

PASSO 1 – Executar Query

O método utilizado para fazer um recarregamento total dos dados consiste em executar uma query com o seguinte propósito:

• Na Base de Dados Organization colocar o valor NULL no campo "Metadata" e aplicado a todos os registos.
• Na Base de Dados [BusinessAnalyticsDW] apagar todos os dados carregados anteriormente.
• Na Base de Dados [BusinessAnalyticsST] apagar todos os dados carregados anteriormente.

Executando a query, temos:

use [PRI-ORG-DEMO]

UPDATE [Integration].[ExtractionTaskPackages]
SET [Metadata]=NULL
FROM [Integration].[ExtractionTaskPackages]

use [BusinessAnalyticsDW]

truncate table [core].[DimCompanies]
truncate table [stdfa].[DimAccounts]
truncate table [stdfa].[DimAltAccounts]
truncate table [stdfa].[DimAccountTypes]
truncate table [stdfa].[DimBankAccounts]
truncate table [stdfa].[DimCostCenters]
truncate table [stdfa].[DimTreasuryItems]
truncate table [stdfa].[DimTreasuryMovementTypes]
truncate table [stdfa].[FactAccounting]
truncate table [stdfa].[FactAccountingProject]
truncate table [stdfa].[FactAccountingBudget]
truncate table [stdfa].[FactAccountingProjectBudget]
truncate table [stdfa].[FactFinCurrentAccounts]
truncate table [stdfa].[FactFinTreasuryMovements]
truncate table [stdfa].[FactSettlements]
truncate table [stdfa].[DimFlows]
truncate table [stdfa].[FactFlows]
truncate table [stdfa].[FactAccountingTreasury]
truncate table [stdfa].[FactMovements]
truncate table [stdfa].[FactPendingDocuments]
truncate table [stdmm].[DimItems]
truncate table [stdmm].[DimLot]
truncate table [stdmm].[DimWarehouses]
truncate table [stdmm].[FactInventory]
truncate table [stdmm].[FactPurchases]
truncate table [stdmm].[FactAvgCostPrice]
truncate table [stdmm].[FactInventoryValues]
truncate table [stdms].[DimSalesPersons]
truncate table [stdms].[DimSalesRegions]
truncate table [stdms].[DimSections]
truncate table [stdms].[FactSales]
truncate table [stdcommon].[DimBranches]
truncate table [stdcommon].[DimDepartments]
truncate table [stdcommon].[DimDocuments]
truncate table [stdcommon].[DimEntities]
truncate table [stdcommon].[DimEntityTypes]
truncate table [stdcommon].[DimEstablishments]
truncate table [stdcommon].[DimPostingTypes]
truncate table [stdcommon].[DimProjects]
truncate table [stdcommon].[DimGeographies]
truncate table [stdcommon].[DimProjectelements]
truncate table [stdcommon].[DimUnits]
truncate table [stdcommon].[DimReversalReasons]
truncate table [stdhr].[DimAbsenceTypes]
truncate table [stdhr].[DimAttendanceItems]
truncate table [stdhr].[DimContractTypes]
truncate table [stdhr].[DimContractualSituations]
truncate table [stdhr].[DimEducationalLevels]
truncate table [stdhr].[DimEmployees]
truncate table [stdhr].[DimExpenseTypes]
truncate table [stdhr].[DimHumanResourcesItems]
truncate table [stdhr].[DimJobs]
truncate table [stdhr].[DimOverTimeTypes]
truncate table [stdhr].[DimProfessionalCategories]
truncate table [stdhr].[DimTrainingAreas]
truncate table [stdhr].[FactPayroll]
truncate table [stdhr].[FactTraining]
truncate table [stdhr].[FactWorkAccidents]
truncate table [stdhr].[FactWorkHours]

use [BusinessAnalyticsST]

truncate table [core].[DimCompanies]
truncate table [stdfa].[DimAccounts]
truncate table [stdfa].[DimAltAccounts]
truncate table [stdfa].[DimAccountTypes]
truncate table [stdfa].[DimAccountTypes]
truncate table [stdfa].[DimBankAccounts]
truncate table [stdfa].[DimCostCenters]
truncate table [stdfa].[DimTreasuryItems]
truncate table [stdfa].[DimTreasuryMovementTypes]
truncate table [stdfa].[FactAccounting]
truncate table [stdfa].[FactAccountingProject]
truncate table [stdfa].[FactAccountingBudget]
truncate table [stdfa].[FactAccountingProjectBudget]
truncate table [stdfa].[FactFinCurrentAccounts]
truncate table [stdfa].[FactFinTreasuryMovements]
truncate table [stdfa].[FactSettlements]
truncate table [stdfa].[DimFlows]
truncate table [stdfa].[FactFlows]
truncate table [stdfa].[FactAccountingTreasury]
truncate table [stdfa].[FactMovements]
truncate table [stdfa].[FactPendingDocuments]
truncate table [stdmm].[DimItems]
truncate table [stdmm].[DimLot]
truncate table [stdmm].[DimWarehouses]
truncate table [stdmm].[FactInventory]
truncate table [stdmm].[FactPurchases]
truncate table [stdmm].[FactAvgCostPrice]
truncate table [stdmm].[FactInventoryValues]
truncate table [stdms].[DimSalesPersons]
truncate table [stdms].[DimSalesRegions]
truncate table [stdms].[DimSections]
truncate table [stdms].[FactSales]
truncate table [stdcommon].[DimBranches]
truncate table [stdcommon].[DimDepartments]
truncate table [stdcommon].[DimDocuments]
truncate table [stdcommon].[DimEntities]
truncate table [stdcommon].[DimEntityTypes]
truncate table [stdcommon].[DimEstablishments]
truncate table [stdcommon].[DimPostingTypes]
truncate table [stdcommon].[DimProjects]
truncate table [stdcommon].[DimGeographies]
truncate table [stdcommon].[DimProjectelements]
truncate table [stdcommon].[DimUnits]
truncate table [stdcommon].[DimReversalReasons]
truncate table [stdhr].[DimAbsenceTypes]
truncate table [stdhr].[DimAttendanceItems]
truncate table [stdhr].[DimContractTypes]
truncate table [stdhr].[DimContractualSituations]
truncate table [stdhr].[DimEducationalLevels]
truncate table [stdhr].[DimEmployees]
truncate table [stdhr].[DimExpenseTypes]
truncate table [stdhr].[DimHumanResourcesItems]
truncate table [stdhr].[DimJobs]
truncate table [stdhr].[DimOverTimeTypes]
truncate table [stdhr].[DimProfessionalCategories]
truncate table [stdhr].[DimTrainingAreas]
truncate table [stdhr].[FactPayroll]
truncate table [stdhr].[FactTraining]
truncate table [stdhr].[FactWorkAccidents]
truncate table [stdhr].[FactWorkHours]

PASSO 2 – Executar ETL

Neste passo, temos 2 alternativas:

• Aguardar pela execução automática (Scheduler).
• Forçar a execução dos packages, ver artigo Como forçar a execução dos processos ETL?