Getting started with Creator

This article provides fundamental tips for schools starting out with our Creator tool.

Creator is a user-friendly tool that helps schools analyse data beyond what you'll find covered in our standard dashboards without needing to know technical details. Creator lets you turn a range of your school’s data into easy-to-read charts, graphs, and reports.

➡️ Accessing Creator

➡️ Questions overview

➡️ Joining data

➡️ Filtering

➡️ Summarising and 'grouping by'

➡️ Accessing Creator

🪪 License and permissions

Creator is accessible to schools with the appropriate license. Depending on your subscription, you'll have a certain number of Creator 'seats' allocated to your school. Once you've confirmed that your school has access, and how many seats you have available, you'll then be able to arrange the appropriate permissions inside the 'Settings' area. Here's a quick video on how to modify permissions inside 'Settings'.

🔎 Finding Creator

After completing the above steps, you'll find the Creator tool by logging into intellischool.app, and clicking the magic wand icon labelled 'Creator' on the toolbar.

⚠️ It's worth noting that if you usually access Intellischool Analytics via your LMS (i.e. Schoolbox, SIMON), you won't be able to see the toolbar pictured below - you'll need to login directly to intellischool.app.

🗺️ Navigating your data

When opening Creator for the first time, you'll see a few options on your 'Home' page. Take a look in 'Browse data' and click on 'Intellischool Data Platform'. In here, you'll find options for 'my_simplified_data' and 'my_warehouse'.

In 'my_simplified_data', you'll find pre-made recommendations for data reports, and in 'my_warehouse', you'll find all available data tables that can be used for building your own reports.

🧑🏽‍💻 Pick your starting data

When clicking on 'New' in the top right-hand corner and choosing 'Question,' you'll be first asked to 'Pick your starting data.' Here, you can choose from either 'My Simplified Data' or 'My Warehouse' under the 'Intellischool Data Platform' menu.

➡️ Questions overview

Questions are queries plus a data visualisation that you build for the query. You can ask questions using Creator’s graphical query builder, or create a native/SQL query.

Creator’s two core concepts are questions and their corresponding answers. To ask a question in Creator, click the + New button in the upper right of the main navigation bar, and select either:

• Question
• SQL Query

This section covers how to ask a question using Creator’s graphical query builder, the “Question” option.

💭 Creating a new question with the query builder

From the + New dropdown, select Question, then pick your starting data:

You can start a question from:

• Tables. You’ll need to specify the database and the table in that database as the starting point for your question.
• A saved question. You can use the results of any question as the starting point for a new question.

🛠️ The query builder

Once you select your data, Creator will take you to the query builder. Say you selected Tables > Sample database > Orders, then you’ll see something like this:

This is the query builder’s editor. It has three default steps.

• Picking data
• Filtering
• Summarising and grouping by

To the right of each completed step is a Preview button (looks like a Play button - a triangle pointing to the right) that shows you the first 10 rows of the results of your question up to that step.

📊 Picking data

The data section is where you select the data you want to work with. Here you’ll pick a model, a table from a database, or a saved question. You can click on a table to select which columns you want to include in your results, noting that you can remove columns in the settings for the table.

➡️ Joining data

You can also select multiple tables from the same database by joining them.

You can join data to combine your current data with another table, or even with a saved question.

After you click on the Join Data button to add a join step, you’ll need to pick the data (from the same database) that you want to join. You can only pick tables and saved questions that are from the same database as your starting data.

Next, you’ll need to pick the columns you want to join on. This means you pick a column from the first table, and a column from the second table, and the join will stitch rows together where the value from the first column is equal to the value in the second column. A very common example is to join on an ID column in each table, so if you happened to pick a table to join on where there is a foreign key relationship between the tables, Creator will automatically pick those corresponding ID columns for you. At the end of your join step, there’s a Columns button you can click to choose which columns you want to include from the joined data.

By default, Creator will do a left outer join, but you can click on the Venn diagram icon to select a different type of join. Not all databases support all types of joins, so Creator will only display the options supported by the database you’re using.

Here are the basic types of joins:

• Left outer join: select all records from Table A, along with records from Table B that meet the join condition, if any.
• Right outer join: select all records from Table B, along with records from Table A that meet the join condition, if any.
• Inner join: only select the records from Table A and B where the join condition is met.
• Full outer join: select all records from both tables, whether or not the join condition is met.

A left outer join example: If Table A is Orders and Table B is Customers, and assuming you do a join where the customer_id column in Orders is equal to the ID column in Customers, when you do a left outer join your results will be a full list of all your orders, and each order row will also display the columns of the customer who placed that order. Since a single customer can place many orders, a given customer’s information might be repeated many times for different order rows. If there isn’t a corresponding customer for a given order, the order’s information will be shown, but the customer columns will just be blank for that row.

