Hi.

I need to build a litle utility to check wheter all records from fact table
have been processed in the cube, and if not, i need to check each dimension
to find if any dimensionmembers is lacking.

Have anyone done this before?

We have a site wiht quite a lot of OLAP cubes, where we need to be able to
make an overview of wheter all records from fact tables have been processed.
At the moment, we know that due to som missing dimension members, not all
records have been processed.

I have startet to look a bit on DSO. At the moment, I have not been able to
find any informations of how many records have been processed. If I can not
find this information, I need to check that all dimension members in fact
table also exists in dimension tables.

What I will try to do, is to make a SQL Server table containing all cubes,
with its dimensions, and the name of fact table and dimension tables, and
the linkin fields from dimension table to fact table.

I have started with this vb-code, which list all cubes and dimensions, with
sourcetable. But in some cases, maybe because this cube is
Sub LNVTest()
Dim dsoServer As DSO.Server
Dim dsoDb As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dimDim As DSO.Dimension

' On Error GoTo HandleErr

Set dsoServer = New DSO.Server
dsoServer.Connect "localhost"
' lstInfo.Clear

For Each dsoDb In dsoServer.MDStores
Debug.Print "Database: " & dsoDb.Name & " (" & _
dsoDb.Description & ")"
' Iterate through the MDStores collection of the Database
' object, which contains Cubes
Debug.Print dsoDb.DataSources(1).ConnectionString
For Each dsoCube In dsoDb.MDStores
On Error Resume Next
Debug.Print vbTab & "Cube: " & dsoCube.Name & " (" & _
dsoCube.SourceTable & ")"
For Each dimDim In dsoCube.Dimensions
Debug.Print vbTab & vbTab & dimDim.Name & " - " &
dimDim.SourceTableAlias
Next
On Error GoTo 0
Next dsoCube
Next dsoDb

dsoServer.CloseServer
Set dsoServer = Nothing
End Sub

If I can retrieve the correct informations from a DSO-program, I will store
the informations in a SQL Serfer Table, and then make a Stored Procedure to
chek each dimension against fact table, to check wether all dimension
members in fact table exists in the dimension table.

Regards,
Lorents Nord-Varhaug

If you want to make sure that all the rows are processed, why don't you
switch on the option to fail the processing on dimension key errors?
Analysis Manager will show you which dimension keys are missing, and if you
process the cube via an DTS package, you can switch on package logging and
the information will be in there.

--
Jacco Schalkwijk
SQL Server MVP


<news (AT) danor (DOT) dk> wrote

Thanks Jacco.

I am aware of this possibillity, but we want to process the cubes even if
some member is missing. The cubes are processed at night, and the
processing should not be stoped.

But we need to make some kind of checking utility to run later, to see if
some members is missing.

Regards,
Lorents Nord-Varhaug

"Jacco Schalkwijk" <jacco.please.reply (AT) to (DOT) newsgroups.mvps.org.invalid> wrote
in message news:%23Zi0Koj0EHA.1308 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Would figure it out with the use of SQL in the staging area instead of in
AS....


<news (AT) danor (DOT) dk> wrote

select * where not exists...

<news (AT) danor (DOT) dk> wrote

What we are selecting is to ignore dimension key errors, and also to log
dimension key errors to a file. So cube processing will complete
overnight, but missing dimension keys can be identified from the cube
error log. Of course, this only works for dimensions with optimized
schema.


From SQL Server BOL>>
Cube Processing Settings Dialog Box
...
Ignore all missing dimension key errors

Processing continues despite dimension key errors.

Log dimension key errors to a file

Select to provide a path to a text file. Universal Naming Convention
(UNC) paths are recommended.

File path and name
Type the path into the File path and name dialog box.

Browse
Click to select a local or network location for the file.
- Deepak

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