Transfer Data to the Synthesis Data Warehouse (SDW)

<< Back to Tutorials Main Page

The Synthesis Data Warehouse (SDW) — formerly called "Reliability Data Warehouse" — is a temporary data storage location in a Synthesis repository. With the Synthesis API, you can transfer data from external sources into the SDW of a repository for use in Weibull++, ALTA or RGA standard folios.

In this tutorial, you'll create a simple solution for moving data from a Microsoft Excel® file over to the SDW. Note that the tutorial only demonstrates one particular approach for extracting data from Excel. It's for demonstration purposes only; it doesn't take efficiency into account and doesn't include any exception handling.

Prerequisites

Before you begin:

• Reference the Microsoft Excel Object Library in your code. (In Visual Studio, choose Project > Add Reference, and in the COM tab, select the latest version of the Microsoft Excel Object Library. Click OK.)

• Create a Synthesis repository for testing purposes.

• You'll also need to create a sample Excel file to complete the tutorial successfully. Copy and paste the following data to an Excel file. Be sure to select cell A1 in Sheet1 of the workbook when pasting the data. Name the Excel file, "SampleData."

Failure State	Time to F or S	Failure Mode
F	7380	Cracked
F	4956	Cracked
F	5451	Cracked
F	6779	Failed
F	7713	Aging
F	1807	Bending
F	5940	Chafing
F	2071	Arcing
F	8178	Loose
F	3006	Burned
F	1385	Broken
F	2432	No Contact
F	1169	No Contact
F	592	Shorted
F	24	Burned
F	453	Burned
F	382	Burned
F	774	Burned
F	53	Burned

Tutorial: Transfer Data from an Excel File to the SDW

The following example demonstrates how to transfer data from an Excel file to the Synthesis Data Warehouse (SDW). A discussion of the example follows.

A VBA version of the code sample is available here.

VB.NET

Imports SynthesisAPI 
Imports Microsoft.Office.Interop.Excel

 Public Module Module1
     Sub Main()

      'Declare a new RawDataSet object. 
       Dim DataCollection As New RawDataSet
 
      'Name it "New Data Collection," and then specify that 
      'it is for use with Weibull++. 
       DataCollection.ExtractedName = "New Data Collection"
       DataCollection.ExtractedType = RawDataSetType.Weibull

      'Declare a RawData object. 
       Dim Row As RawData
 
      'Open the Excel file. The following code assumes that the 
      'Excel file is saved in the C drive. 
       Dim Excel As New Application
       Dim Workbook As Workbook
       Dim Sheet As Worksheet

       Workbook = Excel.Workbooks.Open("C:\SampleData.xlsx")
       Sheet = Workbook.Sheets(1)

      'Read each row of data in the Excel file. 
       Dim i As Integer, MaxRow As Integer
       MaxRow = 20
 
       For i = 2 To MaxRow
           Row = New RawData

           'Set the properties for the current data point. 
            Row.StateFS = sheet.Cells(i, 1).text
            Row.StateTime = sheet.Cells(i, 2).value
            Row.FailureMode = sheet.Cells(i, 3).text

          'Add the current data point to the data collection. 
           DataCollection.AddDataRow(Row)
       Next i

      'Close the Excel file. 
       Workbook.Close()
       Excel.Quit()

      'Connect to a Synthesis repository.   
       Dim MyRepository As New Repository   
       MyRepository.ConnectToRepository ("C:\RSRepository1.rsr10") 'Replace with name and path to test repository. 

      'Send the data collection to the repository. 
       MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)

     End Sub
End Module

Discussion

Begin by creating an SDW data collection. The RawDataSet class represents the data collection, and the type of data it will contain must be defined. For this example, we've set it to contain data sets for Weibull++.

 'Declare a new RawDataSet object. 
  Dim DataCollection As New RawDataSet
 
 'Name it "New Data Collection," and then specify that 
 'it is for use with Weibull++. 
  DataCollection.ExtractedName = "New Data Collection"
  DataCollection.ExtractedType = RawDataSetType.Weibull

Next, declare a RawData object, which represents a single data point. We will use this object to represent each row of data in the Excel file.

 'Declare a RawData object. 
  Dim Row As RawData

The following section of code demonstrates one way to extract a row of data from the Excel file. First, the code opens a connection to the Excel file and specifies the active sheet (in this case, Sheet1). Then For i = 2, it creates a new RawData object and uses the values in row #2 of the Excel file to define the data point.

After the code finishes reading the row of data, it adds the data point to the data collection. When the code reaches Next i, it increases i with 1 and jumps back to the For statement, which creates a new RawData object to represent the data point in row #3 of the Excel sheet, and so on. The loop continues until i reaches the specified number of rows (MaxRow). Finally, the code closes the connection to the Excel file.

 'Open the Excel file. The following code assumes that the 
 'Excel file is saved in the C drive. 
  Dim Excel As New Application
  Dim Workbook As Workbook
  Dim Sheet As Worksheet

  Workbook = Excel.Workbooks.Open("C:\SampleData.xlsx")
  Sheet = Workbook.Sheets(1)

 'Read each row of data in the Excel file. 
  Dim i As Integer, MaxRow As Integer
  MaxRow = 20
 
  For i = 2 To MaxRow
      Row = New RawData

     'Set the properties for the current data point. 
      Row.StateFS = sheet.Cells(i, 1).text
      Row.StateTime = sheet.Cells(i, 2).value
      Row.FailureMode = sheet.Cells(i, 3).text

     'Add the current data point to the data collection. 
      DataCollection.AddDataRow(Row)
  Next i

 'Close the Excel file. 
  Workbook.Close()
  Excel.Quit()

Now that we've filled the data collection with the data points, the next step is to send the data collection to the Synthesis repository.

First, connect to a Synthesis repository (for details, see this tutorial.)

 'Connect to a Synthesis repository. 
  Dim MyRepository As New Repository
  MyRepository.ConnectToRepository("C:\RSRepository1.rsr10") 'Replace with name and path to test repository. 

Then use the Repository.DataWarehouse.SaveRawDataSet method to save the data collection to the Synthesis repository, as shown below.

To verify that the data were imported, launch Weibull++, open the SDW (Home > Synthesis > Synthesis Data Warehouse) and then select the new data collection. (To view the data, you may need to display the StateTime, StateFS and FailureMode columns by right-clicking a column header and choosing Select Columns.)

 'Send the data collection to the repository. 
  MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)

Notes

In a secure database, you must have the "Create/edit/delete SDW data collections" permission in order to send a data collection to the database.

A data point (i.e., RawData object) for a Weibull++ data set typically requires the following properties:

• LastInspectedTime
• NumberinState
• StateFS
• StateTime

For an RGA data set, the properties are:

• LastInspectedTime
• StateFS
• StateTime
• TopLevelSerialNumber

References

• RawDataSet Class
• RawData Class
• RawDataSet.AddDataRow Method
• Repository.DataWarehouse.SaveRawDataSet Method