Archive Scenarios in Partition

Some Scenarios' Members become irrelevant over time. To optimize the performance of your partitions and, by extension, Application, you can archive Fact values associated with deprecated Scenarios. This article will discuss how you can set up an Archive Partition to store obsolete data. 

Tip: If you want to archive Scenarios in a SQL view, refer to this article. 

Before you Start

Here are some questions to ask if you are considering archiving Members using this article's method:

Tip: You can use both this and the SQL View method of storing archived Scenarios. You may need to adjust the script and Attributes' names so that you get your desired outcome.  

Create an Attribute

1. Go to the Attributes node of the Dimension with the Members to be archived.

2. Click Add to create an Attribute.

3. Enter Type for the Property Name and leave the rest of the fields as is. 

4. Click Save.

5. Navigate to the Member List with the Members to be archived.

6. Under the Type column, label the Dimension Members to be archived as Archive.

Create an Archive Partition

If you want to store your data in Kepion, but do not want it in the ROLAP partitions (Writeback, Calculation, etc.), you can create an Archive partition. Removing Fact data from your ROLAP partitions can help with App performance if there is a lot of data to process.

1. Go to the Data node of the relevant Model and click Add.

2. Configure the partition as follows:

• Partition Name as Archive Partition
• Measure Group as CoreMG
• Storage Mode as MOLAP

MOLAP partitions use static views of your data. Static views improve App performance but require processing in order to show any data changes. To learn more about storage types, view Session 10 - Managing Data and Partitions.

Create SQL Script and Procedure for Archiving

1. Write a SQL query that does all of the following:

• Joins the Dimension table (D_Scenario) and Hierarchy table (H_Scenario_Scenario) based on their MemberIds and has their Type equal to Archive.
• Connects the join to the partition of your choice in the relevant Model using the correct Dimension (ScenarioID).
• Moves the data from the partition of your choice to the Archive partition.
• Deletes the archived data from the original partition.
• (Recommended) Checks for any duplicate data between the original partition and Archive partition before moving the data.

Your code may look like this:

-- This snippet of code will help Modelers move data from Partition
-- to Partition using Attributes. This particular code uses a "Type" Attribute
-- in the Scenario Dimension. The "Type" it is searching for it called Archive.

-- Deletes data from Archive Partition that has the same Account, Time, Product,
-- Scenario IDs as the Writeback table. These values will be brought
-- back in at the next step.
DELETE Arc FROM [dbo].[F_Reporting_CoreMG_Archive Partition] AS Arc
INNER JOIN [dbo].[F_Reporting_CoreMG_Writeback] AS Write
ON (Arc.[ScenarioID] = Write.[ScenarioID]
AND Arc.[AccountID] = Write.[AccountID]
AND Arc.[TimeID] = Write.[TimeID]
AND Arc.[ProductID] = Write.[ProductID]
AND Arc.[Status] = Write.[Status]);

-- Grabs the data from the Writeback Partition (nicknamed Write)
-- and moves the data to the Archive Partition using the Scenario Attribute Type.
INSERT INTO [dbo].[F_Reporting_CoreMG_Archive Partition] (
[AccountID]
,[TimeID]
,[ProductID]
,[ScenarioID]
,[Value]
,[Comment]
,[Status] )
SELECT 
Write.[AccountID]
,Write.[TimeID]
,Write.[ProductID]
,Write.[ScenarioID]
,Write.[Value]
,Write.[Comment]
,Write.[Status]
FROM [dbo].[D_Scenario] AS D
INNER JOIN [dbo].[F_Reporting_CoreMG_Writeback] AS Write
ON D.[MemberId] = Write.[ScenarioID]
WHERE D.[Type] = N'Archive';


-- Deletes data from the Writeback Partition that is considered Archive.
DELETE FROM [dbo].[F_Reporting_CoreMG_Writeback]
WHERE [ScenarioID] IN (
SELECT D.[MemberId] FROM [dbo].[D_Scenario] AS D
INNER JOIN [dbo].[H_Scenario_Scenario] AS H 
ON D.[MemberId] = H.[MemberId]
WHERE D.[Type] = N'Archive');

2. Open SQL Server Management Studio and go to Archive Members >Programmability.

3. Right-click Stored Procedures and select New > Stored Procedure...

4. Insert the code into the Procedure.

5. Select Save and name it USR_View_F_Reporting_CoreMG_ArchiveScenario.

Tip: Normal naming convention for Kepion includes the USR_ prefix for any user created object. In the case of the above example, we will name it USR_Pro_F_Reporting_CoreMG_ArchiveScenarioPartition.

6. Return to Kepion and deploy your Application.

Check back on the Archive partition to ensure that the script runs as expected.