Hi,

What is correlation name and how can I use it? I get this error when trying
to process the cube for the first time using DSO.

An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in AutomatedCubeCreation.exe

Additional information: Tables or functions 'ScanGeographyProductTime' and
'ScanGeographyProductTime' have the same exposed names. Use correlation
names to distinguish them.;42000

--
Thanks,
Pushpendu

From SQL Server 2000 BOL>>
Using Table Aliases

The readability of a SELECT statement can be improved by giving a table
an alias, also known as a correlation name or range variable.
...
The specific error message suggests that "ScanGeographyProductTime" has
multiple instances within the same (query?) context, and that these
instances should be explicitly identified using aliases. A table being
joined to itself would be such a scenario.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Now that I understand what is correlation name, how do I make use of it to
fix the error that I am getting.

To get a little background of this error, I am using DSO through Interop in
VB.NET to automate the creation and processing of cube in MS Analysis Server
2000. I get this error when code to process this cube runs. When I process
the same cube - created using DSO in Analysis Manager, the cube gets
processed fine. However, when I do that using code, I get this error.

As for "ScanGeographyProductTime" having multiple instance in the query that
should not be the case as that table is the fact table. Besides this fact
table I have added one dimension to the cube which uses a different table.

Maybe, if I can change the correlation name in MS Analysis Server 2000, that
will take care of the problem. I am not sure how to do that though. Any help
in that direction will be very helpful

Thanks,
Pushpendu
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Pushpendu,

Don't know exactly why you are getting such an error; but maybe if you
describe details of the DSO code you are using, someone in this group
may spot an issue in it?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,

The DSO is shown below. I have highlighted the line where I am getting the error. If you see it as plain text, then the line that is giving error is "dsoCube.Process(DSO.ProcessTypes.processFull) ":

Dim strCubeName As String = txtCubeName.Text

' If the database exists, create a reference to it.

If dsoServer.MDStores.Find(strDbName) Then

obOlapColl = dsoServer.MDStores

dsoDb = obOlapColl.Item(strDbName)

Else

MsgBox("Database " & strDbName & " not found.")

End If

' Check to see if the cube already exists. If not, create a reference

' to it.

If dsoDb.MDStores.Find(strCubeName) Then

dsoDb.MDStores.Remove(strCubeName)

dsoDb.Update()

End If

' MsgBox("Cube " & strCubeName & " already exists for the " & _

' dsoDb.Name & " database.")

'Else

dsoCube = dsoDb.MDStores.AddNew(strCubeName)

dsoCube.Update()

dsoCube.DataSources.Add(dsoDb.DataSources.Item(1))

dsoCube.Update()

dsoCube.SourceTable = "dbo.ScanGeographyProductTime"

'dsoCube.SourceTableAlias = "FactTable"

dsoCube.EstimatedRows = 1000

dsoCube.JoinClause = "dbo.ScanGeographyProductTime.GeoNodeID=dbo.vw_Geo Level_District_Store.ChildGeoNodeID"



Dim dsoMeasure As DSO.Measure

dsoMeasure = dsoCube.Measures.AddNew("Units")

dsoMeasure.AggregateFunction = DSO.AggregatesTypes.aggSum

dsoMeasure.SourceColumn = "Units"

dsoCube.Update()

Dim dsoDimension As DSO.Dimension

'dsoCube.Dimensions.Remove("Event^Store")

dsoDimension = dsoDb.Dimensions.AddNew("Event^Store")

dsoDimension.DataSource = dsoDb.DataSources.Item(1)

dsoDimension.DimensionType = DSO.DimensionTypes.dimRegular

'dsoDimension.FromClause = "dbo.Stores"

'dsoDimension.JoinClause = "dbo.ScanGeographyProductTime.GeoNodeID=dbo.Stores .StoreID"

dsoDimension.StorageMode = DSO.StorageModeValues.storeasMOLAP

Dim dsoLevel As DSO.Level

'dsoLevel = dsoDimension.Levels.AddNew("All")

'dsoLevel.LevelType = DSO.LevelTypes.levAll

'dsoLevel.MemberKeyColumn = "Stores"

dsoLevel = dsoDimension.Levels.AddNew("Store ID")

dsoLevel.LevelType = DSO.LevelTypes.levRegular

'dsoLevel.

dsoLevel.MemberKeyColumn = "dbo.vw_GeoLevel_District_Store.ChildGeoNodeID "

' dsoLevel.ColumnType = DSo.DimensionTypes.

dsoLevel.EstimatedSize = 5

dsoDimension.Update()

dsoDb.Update()

dsoCube.Dimensions.AddNew("Event^Store")

'dsoDimension.DataSource = dsoCube.DataSources.Item(1)

'dsoDimension.FromClause = "'Stores'"

'Dim a As DSO.Level = dsoDimension.Levels.AddNew("All")

'a.MemberNameColumn = "'StoreID'"

'dsoDimension.JoinClause = "Regions"

'dsoDimension.Update()

dsoCube.Update()

dsoCube.OlapMode = DSO.OlapStorageModes.olapmodeMolapIndex

dsoCube.ProcessOptimizationMode = DSO.ProcessOptimizationModes.processOptimizationMo deRegular

dsoCube.Update()

dsoServer.Refresh()

dsoDb = dsoServer.MDStores.Item(strDbName)

dsoCube = dsoDb.MDStores.Item(strCubeName)

dsoCube.Process(DSO.ProcessTypes.processFull)


MsgBox("Cube " & dsoCube.Name & " was created for the " & _

dsoDb.Name & " database.")


--
Thanks,
Pushpendu
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote