Category: Self-Service BI

Power BI, Power BI Desktop, Power BI Web App

Power BI and Dynamics CRM

Dynamics CRM and Power BI

Microsoft is building lots of cloud based technologies these days and Microsoft Dynamics CRM is not an exception. With a powerful data visualisation tool like Power BI Microsoft took a great step to integrate Dynamics CRM and Power BI which means you can easily connect from Power BI website and Power BI Desktop to a cloud based Microsoft Dynamics CRM instance. In this article I express a step-by-step tutorial to be able to connect to connect to Dynamics CRM from Power BI website as well as Power BI Desktop.

Prerequisites:

  • You can only connect to Dynamics CRM Online (Cloud based Dynamics CRM) from both Power BI Desktop and Power BI website. If you have an older version on-premises Dynamics CRM and you’re willing to create visualisations on Power BI then you need to connect to the CRM database on SQL Server instance just like any other SQL Server databases.
  • You need to have a valid OData URL for a Dynamics CRM Online instance and an administrator must enable the OData endpoint in the CRM site settings. To find the OData endpoint address:
    • After browsing your CRM Online in a browser click “Customizations” from “Settings”

Dynamics CRM OData for Power BI

  • Click “Developer Resources”

Dynamics CRM OData for Power BI 2

  • Scroll down and then you can see OData URL under “Organization Data Service”

Dynamics CRM OData for Power BI 3

  • You should connect to Dynamics CRM Online using the same user account as your Power BI website. So if you have a different Power BI account then unfortunately you need to create a new account in Power BI which is identical to your CRM account.
  • Your browser’s popup blocker should be disabled or you should exclude https://login.microsoftonline.com from your popup blocker.
  • As Microsoft Dynamics CRM is integrated with Power BI you need to have Office 365 subscription. If you don’t have Dynamics CRM Online and Office 365 subscription, but, you’re willing to learn how Power BI and Dynamics CRM Online work together you can sign up for a 30-day trial of Microsoft Dynamics CRM here. There is also a trial guide for Microsoft Dynamics CRM Online and Office 365 here.

    • Continue reading “Power BI and Dynamics CRM” →

    Power BI, Power BI Desktop, Power BI Web App

    Drill Action in Power BI

    Two days ago Microsoft added  some new exciting features to Power BI with the new Power BI Weekly Service Update. The more exciting one is Drill action in Power BI reports. So from now on we’re able to add a number of fields we’d like to have on the reports with a drill path. That means the users can navigate from a level to another. In this short article I show you how to implement a drill action in Power BI. In the “Data Visualisation with Power BI Desktop” I explained how to implement and publish a complete visualisation in Power BI Desktop. In this post I’m going to use the sample we published to the Power BI website as a sample. So if you are not familiar with how to create data visualisations in Power BI Desktop I encourage you to have a look at this.

    Adding Drill Action to Sales by Product Category – Column Chart:

    • Login to your Power BI account
    • Open a report that you want to add a drill action to (as a sample I’m using Adventure Works Reseller Sales which I published previously)
    • Click “Edit Report”Drill Action in Power BI 01Select “Sales by Product Category” chart
    • From “Fields” pane expand “Products”
    • Drag and drop Product to Axis right behind the Product CategoryDrill Action in Power BI 02Now you can see the Drill Down Level icon (image) on the chartDrill Action in Power BI 03 Continue reading “Drill Action in Power BI” →
    On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Web App, Power Query

    Power BI and SSAS Multidimensional

    Update: If you are here to learn how to browse your SSAS Multidimensional model in Power BI please refer to this article. The current article shows you how to refresh data on Power BI service on top of a SSAS Multidimensional instance based on data import scenario.

    It’s been a while that lots of us are waiting for seeing improvements on Power BI and SSAS Multidimensional. The good news is that Microsoft released a new version of Power BI Personal Gateway last week on 3 Sep 2015. One of the new features added to this release is that we can now refresh an on-prem SSAS Multidimensional model (data import scenario) after we published it to Power BI website. But, what data import scenario means? That means we cannot create mashups with data we already have in an existing SSAS Multidimensional database/cube through the SQL Server Analysis Services connector which is available on Power BI website. So we need to connect to a SSAS multidimensional instance through Power BI Desktop and load the cube’s data into the Power BI model. Indeed we will create a relational model on top a multidimensional model from SSAS.

    Then we can create reports and publish them to Power BI website and finally we’ll be able to schedule data refresh on the Power BI website.

    We can also connect to a SSAS Multidimensional instance through Power Pivot AND/OR Power Query from Excel then load the Excel file into Power BI website.

    Note: We can do the same through Power Query, but, we won’t able to setup a data refresh schedule on Power BI website if we didn’t load

    It’s just awesome isn’t it?

    In this post I show you how to implement all the data import scenarios using Power BI Desktop, Power Pivot and Power Query from Excel.

    First of all you need to download the gateway from here. Then you need to uninstall the existing version of Power BI Personal Gateway from your machine and install the new version. The whole gateway installation and process of refreshing an on-prem SSAS database is pretty much the same as what I explained in this post so I leave the installation part to you.  However, I explain the data refresh part again.

    SSAS Multidimensional Data Import Scenario Through Power BI Desktop:

    Get Data

    • Open Power BI Desktop
    • Click Get Data
    • Select “SQL Server Analysis Services Database” from the list and click “Connect”

    Power BI and SSAS Multidimensional 01

    • Enter the SQL Server Analysis Services instance name
    • The database name is optional, but, I put “AdventureWorksDW2012”
    • Click “Select items and get data from Multidimensional or Tabular model”
    • As you can see you can also put your MDX or DAX custom queries, but, we leave it blank in our sample
    • Click OK

    Power BI and SSAS Multidimensional 02

    Continue reading “Power BI and SSAS Multidimensional” →

    Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power Pivot

    Side-by-side Role-Playing Dimensions In Power BI

    Role-playing dimension is one those concepts that is discussed a lot from time to time. I also posted an article about implementing role-playing dimensions in Tabular models.

    To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

    1. Importing role playing dimensions several times into the model
    2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
    3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

    I this post I explain implementation of the third option above. In this scenario you need to create especial calculated measures based on the roles you have in a fact table. One the most common role-playing dimensions is Date dimension. Consider you have to show Internet Sales Amount by Order Date, Due Date and Ship Date in a single chart in your report. In this case, having 3 different date tables won’t help us to achieve the goal.

    New to Power BI? Quickly learn about Data Visualisation in Power BI here.

    Defining new Measures in Power BI Desktop

    Basically, what I’m going to explain in this post is using inactive relationships between FactInternetSales table and the DimDate dimension by adding a new Calculated measure. In this case, we’ll be able to show Sales Amount by different roles, well, dates in this sample in a single chart.

    Continue reading “Side-by-side Role-Playing Dimensions In Power BI” →