Talk:Transfer Data to the Synthesis Data Warehouse (SDW)/Notes - Revision history

Kate Racaza: Replaced content with '==DRAFTS=='

2016-04-28T18:36:42Z

Replaced content with '==DRAFTS=='

Kate Racaza at 18:27, 28 April 2016

2016-04-28T18:27:49Z

Kate Racaza: Created page with '{{Template:API}}{{Template:BacktoPrevPage|<< Back to Tutorials Main Page}} The Synthesis Data Warehouse (SDW) — formerly called "Reliability Data Warehouse" …'

2016-04-28T18:12:26Z

Created page with '{{Template:API}}{{Template:BacktoPrevPage|<< Back to Tutorials Main Page}} The Synthesis Data Warehouse (SDW) — formerly called "Reliability Data Warehouse" …'

New page

{{Template:API}}{{Template:BacktoPrevPage|[[API Tutorials|<< 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."

{| {{table}}
| 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 [[Transfer_Data_to_the_Synthesis_Data_Warehouse_(SDW)/VBA|here]].

'''VB.NET'''

{{APIPrefix|Imports}} SynthesisAPI
{{APIPrefix|Imports}} Microsoft.Office.Interop.Excel

{{APIPrefix|Public Module}} Module1
{{APIPrefix|Sub}} Main()

{{APIComment|'Declare a new RawDataSet object.}}
{{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|'Declare a new RawData object.}}
{{APIPrefix|Dim}} Row {{APIPrefix|As}} 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.}}
DataCollection.AddDataRow(Row)
{{APIPrefix|Next}} i

{{APIComment|'Close the Excel file.}}
Workbook.Close()
Excel.Quit()

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

{{APIComment|'Send the data collection to the repository.}}
MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)

{{APIPrefix|End Sub}}
{{APIPrefix|End Module}}

==Discussion==
Begin by creating a data collection or container. The [[RawDataSet Class|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++.

{{APIComment|'Declare a new RawDataSet object.}}
{{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

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

{{APIComment|'Declare a new 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>, the code 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 adds 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.

{{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 data point.}}
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 data point to the data collection.}}
DataCollection.AddDataRow(Row)
{{APIPrefix|Next}} i

{{APIComment|'Close the Excel file.}}
Workbook.Close()
Excel.Quit()

Now that we've filled the data collection with data, 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.rsr10"}}){{APIComment|'Replace with name and path to test repository.}}

Then use the [[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet]] method to save the data collection to the Synthesis repository.

To verify that the data were imported, launch Weibull++, open the SDW ('''Home > Synthesis > Synthesis Data Warehouse''') and then select 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'''.)

{{APIComment|'Send the data collection to the repository.}}
MyRepository.DataWarehouse.SaveRawDataSet(DataCollection)

==References==
*[[RawDataSet Class]]
*[[RawData Class]]
*[[AddDataRow Method|RawDataSet.AddDataRow Method]]
*[[Repository.DataWarehouse.SaveRawDataSet|Repository.DataWarehouse.SaveRawDataSet Method]]

← Older revision		Revision as of 18:27, 28 April 2016
Line 80:		Line 80:
	DataCollection.ExtractedType = RawDataSetType.Weibull		DataCollection.ExtractedType = RawDataSetType.Weibull

	{{APIComment\|'Declare a ~~new~~ RawData object.}}		{{APIComment\|'Declare a RawData object.}}
	{{APIPrefix\|Dim}} Row {{APIPrefix\|As}} RawData		{{APIPrefix\|Dim}} Row {{APIPrefix\|As}} RawData

Line 99:		Line 99:
	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.}}
	DataCollection.AddDataRow(Row)		DataCollection.AddDataRow(Row)
	{{APIPrefix\|Next}} i		{{APIPrefix\|Next}} i
Line 130:		Line 130:

	{{APIComment\|'Name it "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

	Next, we declare a [[RawData Class\|RawData]] object, which represents a single data point. We will use ~~the~~ object to represent each row of data in the Excel file.		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.

	{{APIComment\|'Declare a ~~new~~ RawData object.}}		{{APIComment\|'Declare a RawData object.}}
	{{APIPrefix\|Dim}} Row {{APIPrefix\|As}} RawData		{{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>, ~~the code~~ creates a new <code>RawData</code> object and uses the values in row #2 of the Excel file to define the data point.		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 ~~adds~~ 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.		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.

	{{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.}}
	{{APIPrefix\|Dim}} Excel {{APIPrefix\|As New}} Application		{{APIPrefix\|Dim}} Excel {{APIPrefix\|As New}} Application
	{{APIPrefix\|Dim}} Workbook {{APIPrefix\|As}} Workbook		{{APIPrefix\|Dim}} Workbook {{APIPrefix\|As}} Workbook
	{{APIPrefix\|Dim}} Sheet {{APIPrefix\|As}} Worksheet		{{APIPrefix\|Dim}} Sheet {{APIPrefix\|As}} Worksheet

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

	{{APIComment\|'Read each row of data in the Excel file.}}		{{APIComment\|'Read each row of data in the Excel file.}}
	{{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 data point.}}		{{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 data point to the data collection.}}		{{APIComment\|'Add the current data point to the data collection.}}
	DataCollection.AddDataRow(Row)		DataCollection.AddDataRow(Row)
	{{APIPrefix\|Next}} i		{{APIPrefix\|Next}} i

	{{APIComment\|'Close the Excel file.}}		{{APIComment\|'Close the Excel file.}}
	Workbook.Close()		Workbook.Close()
	Excel.Quit()		Excel.Quit()

	Now that we've filled the data collection with data, the next step is to send 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]].)		First, connect to a Synthesis repository (for details, see [[Connect_or_Disconnect_from_a_Synthesis_Repository\|this tutorial]].)
Line 180:		Line 180:
	MyRepository.ConnectToRepository({{APIString\|"C:\RSRepository1.rsr10"}}){{APIComment\|'Replace with name and path to test repository.}}		MyRepository.ConnectToRepository({{APIString\|"C:\RSRepository1.rsr10"}}){{APIComment\|'Replace with name and path to test repository.}}

	Then use the [[Repository.DataWarehouse.SaveRawDataSet\|Repository.DataWarehouse.SaveRawDataSet]] method to save the data collection to the Synthesis repository.		Then use the [[Repository.DataWarehouse.SaveRawDataSet\|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. (~~You~~ may need to display the StateTime, StateFS and FailureMode columns by right-clicking a column header and choosing '''Select Columns'''.)		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'''.)

	{{APIComment\|'Send the data collection to the repository.}}		{{APIComment\|'Send the data collection to the repository.}}