Passing a schema to a cube via AMO

I'm having a problem passing the Schema name to the cube when building it using AMO.
Oh, i'm using SQL Server 2k5.

When i build the cube through Dev Studio, it will process dimensions as expected. So, when pulling data, i can look at the SQL and see (for example):

"Select blah from Schema.TableName"

Which is what i want. However, when i build the cube using AMO i can not process the dimension, and it's failing because the SQL reads:

"Select blah from TableName"

A quick fix for me is to remove the Schema from my tables.. so, dbo.TableName works fine. But that's not exactly what i want. Or, i can create stored procs that pull the data from any table/schema, but that's not what i want either..

So, i guess i'm asking if there's a way to pass a schema to a dataset and have it read by the cube.

Any help would be appreciated..
Thanks,

Chris.

The following snippet is from the AMOAdventureWorks sample that is part of the SQL Server 2005 samples. It comes from the part of the program where they are building the DSV. Note that there are two points where the dbo schema is hard coded. I am hoping that by passing in your own schema name will resolve your issue.

staticvoid AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)

{

OleDbDataAdapter adapter = newOleDbDataAdapter(

"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",

connection);

DataTable[] dataTables = adapter.FillSchema(dsv.Schema,

SchemaType.Mapped, tableName);

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add("TableType", "Table");

dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");

dataTable.ExtendedProperties.Add("DbTableName", tableName);

dataTable.ExtendedProperties.Add("FriendlyName", tableName);

}

|||excellent, thanks so much!

dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); worked exactly as i needed it to, by replacing it with my own schema name.

Partitions not stored in the cube file

[Sorry - I'm throwing alot up here today - I know]

All partition info is stored in a <cubename>.partitions file.

I'm just wondering why this information is not stored in the <cubename>.cube file in the BIDS project. Especially considering that, if you view the code of <cubename>.cube there is an empty <Partitions /> tag for each Measure group.

Seems a bit strange to me. Any idea why this is?

-Jamie

The partitions are written in a separate file because it's common for projects to have many partitions and they can create a performance problem. The infrastructure for BI projects doesn't allow (or it allows with considerable effort not worth doing) loading a project file partially, on-demand as various elements are accessed in UI. Instead, the entire project file content is loaded into memory structures (AMO objects) that are displayed and edited in UI. By having the partitions into a separate file, we can open the cube editor for common task without loading all the partitions. And when the user goes to the Partitions tab (of cube editor), then partitions are loaded.

Adrian Dumitrascu

|||

Adrian,

Thanks, that makes sense. One other question then, why is there an empty <Partitions> element in the .cube file for each measure group?

Regards

Jamie

|||

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

Thanks again Adrian!

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one of the dimensions. I created a partition for each year and quarter, and set the slice appropriately. However, in order to create the partition, I had to use a source query, because I got an error about re-using the same table. I then created a single partition that was based off the table and set a slice and reviewed the SQL that SSAS issued, and it did not restrict based on the slice.
My question is: Can you make SSAS (2005) work like SQL 2000 where it automatically generated (albeit not perfectly) a where clause to restrict the partition?
Thanks,
Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one

of the dimensions. I created a partition for each year and

quarter, and set the slice appropriately. However, in order to

create the partition, I had to use a source query, because I got an

error about re-using the same table. I then created a single

partition that was based off the table and set a slice and reviewed the

SQL that SSAS issued, and it did not restrict based on the slice.

My question is: Can you make SSAS (2005) work like SQL 2000 where

it automatically generated (albeit not perfectly) a where clause to

restrict the partition?

Thanks,

Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan