Sometimes it is very useful to be able to create and train a temporary mining model for use in the context of a single connection. For instance, you might have a data exploration application that creates a temporary Naïve-Bayes model for the current user based on the subset of server data they have selected. Users running such applications may not have the administrative permissions necessary to create new database objects on the Analysis Server 2005 instance they are connecting to but they can still work with temporary models using the session mining model feature.

In order to support this usage, the Analysis Server administrator must first enable this feature, which is off by default. To do this:

You can now create a session mining model using the following statement:

CREATE SESSION MINING MODEL [SessionPeople]

(

      [Age] LONG DISCRETE,

      [CarColor] TEXT DISCRETE,

      [CarModel] TEXT DISCRETE,

      [Gender] TEXT DISCRETE PREDICT,

      [HairColor] TEXT DISCRETE,

      [Key] LONG KEY

) USING Microsoft_Decision_Trees

This model can be processed with an INSERT-INTO statement like a regular model. For training data, the client user can use any existing data sources that they have access to on the server. Alternatively, OPENROWSET can be used if Adhoc OpenRowsets are allowed on the server (they are disabled by default for security reasons).

You can also create session mining structures and models as well as add models to previously created session mining structures using the Analysis Services XML DDL by including the Scope="Session" attribute to the object creation and modification XML as follows:

 <Create Scope="Session" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <ParentObject>

    <DatabaseID>DB1DatabaseID>

    <MiningStructureID>SessionPeopleStructureMiningStructureID>

  ParentObject>

  <ObjectDefinition>

    <MiningModel>

      <ID>SessionMSPeopleSessionMM2ID>

      <Name>SessionMSPeoplSessionMM2Name>

      <Algorithm>Microsoft_Decision_TreesAlgorithm>

      <AllowDrillThrough>trueAllowDrillThrough>

      <Columns>

        <Column>

          <ID>AgeID>

          <Name>AgeName>

          <SourceColumnID>AgeSourceColumnID>

        Column>

        <Column>

          <ID>CarColorID>

          <Name>CarColorName>

          <SourceColumnID>CarColorSourceColumnID>

        Column>

        <Column>

          <ID>CarModelID>

          <Name>CarModelName>

          <SourceColumnID>CarModelSourceColumnID>

          <Usage>PredictUsage>

        Column>

        <Column>

          <ID>GenderID>

          <Name>GenderName>

          <SourceColumnID>GenderSourceColumnID>

        Column>

        <Column>

          <ID>KeyID>

          <Name>KeyName>

          <SourceColumnID>KeySourceColumnID>

          <Usage>KeyUsage>

        Column>

      Columns>

    MiningModel>

  ObjectDefinition>

Create>

Such structures and models can be processed using the same XML Process statement supported for regular mining objects.

Session mining structures and models are visible only to the creating connection and they go away automatically when the client disconnects.

Session mining models with OLAP data

Another useful scenario is to create a temporary data mining dimension on the fly to slice cube data – for instance, you might want to cluster your customers based on demographic and sales data and then slice your sales facts by the discovered clusters. You can do this using the following six steps.

Step 1:

Either create and process a session mining structure and model using the XML DDL as shown above or use an existing model with bindings to the source OLAP cube. The source model for the temporary DM dimension does not have to be a session mining model.

Step 2:

 Create a session DSV for processing the DM dimension using the XML DDL statement below (replace “[Customer Clustering]” in the QueryDefinition with your model name):

