AMO:Quesiton About the DataSourceView

Hi, friends, thank you very much to read my question as below:

I 'am using the AMO, and meet a question about the DataSourceView object.

My question is very simple:

After I added a column into a source table in the SQL Server, how to get the change into the DataSourceView object?

Background:

I have a cube in my OLAP Server, and I connect with it in my application with AMO. My task is to add a new measure, mapping to a new column I just added into the source table, just like this:

Dim meas As Microsoft.AnalysisServices.Measure

meas = Mainform.tDatabase.Cubes(0).MeasureGroups(0).Measures.Add()
meas.Source = CreateDataItem(Mainform.tDatabase.DataSourceViews(0), TableName, ColumnName)

But my problem is that the table schema in the DataSourceViews(0) doesn't include the column just added.

How could I update the DataSourceViews? I tried the Update() method, but that's is used to update the the OLAP server, not what I want to do. I want to update the DataSourceViews object itself refering to the SQL Server's table schema.

Thanks!

Have you added the column to your datatable that you use to populate your DSV ?
|||

sorry, I dont' catch your mean. I have added the column to the source table in the database of the sql server 2005. What is your mean of "MY Datatable use to populate my DSV"? Perhaps, you could teach me how to populate my DSV based on the datatable in the sql server 2005.

Thanks.

|||Hi,

Take a look at this If you scroll down to the part where it says "DataSourceView Objects", you will see that they are using SQL to populate a datatable to fill the datasourceview. This is very similar to what you will do, You've added the new column in the table in your database, now you've got to query that column, to add it to the datatable.

Hope that makes sense,

C

AMO: Use a query for datasourceview schema

Hi!

I want to use a query that returns all the necessary information to make a cube

Is there a way to provide this query to the datasourceview.schema property without adding any other tables?

So you basically have one big view that contains all your measures and all the attributes for every dimension?

I think this should be possible, the data source view is basically a .Net DataSet object, so you could just create a single table in your dataset with this view as the source.

But this is not really a recommended architecture. I would expect processing performance to suffer, SSAS would end up doing a lot of select distincts over the attribute columns. You would normally want to have all the tables and joins referenced in this view in your DSV.

|||

I know that there will be performance issues but all I have in my hands is a query and not even a view (actually many queries each for one cube)

In Analysis Services Designer you can provide just a named query but I do not know how to do this programmatically.

I suspect that after filling the datasourceview schema with an adapter, some extended properties will have to be added so as data to be fetched from the tables of the database to cube. Something like this:

Datasourceview.schema.Tables(0).ExtendedProperties.Add("TableType", "NamedQuery")

Datasourceview.schema.Tables(0).ExtendedProperties.Add("QueryDefinition", Query)

but it does not work!!! (cube is created but has no data)

Any idea?

|||I believe you want:

Datasourceview.schema.Tables(0).ExtendedProperties.Add("TableType", "View")

I don't know if that'll solve your problem, but that's the proper syntax for that statement AFAIK.

C
|||I think Chris is correct, the table type looks like it might have to be set to "View". The AMOAdventureWorks sample shows how to build a cut down version of the Adventure Works database using purely AMO (Although it does not contain an example of creating a named query) I'm not sure, but it's possible that you may also need to set:

Datasourceview.schema.tables(0).ExtendedProperties.Add("IsLogical", "True")

|||And, if i remember correctly, i had to remove the Schema name from the DT as well..

Datasourceview.schema.tables(0).ExtendedProperties.Remove("DBSchemaName")

C

AMO: Use a query for datasourceview schema

Hi!

I want to use a query that returns all the necessary information to make a cube

Is there a way to provide this query to the datasourceview.schema property without adding any other tables?

So you basically have one big view that contains all your measures and all the attributes for every dimension?

I think this should be possible, the data source view is basically a .Net DataSet object, so you could just create a single table in your dataset with this view as the source.

But this is not really a recommended architecture. I would expect processing performance to suffer, SSAS would end up doing a lot of select distincts over the attribute columns. You would normally want to have all the tables and joins referenced in this view in your DSV.

