Example: Lifetime sow performance predicted by the first farrowing

Top  Previous  Next

A recent published study looked for a correlation between the number of pigs born alive to parity one females and their subsequent lifetime and reproductive performance on commercial farms in southern Europe (Ilida, Piñeiro and Koketsu, 2015).  The study results show an existing sub-population of sows in the farms that will usually perform better than their peers, and this sub-population can be identified at the first farrowing.

This tutorial shows how we can replicate a part of this study using Porcitec’s sow data analytical tool, OLAP. Online analytical processing, or OLAP, is an approach to answering multi-dimensional analytical queries swiftly, and is part of a broader category of business intelligence tools.

In this tutorial the selected analysis is the lifetime liveborn average of sows based on the number of liveborn at first farrowing.


Materials and methods

Among the different variables the study was based on, the most decisive one to explain the sow's subsequent performance turned out to be the number of liveborn piglets at first farrowing. Four categories were established in this respect for liveborn at first farrowing: 7 or less, from 8 to 11, from 12 to 14, 15 or more. The data for this tutorial was obtained from a large farm managed by Porcitec. It includes nearly 300,000 farrowings. We have avoided using the multi-farm feature of Porcitec in order to simplify this tutorial and to exclude any effects that heterogeneous farms could add to the results.


Creating the OLAP report

To create a OLAP report, from the Porcitec application, go to Report Browser and click New button. Then double-click OLAP icon.

The first thing to do is to define the data we want to use for the analysis. Click Data Source button in the toolbar. For this analysis, we will use the table of parities (ParityEx table), and as variables, we select ID (female ID), Parity, and Liveborn.

1.Click Wizard button and drag and drop the ParityEx table from the top right panel to the gray form.
2. Check the ID, PARITY and LIVEBORN variables.
3.Also, we need the liveborn at parity 1 (P1). Right click IID_P1PARITY and select Expand Joined Table. This brings up a detail table for the parity 1 record.
4.Check LIVEBORN variable from the detail table.
5.In the Columns sheet, click LIVEBORN in the last column to select it. Right click and select Alias. Enter P1_LIVEBORN.
6.In the criteria sheet, enter P.PARITY BETWEEN 1 AND 6 as a filter to include only parities between 1 and 6.
7.Click Run SQL button at the top left to see the results.


Click Accept Query icon in the toolbar to return to the previous screen. The generated SQL will be:


Click Ok button to return to the OLAP report designer.

Now the data source is ready and you need to define how you want to slice it. You can see the raw data by clicking the Cube Grid tab. The ID column was not strictly necessary but it can help us to verify some values if desired. Also, note under P1 LIVEBORN column, some cells may contain <null> values. These belong to sows that were entered into the breeding herd with parity>1. These rows will be excluded from the report when we create the categories.





Defining the slices

Once the data source has been defined, the second phase is to define the layout of the report. We want the parity number in the horizontal axis and the liveborn average in the vertical axis for each P1 liveborn cohort. Click Slice Grid tab to begin defining the layout.

The Field list button contains the list of variables you selected from your Data Source. The Measures button contains the mathematical functions and formatting for the variable you are measuring. Move the Measures button to the right of the Field list button.

Now click Field list button and drag and drop the LIVEBORN variable to the main panel.


At this point since there are no defined dimensions, OLAP report shows the sum of all liveborn.



To slice liveborn by parity, drag and drop the PARITY variable from Field list to the horizontal dimension headers bar where LIVEBORN is placed.


Now you can see that OLAP has sliced the liveborn by parity.




Finally, we want to slice it by liveborn at P1, so drag and drop the P1LIVEBORN to the vertical bar.



The above screen shows the vertical axis sliced by liveborn. However now we want to group the P1 liveborn by categories. Right click the 0 value under P1 LIVEBORN. Select Move to Group and then Create New. Enter 0-7 LB P1. Repeat the same steps for all liveborn values, creating the remaining groups 8-11 LB P1, 12-14 LB P1, 15+ LB P1.

Right click the null value and select Filter out this item to exclude it from the results. The following figure shows the sum of liveborn for each group and parity combination.


The liveborn results show the sum. To change to averages, right click any cell and select Properties. Select Average in Aggregate.


Then click Display Format sheet and select 12345.50 as the format to cut the number of decimals. Now the numeric report is finished and you can see that the results are consistent with the study.



Creating a chart

You can export this grid to Excel or other formats for further analysis or you can create a chart from Porcitec. Click Chart sheet. Click Line style in Chart style.




Click Chart Properties in toolbar. Select Series and categories from dimensions in the drop down box. Then select Dimensions from rows in Use as series, and select Dimensions from columns in Use as categories.


The following chart shows the results obtained. You can see that the difference between groups is maintained throughout the productive life of the sow.  For example, the second best group (12-14 LB P1) has 12.91 liveborn at first farrowing and maintains this performance difference between adjacent groups across all parities.



Adding number of farrowings and standard deviation

The created analysis displays only the averages. An average without knowing the sample size can be misleading. So now we want to add the number of farrowings and the standard deviation. The standard deviation measures the amount of variation or dispersion of the liveborn values.

To break down the cells vertically in 3 values (liveborn, SD, and count), drag and drop the Measures box to the vertical axis, just to the right side of P1 LIVEBORN box.


From the Field List, drag and drop LIVEBORN to the grid twice.

Click the arrow of Measures box. There are 3 measures. Right click the second one and select Properties.


Enter FARROWINGS in the Caption box.

Select Count in Aggregate drop down box.

Click Ok. Now the grid shows the number of farrowings.        

Click the arrow of Measures box. Right click the third one and select Properties.

Enter STANDARD DEV in the Caption box.

Select Std Dev in Aggregate drop down box.

Click Display Format sheet and select 12345.50.

Click Ok. Now the grid shows the standard deviation.

The following figure shows the numeric analysis: for each parity and group at P1, it shows the liveborn average, number of farrowings and the standard deviation.        




In this tutorial we have shown how to replicate one query of the study using your own data and the unique OLAP tool included in the Porcitec software application. We could continue doing a similar analysis for farrowing rate by this Parity 1 liveborn cohort, and other cases published in the study. Can we expect that farrowing rate is higher for sows that have higher liveborn numbers at first farrowing? Reproductive biology for litter size is different from biology for farrowing rate, so the outcome is not so obvious. Applications like Porcitec, used to manage the data for daily swine farm management, can also help us find answers to complex questions using its integrated analytic tools.