Testing Cubes in BI Projects using Excel

Recently, am involved in the BI projects testing activities. Basically, the main task is the data comparison between data in database(Source data) and the data out of our cubes (Target data).

 

Excel is chosen for the testing platform.

1. As to the 'Target Data', pivots in Excel could be used for the representation out of the cubes.

 

Data -- > From Other Sources  -- > Chosse the cube -- Finish.
Filter the pivot in Excel to get the desired data set.

2. As to the 'Source Data', there are two ways.

  2.1 Stored Procedure:

   The most advantage of using SP is flexible, since passing parameters is allowed, the interaction is realized.
   Following are the main steps:
    2.1.1. Create ODBC connection. (Name: 'test')
    Control panel --&gt
    Administrative Tools  --&gt
    Data Sources (ODBC)  --&gt
    User DSN  --&gt
    Add  --&gt
    SQL Server Native Client 10.0 --&gt
    Name, description ,Server  --&gt
    Finish

    2.1.2.Create the stored procedure in DB.
    Test  --&gt
    Programmability  --&gt
    Create Stored Procedure

    Test.[DBSchema].[StoredProcedure]

    2.1.3. Create and Edit the Macro in Excel
    View  --&gt
    Macros  --&gt

    Sub test()
    Range("A1:M500").ClearContents  //clear the contents since last loading of the data.
    Dim Parameter1 As String
    Parameter1 = InputBox("please input Parameter1 ")
    sqlstring = "exec [DBSchema].[StoredProcedure]  " & Parameter1 & " "
    connstring = "ODBC;DSN=test;UID=sa;PWD=sa;Database=test"
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
    .Refresh
    End With
    End Sub

 2.2 Views:  

    The current solution we are adopting is using Views, since not huge data load.
    Less steps for it:
    2.2.1 Create View in DB
    2.2.2 Connect view to represent data in Excel
      Data -- > From Other Sources  -- > Chosse the view -- Finish.

 

Last but not least, for the automation of the comparison, the rules in Excel is applied to. For the assigned cells, if they are eaqual to each other, the third cell results to "Pass", otherwise indicated as "Fail". Then apply to colors for each condition.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12374161/viewspace-667877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12374161/viewspace-667877/