|||

I know that there will be performance issues but all I have in my hands is a query and not even a view (actually many queries each for one cube)

In Analysis Services Designer you can provide just a named query but I do not know how to do this programmatically.

I suspect that after filling the datasourceview schema with an adapter, some extended properties will have to be added so as data to be fetched from the tables of the database to cube. Something like this:

Datasourceview.schema.Tables(0).ExtendedProperties.Add("TableType", "NamedQuery")

Datasourceview.schema.Tables(0).ExtendedProperties.Add("QueryDefinition", Query)

but it does not work!!! (cube is created but has no data)

Any idea?

|||I believe you want:

Datasourceview.schema.Tables(0).ExtendedProperties.Add("TableType", "View")

I don't know if that'll solve your problem, but that's the proper syntax for that statement AFAIK.

C
|||I think Chris is correct, the table type looks like it might have to be set to "View". The AMOAdventureWorks sample shows how to build a cut down version of the Adventure Works database using purely AMO (Although it does not contain an example of creating a named query) I'm not sure, but it's possible that you may also need to set:

Datasourceview.schema.tables(0).ExtendedProperties.Add("IsLogical", "True")

|||And, if i remember correctly, i had to remove the Schema name from the DT as well..

Datasourceview.schema.tables(0).ExtendedProperties.Remove("DBSchemaName")

C

AMO vs. ADOMD

I use AMO to programmatically create a complete database with datasource, datasourceview, dimensions, and cubes. I also have a webservice that uses ADOMD.NET to retrieve metadata about those objects (for security purposes and also for .NET 1.1 applications). However, I notice that using ADOMD's GetSchemaDataSet only return certain metadata. For example, for a dimension, it doesn't return the DimensionID or other properties I am able to set with AMO. I want to use ADOMD.NET because it returns the metadata in XML format already. With AMO, I have to serialize the dimension object. Is there any other way to retrieve additional metadata for AMO objects?

You can use "DISCOVER_XML_METADATA" with GetSchemaDataSet (this is the discover type AMO is using to get all the metadata for objects).

<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList>
<DatabaseID>DataWarehouse Project1</DatabaseID>
<DataSourceViewID>Budget 1</DataSourceViewID>
<ObjectExpansion>ObjectProperties</ObjectExpansion> // can also be ExpandFull, ExpandObject or ReferenceOnly
</RestrictionList>
</Restrictions>

Adrian Dumitrascu

|||Thanks Adrian!! That's exactly what I am looking for. I assume I can just deserialize the xml to get the AMO object back. My only concern is that AMO objects are pretty powerful and it's possible for the client to modify the cube structure. I may create my own readonly wrapper classes for each AMO object or inherit the AMO objects and override the update/process methods.|||

> I assume I can just deserialize the xml to get the AMO object back.

Yes, there is the Utils class with Serialize and Deserialize methods for this.

> I may create my own readonly wrapper classes for each AMO object or inherit the AMO objects and override the update/process methods.

You will need to create wrapper classes because most of the AMO classes are sealed. But what kind of additions/changes you want for the Update and Process ? Maybe they have some flags/parameters that would help.

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

You will need to create wrapper classes because most of the AMO classes are sealed. But what kind of additions/changes you want for the Update and Process ? Maybe they have some flags/parameters that would help.

Here's my scenario. I am deploying a click-once application to the end-user that allows them to query the cube by choosing a measure and 2 dimensions. Then they can save their configurations back to the server so next time they can open the configuration and view/edit them. I have a webservice that the click-once app uses to retrieve metadata about the cube. I was going to have the click-once app reference Microsoft.AnalysisServices dll so I can easily deserialize the XML to actual AMO objects I can use. However, since the click-once shouldn't need a live connection to the Analysis Server or make any changes to it, I don't really want to use AMO objects at all. Kind of an overkill. I was going to just create some simple classes that will parse the XML for the properties I want. The bad part is that I will have to create a class for all the AMO objects I want to use...not hard at all but just tedious.