<Create Scope="Session" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                              xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <ParentObject>

      <DatabaseID>DB1DatabaseID>

    ParentObject>

    <ObjectDefinition>

          <DataSourceView>

            <ID>Session_DMDSVID>

            <Name>Session_DMDSVName>

            <DataSourceID>.DataSourceID>

            <Schema>

              <xs:schema xmlns="" id="DIMENSION_CONTENT_SCHEMA" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">

                <xs:element name="DIMENSION_CONTENT_SCHEMA" msdata:IsDataSet="true" msprop:design-time-name="dataSet2">

                  <xs:complexType>

                    <xs:choice maxOccurs="unbounded">

                      <element auto-ns2:DbTableName="DIMENSION_CONTENT_ROWSET" name="DIMENSION_CONTENT_ROWSET" msprop:QueryDefinition="SELECT * FROM [Customer Clustering].DIMENSION_CONTENT" msprop:design-time-name="dataTable1" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:auto-ns2="urn:schemas-microsoft-com:xml-msprop">

                        <xs:complexType xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

                          <xs:sequence>

                            <element auto-ns2:DbColumnName="ATTRIBUTE_NAME" name="ATTRIBUTE_NAME" msprop:design-time-name="dataColumn1" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_NAME" name="NODE_NAME" msprop:design-time-name="dataColumn2" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_TYPE" name="NODE_TYPE" msprop:design-time-name="dataColumn3" type="xs:int" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_GUID" name="NODE_GUID" msprop:design-time-name="dataColumn4" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_CAPTION" name="NODE_CAPTION" msprop:design-time-name="dataColumn5" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="CHILDREN_CARDINALITY" name="CHILDREN_CARDINALITY" msprop:design-time-name="dataColumn6" type="xs:unsignedInt" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="PARENT_UNIQUE_NAME" name="PARENT_UNIQUE_NAME" msprop:design-time-name="dataColumn7" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_DESCRIPTION" name="NODE_DESCRIPTION" msprop:design-time-name="dataColumn8" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_RULE" name="NODE_RULE" msprop:design-time-name="dataColumn9" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="MARGINAL_RULE" name="MARGINAL_RULE" msprop:design-time-name="dataColumn10" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_PROBABILITY" name="NODE_PROBABILITY" msprop:design-time-name="dataColumn11" type="xs:double" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="MARGINAL_PROBABILITY" name="MARGINAL_PROBABILITY" msprop:design-time-name="dataColumn12" type="xs:double" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_SUPPORT" name="NODE_SUPPORT" msprop:design-time-name="dataColumn13" type="xs:double" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="MSOLAP_MODEL_COLUMN" name="MSOLAP_MODEL_COLUMN" msprop:design-time-name="dataColumn14" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="MSOLAP_NODE_SCORE" name="MSOLAP_NODE_SCORE" msprop:design-time-name="dataColumn15" type="xs:double" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="MSOLAP_NODE_SHORT_CAPTION" name="MSOLAP_NODE_SHORT_CAPTION" msprop:design-time-name="dataColumn16" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                            <element auto-ns2:DbColumnName="NODE_UNIQUE_NAME" name="NODE_UNIQUE_NAME" msprop:design-time-name="dataColumn17" type="xs:string" minOccurs="0" xmlns="http://www.w3.org/2001/XMLSchema">element>

                          xs:sequence>

                        xs:complexType>element>

                    xs:choice>

                  xs:complexType>

                xs:element>

              xs:schema>

              <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">diffgr:diffgram>

            Schema>

          DataSourceView>

    ObjectDefinition>

Create>

Step 3:

Create a session data mining dimension using the XML DDL statement below (replace “[Customer Clustering]” in the MiningModelID element with your model name):

<Create Scope="Session" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                              xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <ParentObject>

      <DatabaseID>DB1DatabaseID>

    ParentObject>

    <ObjectDefinition>

      <Dimension>

            <ID>Session_DMDimID>

            <Name>Session_DMDimName>

            <Source xsi:type="DataSourceViewBinding">

              <DataSourceViewID>Session_DMDSVDataSourceViewID>

            Source>

            <MiningModelID>Customer ClusteringMiningModelID>

            <Attributes>

              <Attribute>

                <ID>ATTRIBUTE_NAMEID>

                <Name>ATTRIBUTE_NAMEName>

                <KeyColumns>

                  <KeyColumn>

                    <DataType>WCharDataType>

                    <DataSize>255DataSize>

                    <Source xsi:type="ColumnBinding">

                      <TableID>DIMENSION_CONTENT_ROWSETTableID>

                      <ColumnID>ATTRIBUTE_NAMEColumnID>

                    Source>

                  KeyColumn>

                KeyColumns>

                <NameColumn>

                  <DataType>WCharDataType>

                  <Source xsi:type="ColumnBinding">

                    <TableID>DIMENSION_CONTENT_ROWSETTableID>

                    <ColumnID>ATTRIBUTE_NAMEColumnID>

                  Source>

                NameColumn>

              Attribute>

              <Attribute>

                <ID>PARENT_UNIQUE_NAMEID>

                <Name>MiningDimensionContentNodesName>

                <Usage>ParentUsage>

                <KeyColumns>

                  <KeyColumn>

                    <DataType>WCharDataType>

                    <DataSize>255DataSize>

                    <Source xsi:type="ColumnBinding">

                      <TableID>DIMENSION_CONTENT_ROWSETTableID>

                      <ColumnID>PARENT_UNIQUE_NAMEColumnID>

                    Source>

                  KeyColumn>

                KeyColumns>

                <NameColumn>

                  <DataType>WCharDataType>

                  <Source xsi:type="ColumnBinding">

                    <TableID>DIMENSION_CONTENT_ROWSETTableID>

                    <ColumnID>PARENT_UNIQUE_NAMEColumnID>

                  Source>

                NameColumn>

                <MembersWithData>NonLeafDataHiddenMembersWithData>

              Attribute>

              <Attribute>

                <ID>NODE_RULEID>

                <Name>NODE_RULEName>

                <KeyColumns>

                  <KeyColumn>

                    <DataType>WCharDataType>

                    <DataSize>255DataSize>

                    <Source xsi:type="ColumnBinding">

                      <TableID>DIMENSION_CONTENT_ROWSETTableID>

                      <ColumnID>NODE_RULEColumnID>

                    Source>

                  KeyColumn>

                KeyColumns>

                <NameColumn>

                  <DataType>WCharDataType>

                  <Source xsi:type="ColumnBinding">

                    <TableID>DIMENSION_CONTENT_ROWSETTableID>

                    <ColumnID>NODE_RULEColumnID>

                  Source>

                NameColumn>

              Attribute>

              <Attribute>

                <ID>NODE_UNIQUE_NAMEID>

                <Name>NODE_UNIQUE_NAMEName>

                <Usage>KeyUsage>

                <KeyColumns>

                  <KeyColumn>

                    <DataType>WCharDataType>

                    <DataSize>255DataSize>

                    <Source xsi:type="ColumnBinding">

                      <TableID>DIMENSION_CONTENT_ROWSETTableID>

                      <ColumnID>NODE_UNIQUE_NAMEColumnID>

                    Source>

                  KeyColumn>

                KeyColumns>

                <NameColumn>

                  <DataType>WCharDataType>

                  <Source xsi:type="ColumnBinding">

                    <TableID>DIMENSION_CONTENT_ROWSETTableID>

                    <ColumnID>NODE_UNIQUE_NAMEColumnID>

                  Source>

                NameColumn>

                <Translations>

                  <Translation>

                    <Language>1033Language>

                    <CaptionColumn>

                      <DataType>WCharDataType>

                      <Source xsi:type="ColumnBinding">

                        <TableID>DIMENSION_CONTENT_ROWSETTableID>

                        <ColumnID>NODE_CAPTIONColumnID>

                      Source>

                    CaptionColumn>

                  Translation>

                Translations>

                <AttributeRelationships>

                  <AttributeRelationship>

                    <AttributeID>PARENT_UNIQUE_NAMEAttributeID>

                    <RelationshipType>FlexibleRelationshipType>

                  AttributeRelationship>

                  <AttributeRelationship>

                    <AttributeID>ATTRIBUTE_NAMEAttributeID>

                  AttributeRelationship>

                  <AttributeRelationship>

                    <AttributeID>NODE_RULEAttributeID>

                  AttributeRelationship>

                AttributeRelationships>

              Attribute>

            Attributes>

          Dimension>

    ObjectDefinition>

Create>

Step 4:

Create a session cube that has a measuregroup that’s linked to the measuregroup in your source cube that contains the facts you’re interested in analyzing. This linked measuregroup should include the DM dimension created above as well as the case dimension used in the DM dimension’s source mining model. The XML DDL statement should look like the sample below (the sections related to the DM dimensions are bolded):

