Note: This profile refers to the 2010 data, but applies to subsequent CIC data as well.

Contents

1. What is the Permanent Residents Rounded Data Cube?
2. Basic info for CIC data cube
3. How to make the CIC Data Cube work
4. Variable categories ("fields") and variables
5. Playing with the pivot table: columns and rows
6. Playing with the pivot table: filters
7. Saving a table

What is the Permanent Residents Rounded Data Cube?

The Permanent Residents Rounded Data Cube (referred to here as the CIC Data Cube, or simply "Cube") is a Citizenship and Immigration Canada dataset that counts the number of permanent resident landings at any given Census geography down to the Census Subdivision.

The "cube" means that you can drill down to more specific characteristics of permanent residents, like age, years of education, mother tongue, immigrant class, and occupation.  But before we muddle you more, let's start with some basics about the dataset itself.

Basic info for CIC data cube

File name	PR_FFR2010.cub (must install "install.exe", then use FFR2010_Offline_PRCube.xls to view data as a PivotTable in Microsoft Excel)
Metadata and variables	Updated 2013-09-26: CIC_PRCube_2012_vars.xls
Date	2000-2010, yearly
Scale	Canada, Province, Census Agglomeration (CA) / Census Metropolitan Area (CMA), Census Division (CD), Census Subdivision (CSD), Destination*
Extent	Canada
Source	Citizenship and Immigration Canada
Description	The Citizenship and Immigration (CIC) Permanent Residents Rounded Data Cube counts the number of permanent residents in a given Census geography, crosstabulated by age, landing year, country of birth, country of citizenship, country of last permanent residence, education level, gender, immigrant class (incl. subcategories), marital status, mother tongue, occupation skills (National occupation codes 2, 3, and 4 digit, and skill level), official languages spoken, and years of schooling.  The data cube is a multidimensional database that uses a Microsoft Excel PivotTable as a user interface.
Additional documentation	Glossary and user guide

*Destination is not a standard Census geography.

How to make the CIC Data Cube work

To use the CIC Data Cube, you will first need to install it on your computer.  Note to larger organizations: this will mean getting help from your IT department.  Either that or you can use your personal computer.

Here are the steps:

1. Download the zipped data cube from communitydata.ca

http://communitydata.ca/content/permanent-residents-rounded-data-cube-2…

2. Unzip the file anywhere on your computer

If you are having trouble unzipping the file, let us know and we'll help you out.  For smaller organizations we recommend using PeaZip because it's free and works well.

This may go without saying, but you can unzip the file anywhere on your computer – My Documents, Downloads, etc.  We recommend creating a new folder and giving it a memorable name (e.g. "CIC Data Cube") in which to place the unzipped files.

3. In the "cube" folder, double-click on install.exe

In the folder called "cube", run the file called "install.exe".  You will need admin privileges to do so.  In most small organizations, users will already have admin privileges.  For those in larger organizations, you will need to contact your IT department.

4. Accept the terms and conditions, and finish the installation

When you double-click on install.exe, you will have to accept the terms and conditions of use set out by CIC.  This notably includes citing the source of the data in publications.  It also explains how the values in the dataset are rounded to the nearest 0 or 5 for confidentiality purposes. 

Upon accepting the terms, you will need to read and close the subsequent window that opens.

And then press any key to continue.

The cube should now be installed in a folder with a similar path to this (except the drive may not be C in your case):

C:\FFR2010_PR\

Within this folder, you will see a file called "gui.menu.exe -- run this file to open the CIC Data Cube.  You should see a screen that looks like this:

Select "Permanent Residents" to view data.  When you select "Permanent Residents", an XLS file will download from your default internet browser (Explorer or Chrome or Firefox).  It will look similar to this:

Mercifully, you will see an Excel pivot table when you open the file.  It should look exactly like this:

At this point, you can play around yourself.  The rest of this Product Profile will describe the variables available to work with, and how exactly to work with them.

Variable categories ("fields") and variables

There are 17 variable categories (called "fields" in Excel pivot tables) that you can use in the CIC Data Cube.  These are:

Age	Gender	Occupation Skills - NOC Codes
Calendar [time period]	Geography CD	Official Languages Spoken
Country of Birth	Geography CMA	Skill Level Hierarchy
Country of Citizenship	Immigration Class	Skilled Worker Regime
Country of Last Permanent Residence	Marital Status	Years of Schooling
Educational Level	Mother Tongue

Each field includes a set of individual variables, and can be crosstabulated against other fields. 

For example, it's possible to count not only the number of permanent resident landings whose mother tongue is Punjabi [166,695] as well as those with a bachelor's degree [666,375], but also Punjabi-speakers with a bachelor's degree [47,960].

We've listed the variables in each field below, except when there are too many variables to reasonably list, in which case we explain what variables are available.  [Updated 2013-09-26: You can consult the variable list in this Excel spreadsheet.]

