How to Integrate Firebase with Power BI using Big Query

What is Firebase?

Firebase, a backend platform used to build Web, Android, and IOS applications, offers real time database, and various APIs, in addition to multiple authentication types and hosting platforms. In this blog, I have tried to give you a basic idea about the Firebase platform and how you can deal with its various components and sub-components.

Firebase effectively powers your application’s backend, including data storage, user authentication, static hosting, etc., all you need to do then is just create that extraordinary user experience. With Firebase, you can build cross-platform native mobile and web apps with Android, iOS, and JavaScript SDKs. You can also conveniently connect Firebase to your app’s existing backend either using server-side libraries or REST APIs.

How does it work?

Firebase Realtime Database is a secure way to access your database directly from the client-side to create wonderful and really collaborative applications. Data is persisted locally and continues to fire real time events even while offline, giving your user a fully responsive experience.

What is even more exciting about Realtime Database is its capability to synchronize local data changes with remote updates when the client was offline that means any conflicts are merged automatically when the device’s connection is restored.

The Realtime Database provides a relatively flexible, expression-based rules-language called Firebase Realtime Database Security Rules. This language essentially defines how your data should be structured and when it can be read from, or who it can be written to. When integrated with Firebase Authentication, you as a developer can easily define who has access to which data, and how they can access it.

The Realtime Database is a NoSQL database and has a variety of optimizations and functionaries compared to a relational database. The Realtime Database API is designed in such a way that it can only allow operations that can be executed quickly. This enables you to effectively build a great Realtime experience that can serve millions of users without ever truly compromising on responsiveness. However, because of this, it is important to think about how users need to access your data and then structure it accordingly.

Components of Firebase

Firebase is a comprehensive app development platform that comes with the following characteristics

Features

• Real-time Database − Firebase supports JSON data wherein all users connected to it will receive live updates after every change.
• Authentication − Anonymous authentication, password or other different social authentications can be used.
• Hosting – Applications can be easily deployed over a secured connection to the Firebase servers.

Firebase Advantages

• Simple, user friendly, and does not require complicated configuration
• Real-time data access where every change will automatically allow connected clients to get updates
• Simple control dashboard
• Multiple useful services to choose from

Firebase Limitations

• Firebase free plan has a limit of 50 Connections and 100 MB of storage

What is Microsoft Power BI?

Power BI is essentially a collection of multiple software services, apps, and connectors that work together to convert your unrelated sources of data into insights that are coherent, visually immersive, and interactive. Your data could be an Excel spreadsheet or a collection of cloud-based, on-premises hybrid data warehouses, with Power BI you can connect your data sources , visualize them, discover what is  important, and share your data insights.

Components of Power BI

Power BI consists of several elements that all work cohesively, starting with these basics components:

• Power BI Desktop (a windows desktop app)
• Power BI service (an online Software as a Service – SaaS)
• Power BI mobile apps (for Windows, Android, and iOS devices)

These three elements-Power BI Desktop, the service, and the mobile apps—are designed to let you create, share, and consume business insights in a way that serves you and your role most effectively. In addition to these three, Power BI also includes two other elements:

• Power BI Report Builder is used to create paginated reports so you can share them in Power BI Service
• Power BI Report Server which is an on-premises report server where you can publish your Power BI reports after creating them in Power BI Desktop

The flow of work in Power BI

Workflow in Power BI commonly begins by connecting with data sources on the Power BI Desktop and building a report. Then publish your report from the Power BI Desktop to the Power BI Server, and share it so other business users on the Power BI service and on mobile devices can view and interact with your report. This workflow is quite common and shows how the three primary Power BI elements complement one another.

What is Big Query?

Without the right infrastructure, storing or even for that matter querying large datasets can be both expensive and time-consuming. Big Query, an enterprise data warehouse solves problems by enabling extremely fast SQL queries using the processing power of Google’s infrastructure. Move your data into Big Query, and let it do what it does best. You can control the access to both the project and your data based on your business needs, this means others can have the ability to view or query your data.

