Archive Scenarios in SQL View – Kepion Support Center

Some Scenarios' Members become irrelevant over time. To optimize the performance of your partitions and, by extension, Application, we recommend you archive Fact values used in deprecated Scenarios. If you do not want to store the data in Kepion, you can move the data to a user-defined SQL table, which you can access with a SQL View.

In this article, we will explain how you archive Members in a user-defined SQL table.

Before you Start

Here are some questions to ask before you follow this method:

	SQL View
Is the archived data accessible through Kepion?	Yes
Does going through this process improve data access for ROLAP data?	Yes
Is the data stored in Analysis Server?	Yes (MOLAP partition)
When would this example be used?	If you need to define what counts as archived data, creating a user-defined table will help. An example can be scenarios that were relevant ten years ago but are no longer relevant.

Tip: You can use this method and the Archive Scenarios in Partition method together. You may need to adjust the script and Attributes' names so you get the desired outcome.

Create Attributes and Partition

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

2. Click Add to create an Attribute.

3. Name it Type and keep 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.

7. Create another Attribute:

Name as Archive in AS
Type set to Byte
Default set to 0

8. Click Save.

9. Label all Scenarios that need to be archived 1 (Yes) and all Scenarios that need to be kept in the Hierarchy table as 0 (No).

Note: Any Scenario marked with 1 will be kept in the SQL view we will be creating; all Scenarios marked as Archive (Type) and 0 (Archive in AS) will be removed from the Scenario Hierarchy.

10. Go to the Data node of the relevant Model and select Add.

11. 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. They 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.

Store Data in User-Defined Table

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

Creates a user-defined table. If that table is already created, go to the next steps.
Joins the Dimension table (D_Scenario) and Hierarchy table (H_Scenario_Scenario) based on their MemberIds and has their Type equal to Archive and Archive in AS equal to 1.
Connects the join to the partition of your choice using the correct Dimension (ScenarioID).
Moves the data from the partition of your choice to the user-defined table.
Deletes the archived data from the user-defined table.
Removes the Archive Members whose Type is equal to 0 from the Hierarchy table (H_Scenario_Scenario) but keeps them in the Dimension table (D_Scenario).
Removes Archive Members from the Hierarchy Order table (HO_Scenario_Scenario).
(Recommended) Checks for any duplicate data between the original partition and the user-defined table.

Your code may look like this:

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

-- Create a table to hold the archived data if it does not already exist.
IF OBJECT_ID('USR_Reporting_CoreMG_ArchivedScenario', 'U') IS NOT NULL
BEGIN
PRINT 'USR_Reporting_CoreMG_ArchivedScenario table already exists in this database.'
END
ELSE
BEGIN
PRINT 'USR_Reporting_CoreMG_ArchivedScenario table is being created.'
CREATE TABLE [dbo].[USR_Reporting_CoreMG_ArchivedScenario] (
[ID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[AccountID] [smallint] NULL,
[TimeID] [int] NULL,
[ProductID] [smallint] NULL,
[ScenarioID] [smallint] NULL,
[Value] [float] NULL,
[Status] [smallint] NULL,
[Comment] [nvarchar](max) NULL,
);
END

-- Deletes data from the SQL table 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].[USR_Reporting_CoreMG_ArchivedScenario] 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 SQL table using the Scenario Attribute Type.
INSERT INTO [dbo].[USR_Reporting_CoreMG_ArchivedScenario] (
[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');

-- Deletes the archived Scenario Members from the Hierarchy but not the Dimension.
DELETE H FROM [dbo].[H_Scenario_Scenario] AS H
INNER JOIN [dbo].[D_Scenario] AS D ON D.[MemberId] = H.[MemberId]
WHERE D.[Type] = N'Archive'
AND D.[Archive in AS] = 0;

-- Deletes the archived Scenario Members from the Hierarchy Order List
-- but not the Dimension.
DELETE HO FROM [dbo].[HO_Scenario_Scenario] AS HO
INNER JOIN [dbo].[D_Scenario] AS D ON D.[MemberId] = HO.[MemberId]
WHERE D.[Type] = N'Archive'
AND D.[Archive in AS] = 0;

2. Once you have verified your code works as intended, create a Procedure in SQL Server Management Studio called USR_Pro_F_Reporting_CoreMG_ArchiveScenarioView.

Create a View

1. Right-click Views and select New View...

2. Write the SQL query or use the graphical Query Designer to create the View. Your query may look like this:

-- Only show Data from USR Archive table that is labeled as Yes for Archive in AS.
SELECT USR.[ID]
,USR.[AccountID]
,USR.[TimeID]
,USR.[ProductID]
,USR.[ScenarioID]
,USR.[Value]
,USR.[Status]
,USR.[Comment]
FROM dbo.[USR_Reporting_CoreMG_ArchivedScenario] AS USR 
INNER JOIN
dbo.[D_Scenario] AS S ON USR.ScenarioID = S.MemberId
WHERE S.[Archive in AS] = 1

3. Select Save and name it USR_View_F_Reporting_CoreMG_ArchiveScenario.

4. Click Execute to verify it is pulling the correct data.