CreateStarSchema

Creates or updates a star schema by exporting data for all cells that intersect the specified dimension members. CreateStarSchema launches an asynchronous thread.

CreateStarSchema provides arguments to specify the dimension members for the extraction. You can specify members using any of the following techniques:

Member IDs. You can specify either one member or an array of members.
Member labels. You can specify either one member or an array of members.
Member lists. Enclose member list names in braces ( { } ).
The ALL keyword to specify all members of the dimension.
Note:
You can mix these techniques in a call to CreateStarSchema. For example, you can specify both member IDs and labels within an array for a given dimension.

To export data with CreateStarSchema, the connected user must be assigned to the Application Administrator role. To check whether the user is assigned to this role, use the HsvSecurityAccess method IsApplicationAdministrator.

Syntax

<HsvStarSchemaACM>.CreateStarSchema bstrDSN, bstrTablePrefix, ssPushType, eaExtractType, vbExcludeDynamicAccts, varaScenarios, varaYears, varaPeriods, varaViews, varaEntities, varaParents, varaValues, varaAccounts, varaICPs, varaCustom1, varaCustom2, varaCustom3, varaCustom4

Argument	Description
bstrDSN	String (ByVal). The Extended Analytics Data Source Name that points to the database for the star schema. Tip: To get the Extended Analytics Data Source Names that have been registered on an application server, use EnumRegisteredDSNs.
bstrTablePrefix	String (ByVal). The Relational Table Prefix, which is the prefix that identifies the star schema’s tables. If a star schema identified by this prefix does not exist, CreateStarSchema creates a new set of tables. If the star schema exists, the tables are updated; the ssPushType argument determines whether all data is deleted from the <PREFIX>_FACT table.
ssPushType	SS_PUSH_OPTIONS (ByVal). A flag that specifies whether to create a new star schema or update an existing star schema. Valid values are represented by the following constants (from Enum SS_PUSH_OPTIONS): ssCREATE - Create a star schema ssUPDATE - Update a star schema
eaExtractType	= 7, = 7 } EA_EXTRACT_TYPE_FLAGS; EA_EXTRACT_TYPE_FLAGS (ByVal). A flag that specifies the aggregation to use. Valid values are represented by the following constants (from Enum EA_EXTRACT_TYPE_FLAGS): EA_EXTRACT_TYPE_MIN EA_EXTRACT_TYPE_STANDARD - Standard EA_EXTRACT_TYPE_METADATA_ALL - Metadata Only EA_EXTRACT_TYPE_METADATA_SELECTED - Selected Metadata Only EA_EXTRACT_TYPE_SQL_AGG - For internal use. EA_EXTRACT_TYPE_ESSBASE - Oracle Essbase EA_EXTRACT_TYPE_WAREHOUSE - Data Warehouse EA_EXTRACT_TYPE_FLATFILE EA_EXTRACT_TYPE_MAX
vbExcludeDynamicAccts	Boolean (ByVal). A flag that specifies whether to exclude dynamic accounts. Pass TRUE to exclude these accounts, FALSE to exclude them.
varaScenarios	Variant (ByVal). The Scenario dimension members for which to extract.
varaYears	Variant (ByVal). The Year dimension members for which to extract.
varaPeriods	Variant (ByVal). The Period dimension members for which to extract.
varaViews	Variant (ByVal). The View dimension members for which to extract.
varaEntities	Variant (ByVal). The Entity dimension members for which to extract. For each specified member, you must specify a corresponding parent member in the varaParents argument.
varaParents	Variant (ByVal). The parents of the varaEntities argument’s entities.
varaValues	Variant (ByVal). The Value dimension members for which to extract.
varaAccounts	Variant (ByVal). The Account dimension members for which to extract.
varaICPs	Variant (ByVal). The Intercompany Partner dimension members for which to extract.
varaCustom1	Variant (ByVal). The Custom 1 dimension members for which to extract.
varaCustom2	Variant (ByVal). The Custom 2 dimension members for which to extract.
varaCustom3	Variant (ByVal). The Custom 3 dimension members for which to extract.
varaCustom4	Variant (ByVal). The Custom 4 dimension members for which to extract.

Example

The following subroutine uses the dimension members selected on an HsvPOVSelection control to export data to a Standard star schema. HsvPOVSelection.GetCheckedItems is used to build an array of selected members and a second array of parent Entity members. These arrays are passed to CreateStarSchema along with the DSN and Relational Table Prefix.

Sub ExtractStarSchema(sDSN As String, sPrefix As String)
Dim i As Long, vaMems(12), vaPars, vTemp
' for each of the 12 dimensions...
For i = 0 To 11
    'For the entity dimension, get the parents
    If i = DIMENSIONENTITY Then
       'cFormPOV represents an initialized HsvPOVSelection control
        cFormPOV.GetCheckedItems i, vaMems(i), vaPars
    Else    ' else, ignore parents
        cFormPOV.GetCheckedItems i, vaMems(i), vTemp
    End If
Next
' g_cStarSchema is a previously set HsvStarSchemaACM instance
g_cStarSchema.CreateStarSchema sDSN, sPrefix, _
    ssCREATE, EA_EXTRACT_TYPE_STANDARD, True, _
    vaMems(DIMENSIONSCENARIO), vaMems(DIMENSIONYEAR), _
    vaMems(DIMENSIONPERIOD), vaMems(DIMENSIONVIEW), _
    vaMems(DIMENSIONENTITY), vaPars, vaMems(DIMENSIONVALUE), _
    vaMems(DIMENSIONACCOUNT), vaMems(DIMENSIONICP), _
    vaMems(DIMENSIONCUSTOM1), vaMems(DIMENSIONCUSTOM2), _
    vaMems(DIMENSIONCUSTOM3), vaMems(DIMENSIONCUSTOM4)
End Sub