Transfer Data to the Synthesis Data Warehouse (SDW): Difference between revisions

From ReliaWiki
Jump to navigation Jump to search
No edit summary
No edit summary
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Template:API}}
{{Template:API}}{{Template:BacktoPrevPage}}
<div style="border:1px solid #D0CDE8; background:#EEEDF7; border-radius:10px; width:auto; margin:0 0 1em 1em; padding:1em; float:right;">
'''[[API_Tutorials|<< Back to API Tutorials]]'''


'''Transfer Data to the SDW'''
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.  
*[[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)#VBA Tutorial: Transfer Data from an Excel File to the SDW|VBA Tutorial]]
*[[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)#VB.NET Tutorial: Transfer Data from an Excel File to the SDW|VB.NET Tutorial]]
*[[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)#References|References]]
</div>


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 copying data from a Microsoft Excel&#174; 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'''.)


In the following tutorial, you'll create a simple solution for moving data from an Excel file over to the SDW. The tutorial is available for both VBA and VB.NET. Note that the tutorial 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. To complete the tutorial successfully, please use the sample Excel data given below.  
*Create a Synthesis repository for testing purposes.


===Create an Excel file for this tutorial===
*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."
Before you begin the tutorial, please create an Excel file to use as your sample data.


Copy and paste the following data to the Excel file. Be sure to select cell A1 in Sheet1 of the workbook when pasting the data.
Save the Excel file as "SampleData."
{| {{table}}
{| {{table}}
| Failure State||Time to F or S||Failure Mode
| Failure State||Time to F or S||Failure Mode
Line 62: Line 58:




== VBA Tutorial: Transfer Data from an Excel File to the SDW==
==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.


Before you begin, be sure to reference the Microsoft Excel Object Library. Choose '''Tools > Reference''', and then select the library from the list. Click '''OK'''.
A VBA version of the code sample is available [[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)/VBA|here]].  


1. Create a new module and start with the following code to create an SDW data collection. The code uses the <code>RawDataSet</code> object to represent the SDW data collection, and the <code>ExtractedType</code> property to specify that the data collection is for use with Weibull++.
  '''VB.NET'''
 
  '''VBA'''
   
   
  {{APIComment|'Declare a new instance of the RawDataSet class.}}
  {{APIPrefix|Imports}} SynthesisAPI
  {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
{{APIPrefix|Imports}} Microsoft.Office.Interop.Excel
   
   
{{APIComment|'Name the data collection "New Data Collection," and then specify that}}
  {{APIPrefix|Public Module}} Module1
{{APIComment|'it is for use with Weibull++.}}
      {{APIPrefix|Sub}} Main()
  DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
  DataCollection.ExtractedType = RawDataSetType_Weibull
 
2. Use the <code>RawData</code> object to represent a single row of data, and then use the <code>AddDataRow</code> method to add the row to the data collection.
 
The following code demonstrates one way to extract a row of data from the Excel sheet. For <code>i = 2</code>, the code creates a new <code>RawData</code> object, and then sets its properties by using the values from row #2 of the Excel file. After the code finishes reading the row of data, it adds the object to the data collection. When the code reaches <code>Next i</code>, it increases <code>i</code> with 1 and jumps back to the <code>For</code> statement, which adds another new <code>RawData</code> object for the data in row #3 of the Excel sheet, and so on. The loop continues until <code>i</code> reaches the specified number of rows (MaxRow)
 
'''VBA'''
   
   
{{APIComment|'Declare a new instance of the RawData class.}}
      {{APIComment|'Declare a new RawDataSet object.}}
  {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
        {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
 
      {{APIComment|'Name it "New Data Collection," and then specify that}}
      {{APIComment|'it is for use with Weibull++.}}
        DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
        DataCollection.ExtractedType = RawDataSetType.Weibull
   
   
{{APIComment|'Read each row of data from the Excel sheet.}}
      {{APIComment|'Declare a RawData object.}}
  {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
        {{APIPrefix|Dim}} Row {{APIPrefix|As}} RawData
  MaxRow = 20
 
      {{APIComment|'Open the Excel file. The following code assumes that the}}
      {{APIComment|'Excel file is saved in the C drive.}}
        {{APIPrefix|Dim}} Excel {{APIPrefix|As New}} Application
        {{APIPrefix|Dim}} Workbook {{APIPrefix|As}} Workbook
        {{APIPrefix|Dim}} Sheet {{APIPrefix|As}} Worksheet
   
   
  {{APIPrefix|For}} i = 2 {{APIPrefix|to}} MaxRow
        Workbook = Excel.Workbooks.Open({{APIString|"C:\SampleData.xlsx"}})
      {{APIPrefix|Set}} Row = {{APIPrefix|New}} RawData
        Sheet = Workbook.Sheets(1)
   
   
    {{APIComment|'Set the desired properties for the current row of data.}}
      {{APIComment|'Read each row of data in the Excel file.}}
      Row.StateFS = Sheet1.Cells(i, 1)
        {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
      Row.StateTime = Sheet1.Cells(i,2)
        MaxRow = 20
      Row.FailureMode = Sheet1.Cells(i,3)
    {{APIComment|'Add the current row to the data collection.}}
      {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
  {{APIPrefix|Next}} i
 
3. The next step is to save the data collection to the Synthesis repository.
 
Connect to the Synthesis repository and then use the <code>DataWarehouse.SaveRawDataSet</code> method to save the data collection to the repository.
 
'''VBA'''
{{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
{{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}}
  {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository 
  MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
{{APIComment|'Save the extracted data collection to the repository.}}
  {{APIPrefix|Call}} MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
 
=== Test the Code ===
Below is the complete code list for this VBA example. On the Debug menu, click '''Start''' to run the application. Then verify that the data were imported by launching Weibull++, opening the SDW ('''Home > Synthesis > Synthesis Data Warehouse''') and then selecting the new data collection. (You may need to display the StateTime, StateFS and FailureMode columns by right-clicking a column header and choosing '''Select Columns'''.)
 
'''VBA'''
    
    
Sub Main()
        {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
            Row = {{APIPrefix|New}} RawData
   
   
{{APIComment|'Declare a new instance of the RawDataSet class.}}
          {{APIComment|'Set the properties for the current data point.}}
  {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
            Row.StateFS = sheet.Cells(i, 1).text
            Row.StateTime = sheet.Cells(i, 2).value
            Row.FailureMode = sheet.Cells(i, 3).text
   
   
{{APIComment|'Name the data collection "New Data Collection," and then specify that}}
          {{APIComment|'Add the current data point to the data collection.}}
{{APIComment|'it is for use with Weibull++.}}
            DataCollection.AddDataRow(Row)
  DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
        {{APIPrefix|Next}} i
  DataCollection.ExtractedType = RawDataSetType_Weibull
   
   
{{APIComment|'Declare a new instance of the RawData class.}}
      {{APIComment|'Close the Excel file.}}
  {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
        Workbook.Close()
        Excel.Quit()
   
   
{{APIComment|'Read each row of data from the Excel sheet.}}
      {{APIComment|'Connect to a Synthesis repository.}}
  {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
        {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository 
  MaxRow = 20
        MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr11"}}){{APIComment|'Replace with name and path to test repository.}}
   
   
  {{APIPrefix|For}} i = 2 {{APIPrefix|to}} MaxRow
      {{APIComment|'Send the data collection to the repository.}}
      {{APIPrefix|Set}} Row = {{APIPrefix|New}} RawData
        MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
   
   
    {{APIComment|'Set the desired properties for the current row of data.}}
      {{APIPrefix|End Sub}}
      Row.StateFS = Sheet1.Cells(i, 1)
  {{APIPrefix|End Module}}
      Row.StateTime = Sheet1.Cells(i,2)
      Row.FailureMode = Sheet1.Cells(i,3)
    {{APIComment|'Add the current row to the data collection.}}
      {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
  {{APIPrefix|Next}} i
{{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
{{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}}
  {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository
  MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
   
{{APIComment|'Save the extracted data collection to the repository.}}
  {{APIPrefix|Call}} MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
End Sub
 
 
== VB.NET Tutorial: Transfer Data from an Excel File to the SDW ==
Before you begin, be sure to reference the Microsoft Excel Object Library. Choose '''Project > Add Reference''', and then select the library from the COM tab. Click '''OK'''.
 
Create a new form. On the code page, add an Imports statement for <code>Microsoft.Office.Interop.Excel</code>.


1. Start with the following code to create an SDW data collection. The code uses the <code>RawDataSet</code> object to represent an SDW data collection, and the <code>ExtractedType</code> property to specify that the data collection is for use with Weibull++.  
==Discussion==
Begin by creating an SDW data collection. The [[RawDataSet Class|RawDataSet]] class represents the data collection. For this example, we've set it to contain data sets for Weibull++.  


'''VB.NET'''
  {{APIComment|'Declare a new RawDataSet object.}}
  {{APIComment|'Declare a new instance of the RawDataSet class.}}
   {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
   {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
 
  {{APIComment|'Name the data collection "New Data Collection," and then specify that}}
  {{APIComment|'Name it "New Data Collection," and then specify that}}
  {{APIComment|'it is for use with Weibull++.}}
  {{APIComment|'it is for use with Weibull++.}}
   DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
   DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
   DataCollection.ExtractedType = RawDataSetType.Weibull
   DataCollection.ExtractedType = RawDataSetType.Weibull


2. Use the <code>RawData</code> object to represent a single row of data, and then use the <code>AddDataRow</code> method to add the row to the data collection.
Next, declare a [[RawData Class|RawData]] object, which represents a single data point. We will use this object to represent each row of data in the Excel file.


The following 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 <code>For i = 2</code>, the code creates a new <code>RawData</code> object and sets its properties by using the values from row #2 of the Excel file. After the code finishes reading the row of data, it adds the object to the data collection. When the code reaches <code>Next i</code>, it increases <code>i</code> with 1 and jumps back to the <code>For</code> statement, which adds another new <code>RawData</code> object for the data in row #3 of the Excel sheet, and so on. The loop continues until <code>i</code> reaches the specified number of rows (MaxRow).  Finally, the code closes the connection to the Excel file.
{{APIComment|'Declare a RawData object.}}
  {{APIPrefix|Dim}} Row {{APIPrefix|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 <code>For i = 2</code>, it creates a new <code>RawData</code> 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 <code>Next i</code>, it increases <code>i</code> with 1 and jumps back to the <code>For</code> statement, which creates a new <code>RawData</code> object to represent the data point in row #3 of the Excel sheet, and so on. The loop continues until <code>i</code> reaches the specified number of rows (MaxRow).  Finally, the code closes the connection to the Excel file.


'''VB.NET'''
{{APIComment|'Declare a new instance of the RawData class.}}
  {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
  {{APIComment|'Open the Excel file. The following code assumes that the}}
  {{APIComment|'Open the Excel file. The following code assumes that the}}
  {{APIComment|'Excel file is saved in the C drive.}}
  {{APIComment|'Excel file is saved in the C drive.}}
Line 200: Line 153:
   {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
   {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
   MaxRow = 20
   MaxRow = 20
 
   {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
   {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
       Row = {{APIPrefix|New}} RawData
       Row = {{APIPrefix|New}} RawData
   
   
     {{APIComment|'Set the properties for the current row of data.}}
     {{APIComment|'Set the properties for the current data point.}}
       Row.StateFS = sheet.Cells(i, 1).text
       Row.StateFS = sheet.Cells(i, 1).text
       Row.StateTime = sheet.Cells(i, 2).value
       Row.StateTime = sheet.Cells(i, 2).value
       Row.FailureMode = sheet.Cells(i, 3).text
       Row.FailureMode = sheet.Cells(i, 3).text
   
   
     {{APIComment|'Add the current row to the data collection.}}
     {{APIComment|'Add the current data point to the data collection.}}
       {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
       DataCollection.AddDataRow(Row)
   {{APIPrefix|Next}} i
   {{APIPrefix|Next}} i
   
   
Line 217: Line 170:
   Excel.Quit()
   Excel.Quit()


3. The next step is to save the data collection to the Synthesis repository.
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 [[Connect_or_Disconnect_from_a_Synthesis_Repository|this tutorial]].) 
 
{{APIComment|'Connect to a Synthesis repository.}}
  {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository
  MyRepository.ConnectToRepository({{APIString|"C:\RSRepository1.rsr11"}}){{APIComment|'Replace with name and path to test repository.}}


Connect to the Synthesis repository and then use the <code>DataWarehouse.SaveRawDataSet</code> method to save the data collection to the repository.
Then use the [[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet]] method to save the data collection to the Synthesis repository, as shown below.  


'''VB.NET'''
  {{APIComment|'Send the data collection to the repository.}}
{{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
{{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}} 
  {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository 
  MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
  {{APIComment|'Save the extracted data collection to the repository.}}
   MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
   MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)


=== Test the Code ===
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'''.)  
Below is the complete code list for this VB.NET example. On the Debug menu, click '''Start''' to run the application. Then verify that the data were imported by launching Weibull++, opening the SDW ('''Home > Synthesis > Synthesis Data Warehouse''') and then selecting the new data collection. (You may need to display the StateTime, StateFS and FailureMode columns by right-clicking a column header and choosing '''Select Columns'''.)  
 
==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 Class|RawData]] object) for a Weibull++ data set typically requires the following properties:
* LastInspectedTime
* NumberinState
* StateFS
* StateTime


'''VB.NET'''
For an RGA data set, the properties are:
* LastInspectedTime
{{APIPrefix|Imports}} SynthesisAPI
* StateFS
{{APIPrefix|Imports}} Microsoft.Office.Interop.Excel
* StateTime
* TopLevelSerialNumber
  {{APIPrefix|Public Class}} Form1
      {{APIPrefix|Private Sub}} Button1_Click(sender {{APIPrefix|As}} System.Object, e {{APIPrefix|As}} System.EventArgs) {{APIPrefix|Handles}} Button1.Click
      {{APIComment|'Declare a new instance of the RawDataSet class.}}
        {{APIPrefix|Dim}} DataCollection {{APIPrefix|As New}} RawDataSet
 
      {{APIComment|'Name the data collection "New Data Collection," and then specify that}}
      {{APIComment|'it is for use with Weibull++.}}
        DataCollection.ExtractedName = {{APIString|"New Data Collection"}}
        DataCollection.ExtractedType = RawDataSetType.Weibull
      {{APIComment|'Declare a new instance of the RawData class.}}
        {{APIPrefix|Dim}} Row {{APIPrefix|As New}} RawData
 
      {{APIComment|'Open the Excel file. The following code assumes that the}}
      {{APIComment|'Excel file is saved in the C drive.}}
        {{APIPrefix|Dim}} Excel {{APIPrefix|As New}} Application
        {{APIPrefix|Dim}} Workbook {{APIPrefix|As}} Workbook
        {{APIPrefix|Dim}} Sheet {{APIPrefix|As}} Worksheet
        Workbook = Excel.Workbooks.Open({{APIString|"C:\SampleData.xlsx"}})
        Sheet = Workbook.Sheets(1)
      {{APIComment|'Read each row of data in the Excel file.}}
        {{APIPrefix|Dim}} i {{APIPrefix|As}} Integer, MaxRow {{APIPrefix|As}} Integer
        MaxRow = 20
 
        {{APIPrefix|For}} i = 2 {{APIPrefix|To}} MaxRow
            Row = {{APIPrefix|New}} RawData
          {{APIComment|'Set the properties for the current row of data.}}
            Row.StateFS = sheet.Cells(i, 1).text
            Row.StateTime = sheet.Cells(i, 2).value
            Row.FailureMode = sheet.Cells(i, 3).text
          {{APIComment|'Add the current row to the data collection.}}
            {{APIPrefix|Call}} DataCollection.AddDataRow(Row)
        {{APIPrefix|Next}} i
      {{APIComment|'Close the Excel file.}}
        Workbook.Close()
        Excel.Quit()
      {{APIComment|'Connect to the Synthesis repository. The following code assumes that}}
      {{APIComment|'a standard repository called "RSRepository1.rsr10" exists in the C: drive.}} 
        {{APIPrefix|Dim}} MyRepository {{APIPrefix|As New}} Repository 
        MyRepository.ConnectToRepository ({{APIString|"C:\RSRepository1.rsr10"}})
      {{APIComment|'Save the extracted data collection to the repository.}}
        MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)
      {{APIPrefix|End Sub}}
{{APIPrefix|End Class}}


==References==
===References===
To learn more, see the reference documentation for the classes and methods discussed in this tutorial:
*[[RawData Class]]
*[[RawDataSet Class]]
*[[RawDataSet Class]]
*[[RawData Class]]
**[[AddDataRow Method|RawDataSet.AddDataRow Method]]
*[[AddDataRow Method|RawDataSet.AddDataRow Method]]
*[[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet Method]]
*[[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet Method]]

Latest revision as of 18:29, 3 April 2017

APIWiki.png


<< 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 copying 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.rsr11") '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. 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.rsr11") '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.

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

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.)

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