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