Subset maintenance in V12

Ralf Zerfass

Hi all,

we're just in transition from V11 to V12 for many custom applications and I'm missing an easy way to create subsets, as it was possible in V11 dimension editor.

In DEPM-Dashboards at first glance there is no way to define subsets.Using XML-Requests seems to be a a way, but it is not user-friendly.

What is the best way to create new or maintain existing subsets of a dimension in V12?

Are dynamic subsets (e.g. attribute-driven) still possible?

Thanks for any tipps or examples

Ralf

com_proactive_201104.pdf

unknown

Ralf,

Right now there is no easy way to create subsets in V12 MT.  We have used App Engine processes to create subsets.  In the example below, we use the staging table we use to load our account dimension to build subsets based on account type (attribute).   We run this process from an ION workflow after we load the account dimension.   I hope this helps.  Thx - Bill Masterson

#define EngineVersion 5.0
#define RuntimeVersion 5.0

bool Build_Account_Type_Subsets()
@Description: "Process to create the Account Type based Subsets";
@Category: "Utilities";
@Returns: "True if successful, false if not";
{

	//--- Declare constants ---
	string consProcessName = GetProcessName();
	string consDimAccount = "ACCOUNT";
	string consHierarchy = "ACCOUNT";

	string consSubBS = "Balance_Sheet_Accounts";
	string consSubBSBase = "Balance_Sheet_Accounts_Base";
	string consSubNonBS = "Non_Balance_Sheet_Accounts";
	string consSubISBase = "Income_Statement_Accounts_Base";
	string consSubStatAccountsBase = "Stat_Accounts_Base";
	string consSubIS = "Income_Statement_Accounts";
	string consSubStatAccounts = "Stat_Accounts";

	string consAcctTypeAsset = "A";
	string consAcctTypeLiability = "L";
	string consAcctTypeEquity = "Q";
	string consAcctTypeRevenue = "R";
	string consAcctTypeExpense = "E";
	string consOLAPConnect = "DEPM";
	string consSQLConnect = "DEPM_STAGING";

	//--- Define Processing Variables ---

	string errorMessage = "";
	int errorCode = 0;

	//--- Connect to DB ---
	OLAPConnection OLAPConnect = OLAPCreateNamedConnection(consOLAPConnect);
	SQLConnection SQLConnect = SQLCreateNamedConnection(consSQLConnect);

	//--- Build string lists for subset members ---
	StringList slBSAcctElements = CreateStringList();
	StringList slBSAcctElementsBase = CreateStringList();
	StringList slNonBSAcctElements = CreateStringList();
	StringList slSubISAccounts = CreateStringList();
	StringList slSubStatAccounts = CreateStringList();
	StringList slSubISAccountsBase = CreateStringList();
	StringList slSubStatAccountsBase = CreateStringList();

	//--- Note:  Subset list necessary to determine if subset already exists ---

	StringList slSubsets = OLAPGetSubsets(OLAPConnect, consDimAccount, consHierarchy, OlapSubsetAccessPublic);

	//--- Retrieve list of accounts and associated account types from Staging Account ---
	string sAttributeSQL = "SELECT DISTINCT AccountType, GeneralLedgerChartAccount FROM Stage_Account";
	SQLData rsAttrList = SQLExecuteQuery(SQLConnect,sAttributeSQL);

	//--- Process Accounts ---

	foreach (SQLDataRow AttrRow in rsAttrList)
	{
		//--- Get Values from SQL row ---
		string sAccountType = StringTrim(SQLDataRowGetString(AttrRow, "AccountType"));
		string sAccount = StringTrim(SQLDataRowGetString(AttrRow, "GeneralLedgerChartAccount"));

		//--- Check account type attribute to determine which subsets the account belongs ---
		if(sAccountType == consAcctTypeAsset or sAccountType == consAcctTypeLiability or sAccountType == consAcctTypeEquity)
		{
			Append(slBSAcctElements, sAccount);
			if(OLAPIsBaseElement(OLAPConnect, consDimAccount, sAccount) == true)
			{
				Append(slBSAcctElementsBase, sAccount);
			}

		}
		else
		{
			if(sAccountType == consAcctTypeRevenue or sAccountType == consAcctTypeExpense)
			{
				Append(slSubISAccounts, sAccount);

				if(OLAPIsBaseElement(OLAPConnect, consDimAccount, sAccount) == true)
				{
					Append(slSubISAccountsBase, sAccount);
				}

			}
			else
			{
				Append(slNonBSAcctElements, sAccount);
				if(OLAPIsBaseElement(OLAPConnect, consDimAccount, sAccount) == true)
				{
					Append(slSubStatAccountsBase, sAccount);
				}
				Append(slSubStatAccounts, sAccount);
			}
		}
	}

	//--- Write subsets.  First check to see if it exists.  If it exists, modify the definition.  otherwise create the subset ---
	try
	{
		if(Contains(slSubsets,consSubBS) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubBS, slBSAcctElements);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubBS, slBSAcctElements,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	try
	{
		if(Contains(slSubsets,consSubBSBase) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubBSBase, slBSAcctElementsBase);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubBSBase, slBSAcctElementsBase,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	try
	{
		if(Contains(slSubsets,consSubNonBS) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubNonBS, slNonBSAcctElements);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubNonBS, slNonBSAcctElements,OlapSubsetAccessPublic);
		}

	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}


	try
	{
		if(Contains(slSubsets,consSubISBase) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubISBase, slSubISAccountsBase);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubISBase, slSubISAccountsBase,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	try
	{
		if(Contains(slSubsets,consSubStatAccountsBase) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubStatAccountsBase, slSubStatAccountsBase);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubStatAccountsBase, slSubStatAccountsBase,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	try
	{
		if(Contains(slSubsets,consSubIS) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubIS, slSubISAccounts);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubIS, slSubISAccounts,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	try
	{
		if(Contains(slSubsets,consSubStatAccounts) == true)
		{
			OLAPModifySubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubStatAccounts, slSubStatAccounts);
		}
		else
		{
			OLAPCreateSubsetDefinition(OLAPConnect, consDimAccount, consHierarchy, consSubStatAccounts, slSubStatAccounts,OlapSubsetAccessPublic);
		}
	}
	catch (errorMessage, errorCode)
	{
		NotifyError("Error - " + ToString(errorCode) + " " + errorMessage);
		return false;
	}

	//--- Disconnect ---
	OLAPDisconnect(OLAPConnect);
	SQLDisconnect(SQLConnect);
	return true;


}