Getting Started in Count - a tutorial

Hi. This is Mitra from Count. Count is different to other BI tools. It allows analytical engineers, analysts, and other business users to work together in one tool for the first time. In Count, we can build data models and define metrics. We can conduct exploratory analysis that's easy to share across our organization. We can also create reports and data stories in new and exciting ways. And all this is supported by a standardized set of metrics that every user at any level can engage with. In this tutorial, I will introduce you to the platform and help you get started straight away. The first environment you come to encounter is your workspace. Typically, each organization has one workspace and it houses all your canvases and the data connections you have access to. Navigation of the workspace is contained in the left hand panel and I'm going to be focusing on projects. These are folders to help organize canvases, and depending on how you or your organization have set these up, you may find company wide projects, department specific projects, and also analyst specific projects, and you will only see projects that you have been given access to. So these individual sandboxes are a really useful place to allow you the freedom to do exploratory work in your own space before you're ready to share with others. And it's from within these projects that we can start creating canvases. To do this, I'm going to click on new canvas. The canvas is the heart of count, and and I'm going to give you a quick tour of this environment. There are three main areas of navigation. There is a data bar on the left hand side which details all the data connections available to you in this canvas. There's a toolbar at the bottom which allows us to add objects and to use whiteboard tools and there's a design bar on the right hand side and it's from here that we can format different objects when they are selected on our Canvas. So the first thing we will often want to do with a blank canvas is add some data to it. So let's start in our data bar. In the sources section, we can see any database connections that have been made available to us in this canvas such as this BigQuery one and also any metric catalogs which have been defined using our semantic layer like this one. So if I were to click on one of these sources and then come to the panel below, this is where I can learn more about the schema. So by clicking, I can find out what tables are in here and which columns are available. So one way that we can easily get some data onto our canvas from here is using this add to canvas button. We can see here that it has added a SQL cell. So SQL cells are fundamental to analysis and count. It's how we add data from our database to our canvas and also how we build up our analysis. Let's look at what is in a SQL cell. Well, it has a title. I'm going to give it a better name. It has some SQL code, and it has the results of this query. Unlike other tools where we might build up our SQL code in one place, KIND allows us to explore our data in a sequence of individual cells. And all of our cells can use the results of any other cell. So let's see what that looks like. I'm going to hover over my selected SQL cell to add a new cell. There are various types, but I'm going to stick with SQL. Here you can see the start of some suggested SQL and it's referencing accounts. And this is what I called my SQL cell originally, and that's why it's important to name yourselves well as this is what we use to reference other cells. So I'm going to go ahead and add some more SQL here. So here we can see the results. We can see that this is a subset of the cell that I've pre read because I have included, filter on it. Let's explore other cell types. We also have visuals. By adding a visual cell type, we can see that we have an empty cell. But in our design panel on the right hand side, we now have some suggestions on visuals. There are two tabs here to be aware of. We can make templated visuals, which are standard charts that are commonly used, or we can look at the custom tab, and this has more options that allow us to build up bespoke visuals. So let's go back to our templated options and choose something straightforward. Visuals are created through a low code drag and drop. So here we have some fields that we need to populate. And on the right hand side, we can view the source that we're using. So the source is new accounts, which was my latest cell. We are using the results of this and these are the columns that make that up. So let's go ahead and drag some of these along. I'm gonna drag the date. It has defaulted to year, so if I want to change that, I just click on the three dots and I can see all the other aggregations that are available to me. I'll click on month. Let's put the number of employees and maybe for some more depth. Let's, in the color field, pull an industry, and that will color code this by industry. If I want to do some simple formatting again, I can click on the three dots, and we have a display tab. And from here, I can maybe choose my brand colors. Okay. So let's look at another cell type. I'm going to add cell and this time we'll look at a table. So this is another low code option. And here, again, we can see the data source on the right hand side and a much more simplified option here to drag in columns. I'm going to drag, let's say, name, employees, and industry. Resize this. And maybe alongside my analysis, I would like just a list of named accounts, and I can sort this on number of employees. So now we are showing the top accounts that make up our new accounts in the last year. Okay. So we've explored how to create some SQL cells. We've looked at visual cell and the table cell, and it's important to know that cells are reactive. So any change that is made to a parent cell means that everything downstream will be updated to reflect that. I'm going to go into this cell and just make a minor change to that date, and you will see that that cell and everything downstream of it is updating. It can be useful to understand what's actually happening behind the scenes here as well. Each cell that we have created here is running a different query back to the database. If I were to click on this one and choose copy as SQL, We can see here that there is a DAG, a short one, because there's only one parent cell upstream and this SQL has been compiled on the fly and it contains the logic from this cell and the logic of all cells above it. So this is the query that is run back to the database for this cell, and this is the same for any cell, including our visuals. You may also have noticed that in our data sources menu, we have a local database and this is counts in memory database that you can use while you're working in count. And this can speed up your queries, and it can reduce the overall query load back to your database. So to lean on this, we can simply highlight the cells downstream of our first parent cell. And then under source, we can just change this to local. You might notice that the colors behind the titles have changed and they match up to our data sources. So BigQuery here we can see was green. So this is still a BigQuery cell, but local here is purple, and we can see that these have changed. So the benefit now is that by running this one query against your database, we can then move into our local dot DB database and do as much analysis as we would like without sending additional queries back to your database. I mentioned as well, it makes your queries a lot quicker. So, again, if I come and make a change here like we did before, you'll see that everything downstream changes extremely quickly. Whereas before, when we were running that on our BigQuery database, it was taking a few seconds. So I recommend leaning on a dot DB database where possible. So let's go back to cell types. We've covered a few, but there are a couple more to go. I'm gonna draw your attention down to the toolbar, just so that you know that you can use this toolbar to add some of these cells as well. You can add blank SQL cells from here. There's artificial and table cells. And next, we're going to look at control cells. So control cells are great for setting up filters for reports or using them to specify parameters in your analysis which you want to be able to change easily. There are lots of options for control cells, and I'd recommend watching the video about control cells and reading the documentation on these if you'd like to know more. I'm just gonna choose one to show you as an example today. So I'm gonna go for a multiple select. I'm going to add this to my canvas. It looks fairly nondescript, and we're gonna start by setting this up in the design panel on the right hand side. I'm gonna set dynamic controls. So this means that the options that users are going to be able to select from are going to come from a table. Let's say I want it from my original accounts table, and let's look at industry. I select that. So now that I have this set up, if I come to my control cell, we'll see that all the unique entries from the industry column are populated. And because this is a multi select, I can select one or more of these options. We're halfway there. We've created our control cell. But how do we actually put this to use? You'll see that we can reference this using some SQL, or we can use some ginger code here. I'll start by giving this a slightly more meaningful name. We'll call it control one and then you'll see that the reference using SQL changes. Essentially, control cells are just tables. So let's take this. I'm going to add the logic to connect this control cell. So here I have stated in my where clause that industry is in, and then I have just selected value from my control. I've used in because essentially has multiple results rather than equals. Now we have our control cell and we know that everything will update downstream. So let's have a look at our chart as we take off some of these options and now we can see that we are filtering not only on our SQL cell but all the visualisations that we've created as well. And there's one final cell type to talk to you. Right? Again, I will come down to add cell, and this time, we'll show you Python. We can move between SQL and Python seamlessly. I will just add in a simple Python script, and we can see all the elements are there. We have a title, we have some of our Python code, and we have our results. And just to demonstrate that, we can then go back to SQL if we want. I will just do a select star from that cell. So now let's start looking at some of the other tools available in Kount to help us organize and present our data and to collaborate with others. First of all, I will draw your attention to overviews, which is in the left hand panel. It's a small area where you can introduce users to the Canvas and help them get started using it. If I use a forward slash, I can add some formatted text. And I can also bring in that control cell that we created. So now wherever we are in the canvas, our control cell is here as well as some instructions for our users. The next feature I will look at is templates. We always want our data outputs to look professional and look polished, but we don't always have the time or even the skills to put into DesignMark. So templates can enable us to have consistency between our outputs and help us make them look polished in no time at all. So the templates icon is in our data panel. If I click on this, you will see templates made within your organization and also built in templates that come from us account. I'm going to select this single insight template. If I click use template, it will simply add it to the canvas wherever I am. You can see here we have some placeholders for text, our brand, and we have space for official that's empty. So all I have to do is grab my official and drop it into this space, and it will snap into place and resize. I think the only thing that maybe I would like to do with this is get rid of this white background. By clicking in it, I can come down to style, which helps me format my visual, and I'm just gonna take that solid white background off. And there we are. In just a few clicks, we have gone from a fairly basic looking chart to the start of something that I could share and that looks more professional. I could give it a title and I could update the text down here. Next, I'll come on to our whiteboard tools and these are in the toolbar at the bottom of your screen. These are a great way to communicate and collaborate with far more freedom than you're probably used to. We can add sticky notes, for example. We can also add text boxes. We can add shapes. We can use a pencil if we just want to draw on top of these. And you can also use the comment tool. The benefit of this being that you can tag people in a message. And by sending this, they will get an email notification, and you can continue that conversation in this comment thread. And the last one that I'll come on to is frames. Frames are incredibly useful in count. They allow us to organize elements in our canvas. So for example, I could drag and select all these cells. And now when I move this frame, these are all contained within it. The other benefit frames is that they are the basis for creating reports in count. We've just added a frame, and my template here came in a frame as well. So we have two on this canvas. I'm going to click on report. So report view is very powerful in allowing us to focus attention on the elements of our canvas that really matter. So it takes the viewer away from the canvas environment. And here we can see one of the frames that I created from my template. And if I click across, we can see the second frame, which was my analysis. So you can see that by adding frames to canvas, we could create a very nice slideshow that can be presented. Or maybe we just want a single slide or a single dashboard. Within our settings menu, I could turn off any frames that I don't want to show. So now I just have a view of this one slide. You'll remember that we added our overview, and that still comes with us in report view, and we can still interact with these here. We've explored how to build up analysis and count and how to create simple outputs. So now I'd like to look at alerts. Alerts allow you and your team to be notified about your data by email or Slack even when you're not in a canvas. And it's a great way to ensure regular visibility of your work so that your analysis isn't at risk of gathering dust and being forgotten by. So alerts can be set up on individual cells or they can be set up on entire frames if you've added some context around those. Here with my frame selected, all I have to do is go to this icon and add alert. You'll notice in the right hand panel I'm now on the alerts tab. Under alerts, you will see any that you have in this canvas and this is our new one, our insight card. And below, we can see how this is configured. With alerts, we decide who gets alerted and how and we also decide on a schedule. If I click to manage subscribers, we can see that I'm currently the only subscriber, but I can enter names of colleagues or put email addresses in and I can subscribe other individuals. There's also information here that will let you know how you can connect Slack. And then in the schedule, we can change this. So this is not a regularly updated report. We're looking at monthly data. So perhaps I would like a monthly email on the first at a reasonable time in the morning. So that's the simple way to create alerts, but it's also worth knowing that alerts can be based on a trigger rather than just a regular schedule, And that can be explored in the settings menu of your alert. There's a trigger menu and you can associate any logic you would like with that. So typical use cases might be so you can be alerted when an important query errors or if you suddenly have duplicates in your data, for example. Finally, I want to demonstrate how working with catalogs and count differs to working with database connections. Count metrics are semantically, allows users to work from a governed model of defined metrics, and provides an important self-service opportunity for local users. You'll remember at the start of this tutorial, we looked at our data sources and we worked from BigQuery. This time, I'm going to click on our catalog. Here, I can explore this in the same way and see the views and the columns. Some of these names might be familiar, and that's because this catalog was actually created based on the data that we have just been working with. Let's add this to the canvas, and, again, we will start in the same way with add to canvas. But this time you'll see that it's not a SQL cell that's been added. It's a visual cell. And that is because the main difference working with a catalog is that catalogs can only be connected to low code cells and to control cells. So let's go into our design panel again. Maybe we can just start creating the same chart. This time, in our source, we see our catalog. This is the dataset within the catalog. So we'll start with date again. It looks a bit different because we can see all the defined aggregations in this panel. We are going to go with month and number of employees. Again, we can see more options for defined aggregates, And we added industry in to color. So this looks a little bit different and you'll remember when I was doing the SQL cells that I put a filter from the first of January twenty twenty four onwards, and we can still do that in a local environment. I'm going to bring the date into this filter, and here I'm going to say that it is on or after, and I'm just going to navigate to the first of January. The other thing we were able to do previously was add a control cell, and we can still do that in a liquid environment by clicking on a multiple select. This time, I'm just going to select my catalog as the source and choose industry again. Here, we can see the same type of drop down, but how do we actually connect this? And, again, we can still do that in the local environment. So under filter, I'm going to pull industry, but then at the bottom, I'm going to ask it to connect a control, and then I'm gonna select my control. So now I have a control that is still connecting to my chart. The other thing we can do when we're working with sales that have been created from catalogs is we have a feature called explore from. So we would use this if we were looking at Canvas or a report and we wanted to know more about a particular visual. If I click on explore, we're taken to our explore view, which is a very restricted environment. I cannot pan around this canvas. It is just a single cell view. And this is the visual that I was looking at. And here I can play around with what I would like to see. Maybe I want a different type of drill down, for example. Perhaps I just want to turn this into a table view and see what the underlying data is. Maybe when I have that, I would like to export that CSV. I can also save my explored view as a new canvas, or I can simply go back to the canvas after I've done some exploration and find out what I've wanted what I want to know, and this chart has not been affected. If you're interested in finding out how you can define metrics and create catalogs and count, please do read the documentation or view the video tutorial on that. But I hope this has given you some exposure to how an end user can use catalogs and how it can also be helpful for analysts to use these predefined metrics, even if you have the knowledge to be able to do all the SQL work as well. There are also additional videos and documentation relating to all the features that I've covered in this video. I would also encourage you if you need any support or inspiration to come to the icon in the bottom right hand corner of your screen. Within here, you can send us a message if you would like to chat to us. You can also see links if you would like to have a look at our documentation, if you would like to join our Slack community channel where you can have discussions about work you're doing or post questions or feature requests. And there's also a link to see some example canvases on our website, which can be a really nice way of getting inspiration for your next kind of project. Thank you.