It’s very easy to get a data model set up in AtScale, working with data in Google BigQuery. Learn how to use the AtScale semantic layer with Google BigQuery by using this step-by-step guide. You’ll be up and running in just a few minutes!
Step 1. Log into your web-based AtScale user interface, and head to your design center.
You can get stuck in building your data model straight away.
Step 2. You can leverage an existing data model to get the process started.
For instance, you can open up a data project you created before, then simply add a new cube to get the ball rolling.
Once you’ve named and saved your new cube, click on it to open it up. You’ll now see the main canvas where you’ll be working on your data model.
Step 3. Select your data to start building your dimensional model.
This is surprisingly easy to do in AtScale. All you need to do is to drag and drop the dimensions and measures you need from your Tools menu.
For instance, here we’re opening up the Library, to grab the fact table. We can just drag it onto our canvas to indicate that this is the table that we want to use in our data model in AtScale.
Step 4. Add the dimensions.
Again, this is just a drag and drop process. Select the dimensions you want from your library in AtScale and drop them onto your canvas.
Here, we’re adding the date dimension. In this case, it’s a dimension that we’ve already built (a “conform dimension”) which we’re literally just dragging and dropping out of the Library onto the canvas. And of course, you can build and share these dimensions as templates across your entire user community.
Step 5. Add the measures you want to report on.
To do this, just go to the menu on the right of the canvas and select the Measures tab.
To add your measures, just drag and drop them from your data set into the Measures box:
You’ll get an auto-prompt to create your measure – just type in the name you want to give the measure, and then hit the Save button.
You can set how you want the measure to be aggregated by scrolling down in the Measure editing box to Aggregation Handling. For instance, here we’ll set the aggregation type to Average:
Step 6. Check how your model is coming along with the Preview tool.
Just like that, you’ve already got a usable data model in AtScale. If you want to see how your work is going, you can click the Preview tool in the menu on the right:
This will let you check what you’ll see once you connect the AtScale data model to your BI tool. You can open up the Dimensions and Measures tabs to check that all the data you want to analyze has been included correctly in your model.
Step 7. Now you’re ready to publish – and start querying!
To start using your data model (and share it with your community of data users) you’ll need to publish it. Head to the Projects/Overview tab, and hit the Publish button.
Once the version has been published, every data user will now be able to access the new and improved data model you’ve built.
Step 8. To start working with the data model, choose which BI tool you’d like to connect.
AtScale data models work with Power BI, Excel, Tableau, Looker, Jupyter, and AutoML.
When you select the tool to connect with, you’ll be able to download the data model in the relevant format and open it up in your preferred BI tool.
It’s worth noting here that your data will remain secure right down to the row level, including when users are running queries with the data model.
Step 9. You’re ready to start running queries on your data model in your BI tool of choice.
For instance, we can just drag and drop one of the measures we built in our data model into the Rows field in our BI tool (here we’re using Tableau), and immediately see a visualization of the data.
In other words, we’ve literally taken minutes to go from modeling the data to producing business-ready, actionable insights in our BI tool.
Step 10. To keep track of what the data model is doing, hop back into the AtScale design center.
In the Queries tab, you can watch the queries coming in from the BI tool to AtScale. AtScale will take in the query (in whatever language it was created in your BI platform) and rewrite it into the correct dialect used by the database.
If the query language changes, no problem – AtScale will be able to interpret it into the appropriate terms to run the query against your data warehouse. In a matter of seconds, the information will head back out of the database and into your BI platform–so your business users can actually make use of your data.
Step 11. AtScale gets even faster over time.
The AtScale system learns over time. For instance, if you take a look at your queries, you might see something like this:
If you see a query with “No inbound query” in the Query description, it’s usually because AtScale has spotted an opportunity to optimize your query speed. It will do this by building a data aggregate table. Then, the next time the same query is run, AtScale will use the data aggregate it built to dramatically accelerate query speed.
Step 12. Sit back and relax – you finally have a single source of truth.
Without AtScale’s ability to aggregate the data and eliminate joins, you wouldn’t be able to run this kind of query directly from Tableau to BigQuery. Instead, you’d have to pull out a data extract (a TDE file if you’re working in Tableau) or data import to use for queries. With AtScale, there’s no need to pull the data out of BigQuery to be able to run queries at the pace your business users need.
With AtScale, you aren’t modifying the physical data structures. Instead, you are virtually modifying and working the data set and leaving the data itself alone. So there’s no need to go back to the original data and change something in the model (or wait until your database administrator has time to do it for you). If you need, say, a new column in the data model, you can simply add it virtually, without tampering with the original dataset.
As a result, every user (assuming they have the right permissions) will get the same view and the same consistently correct answers from the data, no matter which BI platform they’re using. Using a semantic layer means that you finally have that single source of truth that’s been eluding businesses for years!
AtScale and Google BigQuery make a powerful team
AtScale enables you to build a semantic layer that will radically simplify and accelerate business intelligence and data science programs on Google BigQuery. With the AtScale solution in place, you can create, distribute and query virtual models on large volumes of data, while maintaining a well-governed and secure data warehouse.
To see AtScale in action, schedule a live demo today!
