Product Profile 2.1: Citizenship and Immigration Canada Permanent Residents Rounded Data Cube

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

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. 

CIC Data Cube Terms and Conditions

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

Read and close this text file

And then press any key to continue.

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:

The graphical user interface for the CIC Data Cube

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:

Open the CIC Data Cube

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!