DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.sql

DTS Fully process dimensions code

Using the following code to fully process dimension in a catalog on SQL Server 2000.

for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process processFull
Next

However this appears to do an incremental process of dimension rather than full process. (I would expect the cubes to be unprocessed after a full process.

Whats wrong with the code.
Cheers.
Mark.for each objDimension in dsoDatabase.Dimensions
dsoDimension = objDimension
dsoDimension.Process (1)
Next

Have tested this and believe it to work. Still do not understand why above did not work.|||I assume you are using vbscript to process. If so you need to reference to the Constant "processFull" so that it applies it correctly. Otherwise it always just does it as a process default.

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSO.Dimension.DataSource fails in jscript

I have a jscript that I am using to create 2 new dimensions (see below). The problem that I am encountering is "object doesn't support this action". This occurs for the following line: inventoryProductDimension.DataSource = dsoDataSource;

First is this because of jscript and secondly is there anyway around this.

Thanks

Ron

Code Snippet

var serverName = config.GetOlapServerName();

var databaseName = config.GetOlapDataBaseName();

var server = new ActiveXObject("DSO.Server");

if (server == null) throw "Could not create DSO.Server object.";

// Connect to OLAP server specified in web.config file

server.Connect(serverName);

var database = new ActiveXObject("DSO.Database");

if (database == null) throw "Could not create DSO.Database object.";

// Retrieve database object from server

database = server.MDStores(databaseName);

// Create new Dimension

var inventoryProductDimension;

//if (inventoryProductDimension == null) throw "Could not create DSO.Dimension for InventoryProduct dimension.";

// Get database's datasource

var dsoDataSource ;

dsoDataSource = database.DataSources("DataMart");

inventoryProductDimension = database.Dimensions.AddNew("InventoryProduct");

// Add inventoryproduct dimension

inventoryProductDimension.DataSource = dsoDataSource;

inventoryProductDimension.FromClause ="\"dbo\".\"InventoryProductCategory\",\"dbo\".\"InventoryProduct\"";

inventoryProductDimension.JoinClause ="(\"dbo\".\"InventoryProductCategory\".\"SurrogateKey\" = \"dbo\".\"InventoryProduct\".\"FK_InventoryProductCategory\")";

// Add InventoryProduct Levels

update: Through research, I have determined that it is not recommended that you use a scripting language to perform DSO functions because the object hierarchy is based on the MDStores interface such that if you wanted to iterate through the collection of databases on server, then you would need to iterate through MDStores rather databases. This works because each MDStore for the Server object is a database object. Bottom-line is that the language must be capable of create object types and this is not case with JScript. In my case, I opted to try another scripting language,VBScript. This seems to function properly; however the collections created using CreateObject may not be available in future versions of DSO.

Thanks

Ron Cotton

DSO.Dimension.DataSource fails in jscript

I have a jscript that I am using to create 2 new dimensions (see below). The problem that I am encountering is "object doesn't support this action". This occurs for the following line: inventoryProductDimension.DataSource = dsoDataSource;

First is this because of jscript and secondly is there anyway around this.

Thanks

Ron

Code Snippet

var serverName = config.GetOlapServerName();

var databaseName = config.GetOlapDataBaseName();

var server = new ActiveXObject("DSO.Server");

if (server == null) throw "Could not create DSO.Server object.";

// Connect to OLAP server specified in web.config file

server.Connect(serverName);

var database = new ActiveXObject("DSO.Database");

if (database == null) throw "Could not create DSO.Database object.";

// Retrieve database object from server

database = server.MDStores(databaseName);

// Create new Dimension

var inventoryProductDimension;

//if (inventoryProductDimension == null) throw "Could not create DSO.Dimension for InventoryProduct dimension.";

// Get database's datasource

var dsoDataSource ;

dsoDataSource = database.DataSources("DataMart");

inventoryProductDimension = database.Dimensions.AddNew("InventoryProduct");

// Add inventoryproduct dimension

inventoryProductDimension.DataSource = dsoDataSource;

inventoryProductDimension.FromClause ="\"dbo\".\"InventoryProductCategory\",\"dbo\".\"InventoryProduct\"";

inventoryProductDimension.JoinClause ="(\"dbo\".\"InventoryProductCategory\".\"SurrogateKey\" = \"dbo\".\"InventoryProduct\".\"FK_InventoryProductCategory\")";

// Add InventoryProduct Levels

update: Through research, I have determined that it is not recommended that you use a scripting language to perform DSO functions because the object hierarchy is based on the MDStores interface such that if you wanted to iterate through the collection of databases on server, then you would need to iterate through MDStores rather databases. This works because each MDStore for the Server object is a database object. Bottom-line is that the language must be capable of create object types and this is not case with JScript. In my case, I opted to try another scripting language,VBScript. This seems to function properly; however the collections created using CreateObject may not be available in future versions of DSO.

Thanks

Ron Cotton