<Create Scope="Session" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                              xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <ParentObject>

      <DatabaseID>DB1DatabaseID>

    ParentObject>

    <ObjectDefinition>

          <Cube>

            <ID>Session_DMDimCubeID>

            <Name>Session_DMDimCubeName>

            <Dimensions>

              <Dimension>

                <ID>OLAPMM CustomerID>

                <Name>CustomerName>

                <DimensionID>OLAPMM CustomerDimensionID>

                <Attributes>

                  <Attribute>

                    <AttributeID>CustomerAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>CityAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>StateAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>AgeAttributeID>

                  Attribute>

                Attributes>

                <Hierarchies>

                  <Hierarchy>

                    <HierarchyID>HierarchyHierarchyID>

                  Hierarchy>

                Hierarchies>

              Dimension>

              <Dimension>

                <ID>OLAPMM ProductsID>

                <Name>ProductsName>

                <DimensionID>OLAPMM ProductsDimensionID>

                <Attributes>

                  <Attribute>

                    <AttributeID>Prod IDAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>Brand NameAttributeID>

                  Attribute>

                Attributes>

                <Hierarchies>

                  <Hierarchy>

                    <HierarchyID>HierarchyHierarchyID>

                  Hierarchy>

                Hierarchies>

              Dimension>

              <Dimension>

                <ID>Session_DMDimID>

                <Name>Session_DMDimName>

                <DimensionID>Session_DMDimDimensionID>

                <Attributes>

                  <Attribute>

                    <AttributeID>ATTRIBUTE_NAMEAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>PARENT_UNIQUE_NAMEAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>NODE_RULEAttributeID>

                  Attribute>

                  <Attribute>

                    <AttributeID>NODE_UNIQUE_NAMEAttributeID>

                  Attribute>

                Attributes>

              Dimension>

            Dimensions>

            <MeasureGroups>

              <MeasureGroup>

                <ID>OLAPMM PurchasesID>

                <Name>PurchasesName>

                <Source>

                  <DataSourceID>.DataSourceID>

                  <CubeID>DM TestCubeID>

                  <MeasureGroupID>OLAPMM PurchasesMeasureGroupID>

                  <Persistence>MetadataPersistence>

                Source>

                <Measures>

                  <Measure>

                    <ID>CostID>

                    <Name>CostName>

                    <Source>

                      <DataType>DoubleDataType>

                      <Source xsi:type="ColumnBinding">

                        <TableID>dbo_OLAPMMPurchasesTableID>

                        <ColumnID>CostColumnID>

                      Source>

                    Source>

                  Measure>

                Measures>

                <StorageMode>MolapStorageMode>

                <ProcessingMode>RegularProcessingMode>

                <Dimensions>

                  <Dimension xsi:type="DataMiningMeasureGroupDimension">

                    <CubeDimensionID>Session_DMDimCubeDimensionID>

                    <CaseCubeDimensionID>OLAPMM CustomerCaseCubeDimensionID>

                  Dimension>

                  <Dimension xsi:type="RegularMeasureGroupDimension">

                    <CubeDimensionID>OLAPMM CustomerCubeDimensionID>

                    <Attributes>

                      <Attribute>

                        <AttributeID>CustomerAttributeID>

                        <Type>GranularityType>

                        <KeyColumns>

                          <KeyColumn>

                            <DataType>IntegerDataType>

                            <DataSize>-1DataSize>

                            <Source xsi:type="ColumnBinding">

                              <TableID>dbo_OLAPMMPurchasesTableID>

                              <ColumnID>CustIDColumnID>

                            Source>

                          KeyColumn>

                        KeyColumns>

                      Attribute>

                    Attributes>

                  Dimension>

                  <Dimension xsi:type="RegularMeasureGroupDimension">

                    <CubeDimensionID>OLAPMM ProductsCubeDimensionID>

                    <Attributes>

                      <Attribute>

                        <AttributeID>Prod IDAttributeID>

                        <Type>GranularityType>

                      Attribute>

                    Attributes>

                  Dimension>

                Dimensions>

              MeasureGroup>

            MeasureGroups>

          Cube>

    ObjectDefinition>

Create>

Step 5:

Process the cube using the DDL statement below:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    <Type>ProcessFullType>

    <Object>

      <DatabaseID>DB1DatabaseID>

      <CubeID>Session_DMDimCubeCubeID>

    Object>

Process>

Step 6:

You can now use the newly created session cube to slice your fact data by the clusters discovered by your (existing or new ) mining model. Here’s a sample query:

SELECT

   [Session_DMDim].[MiningDimensionContentNodes].MEMBERS ON 0,

   [Measures].MEMBERS ON 1

FROM [Session_DMDimCube]