Age	Individual years to 85 Five-year intervals to 89 Large intervals: 0-14, 15-24, 25-44, 45-64, 65+
Calendar	Year, Quarter, Month, from January 2000 to December 2010
Country of Birth	Africa and the Middle East Asia and Pacific Europe and the United Kingdom United States     South and Central America Source area not stated Plus each individual country
Education Level	0 to 9 years of schooling 10 to 12 years of schooling 13 or more years of schooling Trade certificate Non-university diploma Bachelor's degree Master's degree Doctorate
Gender	Male Female Gender not stated
Geography CD	Province, Economic region, CD, CSD, "Destination" (non-Census geography, a grab bag of small municipalities, townships, and neigbhourhoods)
Geography CMA	Province, CMA/CA, CSD, Destination
Immigration Class	Family class Economic immigrants - p.a. [principal applicants] Economic immigrants - s.d. [spouses and dependents] Refugees Other immigrants Category not stated Plus every subcategory within each immigration class
Marital Status	Single     Married, common-law partner Separated, divorced, widowed Marital status not stated
Mother Tongue	241 individual languages
Occupation Skills	38 NOC2 codes 541 NOC4 codes 7 occupational skills
Official Languages Spoken	English French Both French and English Neither
Skill level hierarchy	Unknown Intending to work Not intending to work Plus skill levels for those intending to work Plus age groups for those not intending to work
Skilled Worker Regime	Pre-IRPA Transitional rules/Dual assess IRPA - pre Ministerial Instructions IRPA - Subject to Ministerial Instructions Quebec skilled workers Skilled worker regime not stated Not applicable
Years of Schooling	Individual years of schooling (0-25)

The next section explains how to set up your pivot table in order to see the variables that you're interested in.

Working with the pivot table: columns and rows

In general, pivot tables help to summarize underlying datasets that are complicated and often multidimensional.  In this case, the underlying dataset is the multidimensional CIC Data Cube, and the pivot table is the summary table that you see when you open FFR2010_Offline_PRCube.xls.

The pivot table contains two windows: a spreadsheet to the left (the actual pivot table), and a field list to the right.  The default arrangement shows the number of permanent resident landings ("Rounded Clients") for all of Canada, with rows presented as immigration classes ("Landing Category - Big5") and columns represented as years ("Year").

As you can see, the total number of permanent residents landing in Canada each year has increased from 227,455 in 2000 to 280,685 in 2010.

To play with the pivot table, you can choose from any number of "fields" in the Field List window to the right, and drag them into the row or column position, depending on whether or not you wish to view them as rows or columns.  You can also drag them back into the Field List in order to remove them from the pivot table. 

Let's try this out: Drag "Base Immigration Class Big5 Hierarchy" from the Row Label box into the "Choose fields to add to report" box above.  Your table will now only have one row: "Total", as in total permanent resident landings per year.

Now drag "Education Level" into the Row Label box.  Notice how rows now become individual education levels, e.g. Bachelor's degree.  In other words, the pivot table is now showing how many permanent residents landed each year, by various levels of prior educational attainment.

Let's try the same thing with a geographic field.  Drag Education Level back into the top window.  Then drag "Region CMA Hierarchy" into the Row Label box.  You should now see a table that looks like this:

For each province (represented as a row), it is possible to select the plus sign to its left in order to view a smaller geography.  For example, you can select Saskatchewan (province), then Regina (CMA), then Edenwold No. 158 (CSD) and then White City (Destination).  Again, destinations are not standard Census geographies—they're a grab bag of neighbourhoods and place names.

Playing with the pivot table: filters

In addition to setting up your rows and columns, you can also filter variables within any given field.  Say, for example, you're interested only in data for the Regina CMA.  In this case, it's not worth dragging the Geography CMA field into the Row Label box, because then you will see every available geography.  By filtering Regina CMA, you can only view data for that geographic unit.

To set up a filter, drag the field (in this case Geography CMA) into the "Report Filter" box.  Your pivot table will then have a row at the top that says Region CMA Hierarchy followed by a drop-down menu.  From that drop-down menu, you can select the variable that you want to filter (in this case Regina).

Once you filter Regina, the numbers in your table will shrink to 670 in 2000, and 2,565 in 2010—the number of permanent residents who landed in the CMA of Regina during those years.

You can add as many filters as you wish by dragging fields into the "Report Filter" box, and setting the filter itself in the pivot table.

Saving a table

Avoid saving the original file (FFR2010_Offline_PRCube.xls).  When you've set up a pivot table that you want, copy and paste it into another spreadsheet.  Use that other spreadsheet to in order to modify the data however you wish.

--

This Product Profile is a working document and will evolve as we receive feedback from users.  If you have any questions or comments, make sure to get in touch with us at information@communitydata.ca.  We recommend sharing findings, best practices, and press releases with other CDP members.  Let us know too, so that we can show off your work! 

Happy data!