Checking if all records are processed.

Tech-Archive recommends: Speed Up your PC by fixing your registry

news_at_danor.dk
Date: 11/24/04 

Date: Wed, 24 Nov 2004 15:54:21 +0100

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

Relevant Pages

  • Re: Checking if all records are processed.
    ... The cubes are processed at night, ... some members is missing. ... > switch on the option to fail the processing on dimension key errors? ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube and Dimension Processing
    ... We are using a dedicated server for SSAS. ... Refrsh/ProcessUpdate on dimension and with Changing Dimension property ... I guess you have enough power for process your cubes each hour. ... addition of new members but no changes in existing members ...
    (microsoft.public.sqlserver.olap)
  • Re: Request for some tips or better ways to handle some "issues"
    ... already constructed cubes, but am not quite clear on when the cubes get the ... I still can't browse the dimension ... shared dim, cubes that contain that dim go offline. ... I be using the dimension members' surrogate keys or something other than the ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube and Dimension Processing
    ... I guess you have enough power for process your cubes each hour. ... Will you use a dedicated SSAS server to perform this process or will ... dimension you will need to perform a Full process in you cubes. ... addition of new members but no changes in existing members ...
    (microsoft.public.sqlserver.olap)
  • Re: Delete member from dimension without reprocessing
    ... Doing a full process on any dimension will ... force the reprocessing of any partition that uses that dimension. ... > I have built a datawarehouse and am loading the data from the ... The cubes are partitioned by month. ...
    (microsoft.public.sqlserver.olap)