Video Tutorial

Setup Instructions

In this guide, we will explain how you can easily export data from your Maximizer CRM account and import it into your Power BI application with the help of Maximizer Octopus API. By following these steps, you can set up an integration between Maximizer CRM and Power BI and start creating and sharing valuable insights and reports with your team. 

It is important to note that we will use three main tools.

1. Maximizer's Octopus API: You can know more about it in our documentation: https://developer.maximizerc.om/reference/octopus-api-getting-started
   
   
2. Power BI: You need to have a Power BI account as well to be able to set up the integration. (If you don't have a Power BI account, you can go to https://powerbi.microsoft.com/ and sign up for a free account.
   
   
3. Postman: If you want to know more about how to set up your personal account in Postman, please follow the documentation: https://learning.postman.com/docs/introduction/overview/

Obtain your PAT

To use our API, you will need to obtain your PAT. For more information on how to do this, please visit our documentation: https://developer.maximizer.com/docs/personal-access-token

Once you get your PAT token. You can go to our Postman collection and fork the Octopus: https://www.postman.com/maximizercrm/workspace/maximizer-api/collection/26999830-8e109754-10bd-477c-a4e2-e25f0a41c6bc

In the Postman "Environments," you can find three examples of environments for this collection. For this example, we are going to use the option #1:

Octopus API (cloud, api.maximizer.com) - example with PAT token.

Then, you can try out some examples using your forked Octopus API collection.

Connecting with Power BI

As we are using Octopus API as our data source, we need to be aware of some essential points:

• We are using a POST method to read data.
• We need the authorization token (PAT) to be included in the request.
• Power BI, by default, uses the GET method when using the Web data source. We will need to use Power Query in Power BI to use Octopus API. You can read more details about it in their documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-query-overview

Creating the Power Query

1. Open your Power BI desktop app. It should be the app, as we need to connect to a data source. The web app of Power BI doesn't allow external data source connections. 
2. Click on the Get Data icon on the top menu.
   
3. Then choose the option “Blank query”.
   
4. A pop-up window will open once you click on the blank query option. This is the Power Query Editor main menu. Power BI automatically generates a Query on the left menu for you to right-click the generated query (in the image, it was named Query 1). Then click on the "Advanced Editor" option.  
   
5. You will see another pop-up window, the "Advanced Editor" window, where we will create our query to retrieve data from Maximizer's Octopus API.
   
   • Note: If you want to know more about Power Query, please visit the documentation site: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-query-overview
     
     
6. Now, it is time to fill our main fields. We will use three main variables in our query: 
   • URL: This variable will store the Octopus API URL from which we will retrieve data. We will use the available collections from our public Postman collections: https://www.postman.com/maximizercrm/workspace/maximizer-api/collection/26999830-8e109754-10bd-477c-a4e2-e25f0a41c6bc 
     
     
   • Body: This will include the body from the examples in the Postman collections. 
     
     
   • Source: We are going to use the Web.Content function. For more information, please visit PowerBI documentation:  
     https://learn.microsoft.com/en-us/powerquery-m/web-contents
     
     
7. For this example, we will use Opportunities as our primary data source. 
   The use case is: As a sales manager, I want to know how much revenue was generated by sales leaders in Fiscal year 2023. 
   
   We will use the following code snippet in the Power Query editor:
   

   let url = "https://api.maximizer.com/octopus/OpportunityRead", 
   body = "{{ 
     opportunity: { 
       criteria: { 
         searchQuery: { 
           ""CreationDate"":{ 
             ""$GE"":""2023-01-01T12:00:00"" 
            } 
          }, 
       }, 
       scope: { 
         fields: { 
           ""CloseDate"": 1, 
           ""Key"": 1, 
           ""CorporateRevenue"": 1, 
           ""Leader"":  
           { 
             uid: 1, 
             displayValue: 1 
           }, 
           abEntry:{ 
             key: 1, 
             displayValue: 1, 
             lastModifyDate: 1 
           }, 
         } 
       }, 
       orderBy:{ 
         fields:[ 
           ""CloseDate"" 
         ] 
       } 
     }, 
     configuration: { 
     drivers: { 
       IOpportunitySearcher: "Maximizer.Model.Access.Sql.OpportunitySearcher" 
     } 
   }}" 
   Source = Json.Document(Web.Contents(url, [Headers = [Authorization = "Bearer <PAT>"] ,Content = Text.ToBinary(body)])) in Source 

    

8. Once you fill out the query, Power BI will automatically run a syntax check to validate if there are any errors. If everything is good, you will see the following message, and you can click on the “Done” button. 
   
9. If your query has been successfully executed, you will see a window like this, which includes the response from the Octopus API:
   
10. Click the "Record" field and then the "List" field.
    
11. It will display a list of records containing our queried data from our Maximizer CRM database. We will need to transform this list into a table. We will do that by clicking on the "To Table" button.
    
12. We need to expand the table of 1x1 to a table of NxM. We expand the single column and split it into columns with our retrieved data fields from the query we built in Postman.
    
    
13. Once we have expanded all columns and denormalized our data, we can click the “Close & Apply” button.