➡️ Filtering

Filtering just means narrowing things down based on certain criteria. You’re probably already familiar with filtering when looking for something online, like when shopping. Maybe you only want to see Year 10 students, or students enrolled in Year 10 at a particular campus, or students in Year 10 at a particular campus studying Japanese.

When you add a filter step, you can select one or more columns to filter on. Depending on the data type of the column you pick, you’ll get different filter types, like a calendar for date columns.

You can add subsequent filter steps after each summarise step. This lets you do things like summarise by the count of rows per month, and then add a filter on the count column to only include rows where the count is greater than 100. (This is basically like a SQL HAVING clause.)

Once you’re happy with your filter, click Add filter, and visualise your results. Your data will be updated with the filter applied.

If you want to edit your filter, just click the little purple filter at the top of the screen. If you click on the X, you’ll remove your filter. You can add as many filters as you need.

🧮 Filter types

Depending on the data type of the column, Creator will present different filtering options.

• Numeric columns let you add filters to only include rows in your table where this number is between two specific values, or is greater or less than a specific value, or is exactly equal to something.
• Text or category columns let you specify that you only want to include data where this column is or isn’t a specific option, whether it contains, starts with, or ends with a substring, or whether the row is empty or not.
• Date columns give you a lot of options to filter by specific date ranges, relative date ranges, and more.
• Structured data columns, typically JSON or XML, can only be filtered by “Is empty” or “Not empty”. Some databases, however, support JSON unfolding, which allows you to split up JSON data into separate columns, which you can then filter on.

When viewing a table or chart, clicking on the Filter will bring up the filter modal.

Here you can add multiple filters to your question in one go (which can save you a lot of loading time). Filter options will differ depending on the field type. Any tables linked by foreign keys will be displayed in the left tab of the modal.

When you’re done adding filters, hit Apply filters to rerun the query and update its results. To remove all the filters you’ve applied, click on Clear all filters in the bottom left of the filter modal. Any filters you apply here will show up in the editor, and vice versa.

📆 Filtering by date

One important thing to understand when filtering on a date column is the difference between specific and relative dates:

• Specific dates are things like November 1, 2010, or June 3 – July 12, 2017; they always refer to the same date(s).
• Relative dates are things like “the past 30 days,” or “the current week;” as time passes, the dates these options refer to change. Relative dates are a useful way to set up a filter on a question so that it stays up-to-date by showing you, for example, how many people visited your website in the last 7 days. You can also click on the … to specify a Starting from option, which lets you offset the relative date range. For example, you could set the range as the “Previous 7 days, starting from 2 days ago”.

🧮 Filtering by a segment

If your Creator administrators have created special named filters for the table you’re viewing, they’ll appear at the top of the filter dropdown in purple text with a star next to them. These are called Segments, and they’re shortcuts to a combination of filters that are commonly used in your school. They might be called things like “Active students,” or “Subjects with largest enrolments.”

🧮 Filter with custom expressions

If you have a more complex filter you’re trying to express, you can pick Custom Expression from the add filter menu to create a filter expression. You can use comparison operators like greater than, >, or less than ,<, as well as spreadsheet-like functions. For example, [Subtotal] > 100 OR median([Age]) < 40.

➡️ Summarising and 'grouping by'

When we have a question like “how many people downloaded our app each day last week?”, we’re asking for a summary of the data. A summary is usually made up of two parts: one or more numbers we care about (called a “metric” in data-speak), and how we want to see that number grouped or broken out. To answer that example question of “How many people downloaded our app each day last week?”

• The metric would be the count of people who downloaded the app (the count of rows).
• We want that metric to be grouped by “each day.”
• And we want to filter the rows for “last week.”

There are two common ways you’ll tend to summarise your data:

• Counting the number of rows in your table
• Getting the sum or average of a numeric column

And a lot of the time, you’ll then group that metric by:

• Time
• Place
• Category

Adding a summarise step lets you choose how to aggregate the data from the previous step. You can pick one or more metrics, and optionally group those metrics by one or more dimensions (columns). When picking your metrics you can choose from basic functions like sum, average, and count; or you can pick a common metric defined by an admin; or you can create a custom expression by writing a formula.

Common metrics include:

• Count of rows: the total of number of rows in the table, after any filters have been applied. If you’re looking at your Orders table and want to know how many orders were placed with a price greater than $40, you’d filter by “Price greater than 40,” and then select Count of rows, because you want Creator to count how many orders matched your filter.
• Sum of …: the sum of all the values in a specific column.
• Average of …: the average of all the values in a single column.
• Number of distinct values of…: the number of unique values in all the cells of a single column. This is useful when trying to find out things like how many different types of products were sold last month (not how many were sold in total).
• Cumulative sum of…: This gives you a running total for a specific column. In order for this metric to be useful you’ll need to group it by a date column to see it across time.
• Cumulative count of rows: This gives you a running total of the number of rows in the table over time. Just like Cumulative sum of…, you’ll need to group this by a date column in order for it to be useful.
• Standard deviation of …: A number which expresses how much the values of a column vary, plus or minus, from the average value of that column.
• Minimum of …: The minimum value present in the selected field.
• Maximum of …: The maximum value present in the selected field.