You can access Big Query by using either the Cloud Console or the classic web UI, by using the BQ command-line tool, or even by making calls to the Big Query REST API with the help of a variety of client libraries such as Java, .NET, or Python. Use third-party tools to interact with Big Query, to visualize or load data.

Big Query is fully managed and you do not need to deploy any resources, such as disks and virtual machines to get started.

Enabling Big Query Database using Sandbox

Big Query Sandbox grants you free access to the power of Big Query. However, it is subject to the sandbox’s limits. The sandbox lets you use the Cloud Console without providing your credit card details. You can use it without creating a billing account or enabling billing for your project.

The Cloud Console is the graphical interface that you can use to create and manage Big Query resources and to run SQL queries. See the Cloud Console quick start for a working introduction to the Cloud Console.

Getting started with the sandbox

Any Google Cloud customer can leverage Big Query sandbox including Firebase users. For all Firebase users, the ‘Big Query to Firebase’ in the Firebase Help tab should give the right instructions on how to go about linking the two.

To open the sandbox:

1. Open the Cloud Console
2. Go to the Cloud Console
3. Choose Big Query on the navigation pane. Or open Cloud Console directly by typing this in your browser window : https://console.cloud.google.com/bigquery
4. Accept the terms of service
5. Before you can use the Big Query sandbox, you must create a project. Follow the prompts to create your new project
6. Once your project is created, Cloud Console displays a sandbox banner like this:

7.While you’re using the sandbox, you do not need to create a billing account, and you do not need to attach a billing account to the project.

The Big Query sandbox is subject to the following limits.

• All Big Query Quotas and limits apply.
• You are provided with the same free usage limits as the free tier i.e. an active storage of 10 GB and processed query data of 1 TB per month.
• All datasets commonly have the default table expiration time along with the default partition expiration set to 60 days. Any tables, views, or partitions in partitioned tables therefore, automatically expire after 60 days.
• Sandbox projects do not support:
  • Streaming data
  • Data Manipulation Language (DML) statements
  • The Big Query Data Transfer Service

To overcome the limitations of sandbox, you can upgrade your project and update or remove the expiration time for your resources. After upgrading from the sandbox, Big Query Quotas and limits still apply.

Upgrading from the sandbox

To upgrade from the Big Query sandbox:

• Enable billing for your project
• Update your Big Query resources:
  • Remove or update the dataset’s default table expiration
  • Remove or update the dataset’s default partition expiration
  • Remove or update the expiration time for your tables
  • Remove or update the expiration time for your views
  • Remove or update the expiration time for your table partitions

Integrating Firebase with Power BI using Big Query

Connect to your Google Big Query database on your Power BI Desktop and use the underlying data. This is similar to any other data source found in Power BI Desktop.

Connect to Google Big Query

Here’s how you make the connection to a Google Big Query Database:

• On your Power BI desktop choose the Home ribbon,
• Select Get Data
• Select Database from the categories on the left, and
• Voila! Google Big Query

• Use the Google Big Query window that appears to sign into your Google Big Query account and select Connect

• After you sign in, this window indicates that you have been authenticated

• Once you are successfully connected, a Navigator window will appear and display the data available on the server, from which you can select one or multiple elements to import and use in Power BI Desktop.

Creating visuals in Power BI (Analytics and Crashlytics)

After connecting to Big Query in Power BI, pull data into Power BI and create reports according to need. You are now ready!

 Now that I have taken you through the process, you can try your hands at effectively integrating Firebase with Power BI using Big Query and create reports and analysis a wide range of data.

Write to us if you have questions on Firebase Integration, or if you wish to know more about our services, or if you would like to know how we  helped a leading technology company revamp their analytics capabilities by migrating to  Microsoft Power BI and reducing their IT costs by 45%.