extended property for logical primary key (amo)

Question

hi.
I am practicing on AMO in a web site, through C#.I want to create a data source view with 2 tables and use one of them for case table but this table does not have primary key itself, but it has a column (name:ID) that is unique in a table.therefor i decide to set this column (ID) as logical primary key through AMO. I think I should using extended Property (by using ExtendedProperties.Add() method) for this column but I dont now the key object for logical primary key.
any help please...
thanks

Answer 1

Hi,

I have a book where it's explained how to use ExtendedProperties.
Here is the code of something i had. Maybe you can use it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {        
            Server serv = new Server();

            serv.Connect("localhost");
            if (serv.Connected)
            {
                Database dbs = serv.Databases.FindByName("DB_SSAS");
                if (dbs != null)
                {
                    dbs.Drop(DropOptions.AlterOrDeleteDependents);
                }
                dbs = serv.Databases.Add("DB_SSAS");
                dbs.Update();
                DataSource ds = createDatasource(dbs);
                DataSourceView dsv = createDatasourceView(dbs, ds);
                createDimension(dbs, dsv);
                createCube(dbs, dsv);
                dbs.Process();
            }
            serv.Disconnect();
        }

        static DataSource createDatasource(Database dbs)
        {
            // Übergebener Server + Datenbank eintragen
            DataSource ds = dbs.DataSources.Add("NewDs");
            ds.ConnectionString = "Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Database";
            ds.Update();
            return ds;
        }
        
        static DataSourceView createDatasourceView(Database dbs, DataSource ds)
        {

            DataSourceView dsv = dbs.DataSourceViews.Add("NewDSV");
            dsv.DataSourceID = ds.ID;
            dsv.Schema = new System.Data.DataSet();

                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ds.ConnectionString);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd;
                System.Data.OleDb.OleDbDataAdapter adapter;
                System.Data.DataTable[] tbls;
				System.Data.DataColumn col;

                cmd = new System.Data.OleDb.OleDbCommand("DimensionTable", conn);
                cmd.CommandType = System.Data.CommandType.TableDirect;
                adapter = new System.Data.OleDb.OleDbDataAdapter(cmd);
                tbls = adapter.FillSchema(dsv.Schema, System.Data.SchemaType.Mapped, "DimensionTable");
                col = tbls[0].Columns.Add("ColName", typeof(string));
                col.ExtendedProperties.Add("ComputedColumnExpression","ColName1 + ',' + ColName2");
                System.Data.DataColumn pk_Dim = tbls[0].Columns["DimensionKey"];

                cmd = new System.Data.OleDb.OleDbCommand("FactTable", conn);
                cmd.CommandType = System.Data.CommandType.TableDirect;
                adapter = new System.Data.OleDb.OleDbDataAdapter(cmd);
                tbls = adapter.FillSchema(dsv.Schema, System.Data.SchemaType.Mapped, "FactTable");
                System.Data.DataColumn fk_Dim = tbls[0].Columns["DimensionKey"];
                dsv.Schema.Relations.Add(pk_Dim, fk_Dim);

                conn.Close();
                dsv.Update();                

            return dsv;
        }

        static void createDimension(Database dbs, DataSourceView dsv)
        {

                // Shift
                Dimension dim = dbs.Dimensions.Add("Dimension");
                dim.Type = DimensionType.Customers;
                dim.Source = new DataSourceViewBinding(dsv.ID);
                dim.StorageMode = DimensionStorageMode.Molap;

                DimensionAttribute att;
                att = dim.Attributes.Add("DimensionAttribute");
                att.Usage = AttributeUsage.Key;
                att.KeyColumns.Add(new DataItem("DimensionTable", "DimensionKey"));
                att.NameColumn = new DataItem("DimensionTable", "ColName");

                dim.Update();
        }

        static void createCube(Database dbs, DataSourceView dsv)
        {

                Cube cub = dbs.Cubes.Add("MyCube");
                cub.Source = new DataSourceViewBinding("NewDSV");
                cub.StorageMode = StorageMode.Molap;

                CubeDimension cubDim = cub.Dimensions.Add("Dimension");


                MeasureGroup mg = cub.MeasureGroups.Add("MeasureGroup");
                mg.StorageMode = StorageMode.Molap;

                Measure me = mg.Measures.Add("Measure1");
                me.AggregateFunction = AggregationFunction.Sum;
                me.Source = new DataItem("FactTable", "Id", System.Data.OleDb.OleDbType.Integer);

                RegularMeasureGroupDimension mgDim = mg.Dimensions.Add(cubDim.ID);
                MeasureGroupAttribute mga = mgDim.Attributes.Add(cubDim.Dimension.Attributes.FindByName("DimensionAttribute").ID);
                mga.Type = MeasureGroupAttributeType.Granularity;
                mga.KeyColumns.Add(new DataItem("FactTable", "DimensionKey"));
                
                Partition pa = mg.Partitions.Add("P1");
                pa.StorageMode = StorageMode.Molap;
                pa.Source = new QueryBinding(cub.Parent.DataSources[0].ID, "Select * FROM dbo.FactTable");

                cub.Update(UpdateOptions.ExpandFull);
        }
	}
}

---

Sven