If you summarise and add a grouping you can then summarise again. You can also add steps to filter and/or join in between. For example, your first summarisation step could be to get the count of orders per month, and you could then add a second summarisation step to get the average monthly order total by selecting the Average of… your count column.

You can also add metrics and groupings on the results page in a sidebar: the top of the sidebar where you pick the number (“metric”) you want to see, and the part below is where you pick how to group that number (or how to “break it out”).

If your admins have created any named metrics that are specific to your company or organisation, they will be in this dropdown under the Common Metrics section. These might be things like your company’s official way of calculating revenue.

🔗 Grouping your metrics

Depending on the grouping column you select, Creator will show you what it thinks is the best default visualisation or chart for this summary. So if you select a date column, you’ll see a line chart like this (you can click the green Summarise button to view the summarise sidebar).When you click on a different grouping column than the one you currently have selected, the grouping will switch to use that column instead. But if you want to add an additional grouping, just click the plus (+) icon on the right side of the column. To remove a grouping, click on the X icon.

Some grouping columns will give you the option of choosing how big or small to make the groupings. So for example, if you’ve picked a Date column to group by, you can click on the words by month to change the grouping to day, week, hour, quarter, year, etc. If you’re grouping by a numeric column, like age, Creator will automatically “bin” the results, so you’ll see your metric grouped in age brackets, like 0–10, 11–20, 21–30, etc. Just like with dates, you can click on the current binning option to change it to a specific number of bins. It’s not currently possible to choose your own ranges for bins, though.

Once you’re done setting your metrics and groupings, click Visualise to see your results in all their glory.

🔎 Visualising your data

If you want to jump ahead and learn about how to change the visualisation of your results, by all means, feel free.

🗜️ Drill-through menu

When viewing a chart, you can also click through questions to explore the data in greater detail.

The drill-through menu will present different options depending on what you click on. You can then optionally save any exploration as a new question. The drill-through menu is only available for questions built using the query builder.

📊 Column heading drill-through

When viewing a table, clicking on the heading of a column gives you different options, depending on the columns data type.

⏪ Returning to the editor

To return to the editor for a question, click on the Show editor button in the upper right.

👤 Viewing an individual record’s details

To see more info about a given record (a user account, order, venue, etc.), click on a record’s ID number (or primary key). You can see all fields related to that one record and all connected tables that are hidden in the table view for the sake of readability. To page through the other records in the current table, press the right or left arrow keys, or click on the arrows to the right or left of the screen.

🖌️ Custom expressions

Custom expressions allow you to use spreadsheet-like functions and simple arithmetic within or between aggregation functions.

For example, you could do Average(sqrt[FieldX]) + Sum([FieldY]) or Max(floor([FieldX] - [FieldY])), where FieldX and FieldY are fields in the currently selected table. 

📊 Creating custom columns

Custom columns are helpful when you need to create a new column based on a calculation, such as subtracting the value of one column from another, or extracting a portion of an existing text column. Custom columns that you add aren’t permanently added to your table; the columns will only be present in the given question.

You can use the following math operators in your formulas: +, –, * (multiplication), and / (division), along with a whole host of spreadsheet-like functions. You can also use parentheses to clarify the order of operations.

➕ Adding or removing columns in a table

When viewing tables, you can click on the gear icon in the bottom left to bring up the columns picker. Click Add or remove columns to search for and pick columns, including columns from related tables.

↔️ Sorting results

The sorting step lets you pick one or more columns to sort your results by. For each column you pick, you can also choose whether to sort ascending or descending; just click the arrow to change from ascending (up arrow) to descending (down arrow).

⬆️ Setting a row limit

The row limit step lets you cap how many rows you want from the previous results. When used in conjunction with sorting, limits can let you do things like create a top-10 list, by first sorting by one of the columns in your result, then adding a row limit of 10. Unlike other steps, the row limit step can only be added at the end of your question. If you do want to add more steps to limited results, you can always save the limited results as a question, then start a new question based on those results.

🛝 Play around with saved questions

Each time you start modifying a saved question, Creator will create a new question for you. The query builder will display the name of your starting question under Data.

Feel free to play around with any saved question, as you won’t have any effect on the existing question. When you hit Save on the question, you can choose either to save as a new question (the default), or you can overwrite the existing question you started from.