Tips, ideas, and updates from the experts

  • Viva la Data! See you at Gartner BI and Analytics Summit in Vegas

    Are you at the Gartner BI and Analytics Summit? So are we! Join us at these various events throughout the Summit, and follow along at @MSPowerBI to hear the latest news from the event.

    • Microsoft Booth #311 in the Expo Hall: Stop by the booth in the expo hall to chat about any BI and analytics questions with members of our engineering team, see the latest demos of Power BI, as well as Microsoft Azure’s big data and IoT capabilities, and of course win some cool swag.
    • Solution Provider Session with James Phillips, Corporate Vice President of Power BI – Tuesday 3/31, 10:45am in Augustus I: You’ll hear about the latest BI offerings from Microsoft and get a chance to see them in action. 
    #Gartner BI and Analytics Summit

    Mon, 30 Mar 2015 17:03:00 GMT byPower BI Team0 Comment

    Read full post
  • 7 New Updates to the Power BI Designer Preview

    Try what's coming next for Power BI!

    We’re very glad to announce a new update for the Power BI Designer Preview. This month’s update includes new features and improvements across all major areas in the product: connectors, transformations, modeling and reports. We have also addressed lots of bugs and improved performance and stability of the Power BI Designer.

    What’s New in the Power BI Designer

    These update includes the following new features:

    You can see these features in action in the following video, or read below for more details.

     

    Google Analytics Connector

    The first item on the list is a new connector to import data from Google Analytics. We’re adding this connector not only to the Power BI Designer, but soon also to the Power BI Dashboards service. Stay tuned for upcoming service update announcements that will bring this feature.

    In the Power BI Designer, this connector can be found in the “Get Data” dialog, under “Other Sources” category (or “All”).

    After signing in with a Google Analytics account, users will be able to browse analytics data for all sites registered with the account. Users can pick dimensions and measures in the Navigator dialog to construct a single-table output.

    Users can see the table output preview building up as they select items in the Navigator tree.

    Finally, they can click Load to land the entire dataset directly in the Report view, or select Edit to land a small sample of the data in the Query view.

    In the Report view, users can directly start building visualizations for this data. In the Query view, they will be able to apply several data transformations based on preview data that will run against the underlying service, and they can also leverage special operations available only to Multi-Dimensional sources, such as Add or Remove items.

    There are lots of interesting analytics scenarios that open up with this new connector. Think about all other data sources supported in the PBI Designer and how easy it is now to combine data from any of them with Google Analytics data to build Power BI reports.

    Additional Operators for Date filtering in the Query view

    We have added a few additional filter options for Date/Time columns in the Query view:

    -  Is in Year to Date.

    - Today

    - This Week

    - This Month

    - This Quarter

    These options show up under the respective dropdown menu in DateTime Filters.

    Note that all of these filters are dynamically calculated, so for instance, “Year to Date” means between Jan 1st 2015 & March 25th 2015 today, but it will mean a different Year and Date when we refresh our query or report in the future.

    Automatic Relationship Detection

    In this update, we are adding automatic relationship detection for tables loaded in the model. We’re applying this detection in the following cases:

    - Initial load of the table to the model.

    - Refresh

    - Auto-Detect button.

    This is a very powerful feature, which is now also enabled automatically and will allow users to correlate data from different tables and build rich visualizations without having to worry about manually defining these relationships. This relationship detection works for all data sources supported in the Power BI Designer that do not have an underlying schema (such as Text, CSV, Web, HDFS, etc.).

    You can learn more about how to create and manage relationships in the Designer by reading this Help article.

    Enhanced “Add Relationship” dialog…

    The “Add Relationship” or “Edit Relationship” dialog has been improved to include inline previews of the tables/columns being selected.

    Within the “Manage Relationships” dialog, we can click “Add Relationship”… The dialog now gives us two table/column selectors with inline previews of data. This enables us to easily identify which tables/columns to use for the new relationship.

    Reorder Report Pages

    Last but certainly not least… We’re adding the ability to reorder pages in the Report by drag & dropping within the Report Pages pane… This will definitely make it much easier for you to iterate on the content of your reports and move it around as needed.

    Performance Improvements

    We have improved performance for loading queries that do not contain any filters or transformations by ~40-50%. You should see such queries take half of the time to load or less, compared to last month’s Designer bits.

    Bug Fixes and Quality Improvements

    As with every update, we’ve also fixed lots of bugs and issues reported by many of you via “Send a Frown”. We encourage you to continue sending us feedback about the product and reporting any issues that you may find while working with the Power BI Designer.

    We hope that you enjoy this update. Stay tuned for more updates coming soon… 

    #Power BI#Business Intelligence#data visualization#model#power BI Designer#Microsoft#Google Analytics#Relationship

    Thu, 26 Mar 2015 14:30:00 GMT byMiguel.Llopis9 Comments

    Read full post
  • Better Insights into your Zendesk customer support with Power BI

    An important part of good customer service is ensuring that your customer needs are being met quickly and efficiently. This means not only hearing from your customers, but analyzing how your customers and your team are interacting. This is where Power BI and Zendesk come in. Zendesk allows companies to build better relationships with their customers. Power BI allows users to import data a variety of sources to quickly gain insights into their data.

    When connecting to Zendesk, Power BI gives users an even easier way to explore and monitor their Zendesk data with an out of box content pack. This content pack not only brings in the data, but also offers a dashboard and set of reports to get you started.

    In this post we’ll cover how to get better insights to your Zendesk data using Power BI. For additional details on how to connect and get started, jump to the Connecting to Zendesk section below.

    Exploring your Zendesk data

    Power BI offers an out of box content pack that connects to Zendesk and brings in a variety of important data for you to monitor.

    As soon as you connect to your data, you'll see information about your tickets, agents, organizations and more. For example, at a glance you can see the number of Tickets created, solved and closed over the last 7 and 30 days. Your dashboard can be customized however you like, ensuring you have the data that is most important to you.

    There are even more insights are behind each tile, in the 5 page report that comes with the dashboard. These pages include insights about Tickets, Agent Performance, Recent Activity, Activity by Agent/Group, and Ticket Activity.

    Selecting the top left tile brings you to the Ticket Summary report page - showing a breakdown of tickets by status, group, channel and more. More details about interacting with reports are available here.

    Selecting a visual will bring up options to change the visual type, to pin the visual or to remove it from the report. Pinning a visual will add it to your dashboard, and is a quick way to customize your dashboard with information that's important to you.

    Select a value on the report to cross filter the other visuals. For example, selecting "open" in the top left, will filter the page to all the open tickets. You can also use the filter pane on the right hand side, to change the range of tickets you see based on when they were created.

    Use the navigation at the bottom to browse the other pages of the report, including Agent Performance, Recent Activity, Activity by Agent and Group, Ticket Activity.

    You can also chose to edit the report, which allows you to customize your reports and dashboards based on the underlying dataset. Select Edit Report in the top left corner. The view will switch to show you the field list, allowing you to access any of values and calculations included in this out of box data set – such as additional information about Users, Tickets etc. and customized calculations like Average Reply Time or Average Resolution Time. More details about editing reports can be found here.

    Back on the dashboard, you can also ask questions about your data using the question box. More details about Q&A can be found here.

    You can ask specific questions about your Zendesk data, such as “tickets by status” or one specific to your agents, such as “number of open tickets for Josh”. The results of the questions can also be pinned to your dashboard.

    Connecting to Zendesk

    To access Zendesk data in Power BI, you’ll need to provide your Zendesk company URL and your credentials. Get started by navigating to www.powerbi.com and sign in with your work account. If you don't already have an account, it's quick and free to sign up!

    Once in Power BI, you can connect to Zendesk through the Get Data link in the top right.

    From the list of sources you can search for and select Zendesk. The landing page describes some of the details of the connector, to continue click Connect.

    You've now started the import flow to view your Zendesk data in Power BI!

    Next, enter your Zendesk company URL - this will be in the form "https://company.zendesk.com". You can find more details about what to specify at https://www.zendesk.com/login

    When prompted, enter your Zendesk credentials.  If you have pop-ups allowed you may automatically be prompted. If not, select oAuth 2 as the Authentication Mechanism and click Sign In.

    Follow the Zendesk authentication flow. Note that these credentials must be an Admin for your account.  (If you are already signed in to Zendesk in your browser, you may not be prompted for credentials.) After signing in, click Allow to allow Power BI to access your Zendesk data.

    Click Connect to begin the import process. Once completed, a new dashboard, report and model will appear in the Navigation Pane. Your data is now in Power BI for you to monitor and explore!

     

    #Power BI#Business Intelligence#data visualization#Zendesk#Connector#Customer Interaction#Customer Support#Customer Service#Content Pack

    Tue, 24 Mar 2015 15:30:00 GMT byPower BI Team4 Comments

    Read full post
  • Monitor and analyze your Marketo data with Power BI

    With Power BI our customers can keep a pulse on their business by bringing all their data together and monitoring it through a single pane of glass. Live Power BI dashboards and reports show visualizations and KPIs from data that reside both on-premises and in the cloud, providing a consolidated view across the business regardless of where the data lives. With Power BI content packs for popular services such as Marketo and others, you can now connect and view your data in Power BI with pre-built dashboards and reports, in a matter of minutes.

    The Marketo content pack for Power BI gives you insights from the valuable data building up in your Marketo account.  In Power BI, you can create a dashboard that includes data from all the services you use to get your job done.  Then you can easily share the dashboard and the insights it contains with everyone on your team. 

    In this blog we wanted to show you the insights you can get by connecting your Marketo account to Power BI. We have a Marketo account that contains details of multiple email campaigns and the leads they generated.  After logging into Power BI, we were able to create the following dashboard with just a few clicks. It includes details like the number of emails sent this quarter, the percentage of emails that were opened, a map showing where your message is resonating, and the sources yielding the most leads. 

    We can ask questions of the data using business terms using Power BI’s Q/A technology – its natural language search right on top of the data in my Marketo account. Below you can see we typed “how many leads do we have by state” and that immediately provided the answer. 

     

    The dashboard also comes with a detail report.   By clicking any of the charts in the dashboard, we can drill through to a detail report. 

    The default report has useful insights you can use to analyze your lead generation.  Below we walk you through the default report.  Before we do, it’s important to know that you can customize the report and dashboard to meet your unique needs. We don’t go into that here, but we’d recommend giving it a try yourself (see how).  This is especially useful since everyone’s usage and needs depend on their goals, so customizability is a key benefit of using the Marketo content pack for Power BI.  When you consider that you can mashup data on a single dashboard from multiple other sources like Google Analytics (coming soon!), it makes it all the more useful.

    The report has a lot of good insights.  This page shows you if your emails are targeting the right people by job title, industry, company size, geography, and see how much potential revenue could come from the leads you have.

     

    There’s a page that drills into lead acquisition giving insights on which lead sources are performing the best, the breakdown of leads by lead status and allows you to slice the metrics by industry, company size, revenue and country. It’s a good reference if you’re actively doing customer development.

    The connector also has a page focused on how your marketing programs are doing.  Especially useful is the top programs by acquisition percentage which helps you compare the programs against each other to ensure you’re focusing on programs that work.  You can also see the top 10 programs by success rate, top 10 acquisition programs and the most active programs.

    The email engagement page lets you see which emails are effective and which could be improved.  You see the number of emails sent, open rate, link click rate, unsubscribe rate, and delivery rate for the emails you send.  You also get breakdowns by device and user.

    The last page helps you understand when users are engaging with the emails you send. You might use this to adjust the time your mails are sent or to ensure there are enough customer support staff to handle high traffic periods with speedy answers giving you an advantage over your competitors and building positive sentiment.

    Setting it up

    To set it up you need to login to your Marketo account and get three pieces of information: the Endpoint, Client ID, and Client Secret.  If this is your first time through, we have a nice article on our support site that steps you through it.  You can find it here: 

    http://support.powerbi.com/knowledgebase/articles/462795-marketo

    If you run into issues or need help getting going, please do not hesitate to reach out to us. You can contact support either by clicking the “?” when you’re signed in to your Power BI account or from the “Contact Support” link on our support site. 

    http://support.powerbi.com

    #Power BI#Business Intelligence#data visualization#Marketo#Connector#Marketing#Marketing Automation

    Thu, 19 Mar 2015 17:00:00 GMT byPower BI Team3 Comments

    Read full post
  • MySkills4Afrika and Power BI

    by Anand Mariappan, Senior Software Engineer for Power BI

    I am a Senior Software Engineer in the Power BI team working on the dashboard experience. Recently, I had the opportunity to volunteer in Uganda between Jan 19th to Feb 3rd through the MySkills4Afrika Program by teaching startups to use Power BI. Below is a summary about my experience. For more details checkout my blog.

    The MySkills4Afrika program lets all Microsoft employees volunteer their time and skills to help build a successful future for Africa. Since the program launched over 250 Microsoft employees have completed projects ranging 1-2 week in-person in Africa. Emerging markets in Africa have been identified as game changing opportunities for organizations Microsoft is making this investment through the Microsoft4Afrika initiative, of which MySkills4Afrika is a key component.

    In January Microsoft introduced a preview of the new Power BI preview business analytics service. Business users can sign up in seconds and begin connecting with their data in minutes.

    During my two weeks in Africa, I spent hands on time with MMindzsFIT Uganda and five early stage startups at the ICT Center of Innovation and Entrepreneurship to show how easy it was to use Power BI using Power Query to import data into Excel, Power Pivot to define relationships between the imported data and creating live dashboards within minutes from the Excel file.

    On the last day I gave a talk to everyone at the ICT startup hub on using Power BI for analytics and best practices for building a MVP. These were small-medium businesses including students, developers and sales people. 

    Here is a sample dashboard that I created during one of the training sessions, to show how it would be to use Power BI. Everyone loved the Power BI Q&A feature and were super impressed with how easy it was to create informative visuals.

    The most frequent asks from the various startups were:

    • Accessing new Power BI experience outside USA
    • Android app for consuming the dashboards
    • Embedding dashboards
    • Real time alerting

    Some of the quotes from the startups after I showed them the new Power BI preview experience were:

    • "Power BI Q&A is magical"
    • "Microsoft is going to make real money out of this dashboard experience"
    • "I am really blown away now"
    • "This transition from Power BI Office 365 to Power BI dashboarding is similar to the transition from PowerPoint to Prezi. The new experience is amazing"

    Overall it was a great experience to see happy Power BI customers and to see the product we are building solve for the right user problems.

     

    #Power BI#Business Intelligence#dashboard#Microsoft#data visualizatio#Startup#Africa#MySkills4Afrika

    Thu, 19 Mar 2015 15:00:00 GMT byPower BI Team1 Comment

    Read full post
  • An Invitation from Thomas LaRock to Attend the PASS Business Analytics Conference 2015

    This is a guest blog post from Thomas LaRock, the President of PASS (Professional Association for SQL Server)

    It’s no secret that the role of data in the IT industry, in business, and in the world at large is changing at a rapid pace. As technology continues to become a more integrated and integral part of our lives the value of data continues to rise.

    At PASS we have a 16-year history of empowering IT professionals who use Microsoft data technologies. The SQL Server community is the largest and most engaged group of data pros in the world. PASS volunteers and the PASS Board of Directors work together to help the PASS community succeed in connecting, sharing, and learning from one another. A big part of that effort is keeping an eye on the future of the data profession.

    What we see is that data analytics is the next frontier for professionals passionate about data. The growth of Big (and Little) Data, the advent of cloud computing, and advances in machine learning are all areas that present challenges for our community. Data analysts, data scientists, and line-of-business managers are in high demand as organizations realize the potential of collecting and understanding the wealth of information that is available from a variety of sources.

    PASS is dedicated to helping our members harness the technologies, skills, and networks that are the foundation of solid and successful careers. We believe that keeping up with industry advances is a vital skill for all data professionals. Setting and achieving new goals as well as learning new ways of working with data is a must.

    Whether you’re coming from a background in SQL Server, business intelligence, or social media there are specific cornerstones of turning all this data into something that can benefit your organization. We call this the “analyst’s journey.”

    One such cornerstone is data discovery and integration. We want our members to be aware of the latest technologies in collecting, modeling, and preparing data for analysis. Next is data analysis and interpretation. We want to help our members understand the techniques and tools that enable sophisticated analysis, prediction, and optimization. Then there’s visualization: the creative side of things, where we get into report and dashboard design.

    As with any career another key skillset is communication. The people who analyze and work with data are in the best position to help gain executive buy-in for data-driven business decisions. For years PASS has been the leader in helping data professionals improve their communication and soft skills.

    One way in which we’re reaching out to those who want to learn more about analytics is the PASS Business Analytics Conference. This premiere event brings together a stellar lineup of business and analytics speakers including our keynote speakers Carlo Ratti and BI Brainz founder Mico Yuk. We have created a series of webinars and a Meet the Expert interview series to give people an idea of what the conference will offer. We also have replays from last year’s conference, and we have hours of training available through our Virtual Chapters.

    We’re excited about data and analytics and we’re hearing from more and more SQL Server pros who share that excitement.

    It’s a wonderful time to be a data professional.

    See you in Santa Clara!

    Thomas LaRock

    President, PASS

    PASSBAConference.com

    #Power BI#Business Intelligence#Excel#PASS#data visualization#training#Business Analytics#Conference

    Wed, 18 Mar 2015 16:00:00 GMT byPower BI Team0 Comment

    Read full post
  • Power BI Preview now available worldwide

    by James Phillips, Corporate Vice President, Data Experiences

    This morning at Microsoft’s Convergence Conference, we announced new products and services that will enable organizations to create a data culture in all parts of their organizations. Adopting a data culture starts with making information easily accessible to all - our Power BI offering is helping to accelerate that adoption by changing the way people access, use, manage and share data.

    First unveiled in January, the new and improved Power BI is a cloud-based (software-as-a-service) business analytics service for non-technical business users. With just a browser or a Power BI mobile app, customers can visualize and analyze their data with easy-to-use, accessible tools and innovative technology. Today we are excited to announce we are making the Power BI preview available globally to 140 markets. In addition, some of the industry’s most popular data sources -- including Microsoft Dynamics Marketing, Google Analytics, Zuora, Acumatica, and Twilio -- will be available in the Power BI preview soon.

    With Power BI our customers can keep a pulse on their business by bringing all their data together and monitor it through a single pane of glass. Live Power BI dashboards and reports show visualizations and KPIs from data that reside both on-premises and in the cloud, providing a consolidated view across the business regardless of where the data lives.

    Power BI enables our customers to get started with data in a matter of minutes by making it possible for business users to directly connect with their data without requiring technical or analyst support. Our customers can access popular services such as GitHub, Marketo, Microsoft Dynamics CRM, Salesforce, SendGrid and Zendesk and we will be adding solutions for Google Analytics, Microsoft Dynamics Marketing, Acumatica, Zuora and Twilio. These solutions enable marketers with subscriptions to these services to connect and view their data from pre-built Power BI dashboards and reports that are updated continuously and automatically.

    Today, anyone with a business email account can experience the preview of the new Power BI for free. Visit www.powerbi.com to find out more.

    #Power BI#Business Intelligence#data visualization#Salesforce#Marketo#Zendesk#Analysis Services#Github#Microsoft Dynamics Marketing#Acumatica#Google Analytics#Twilio#Zuora

    Mon, 16 Mar 2015 12:30:00 GMT byPower BI Team6 Comments

    Read full post
  • Create a Scalable Data Model for Enhanced BI

    Managing millions of rows of data from multiple data sources requires a great deal of planning. Making this information readily accessible to BI users is just one (extremely important) goal. The information itself has to be worth obtaining—so you need to design your model to build and manage connections between data from a variety of sources, as well as relationships between multiple tables. Your data model is the foundation for all your data analysis.

    When creating your data model, you need to consider key goals:

    • The impact on speed of processing
    • How you can optimize memory usage and performance
    • Scalability when handling growing volumes of data and requests

    That’s why we’re introducing a deep-dive Microsoft Ignite session on Scalable BI and Advanced Modeling with Microsoft SQL Server Analysis Services and PowerPivot. Created for enterprise developers, this session shows you how to define a scalable data model in Microsoft SQL Server Analysis Services or Power Pivot to meet these essential goals. And we’re taking it even further: the right definition of your data model will be especially useful if you want to take advantage of the self-service navigation, discovery, and prediction capabilities of Microsoft Power BI to share all of this helpful information.

    We’ll discuss how to use common design patterns to solve issues, such as handling relationships at granularities other than primary key in a table. And you’ll get some suggestions about hardware selection, which is particularly important for maintaining the performance of large models.

    This session will be of most interest to BI developers, particularly those beginning to plan or implement an analytics solution using a tabular model.

    If you haven’t already signed up for Ignite—the gathering of some of the best minds in the business—you can register here, or check out more sessions here.

    #Microsoft SQL Server Analysis Services#PowerPivot#Scalable BI#Microsoft Ignite

    Thu, 12 Mar 2015 15:00:00 GMT byPower BI Team2 Comments

    Read full post
  • Power BI Analysis Services Connector Deep Dive

    One of the most exciting features of the new Power BI Preview is the ability to connect live to Analysis Services tabular models that live on-premise though the SSAS connector. In this article we will show you step by step how to securely connect an on-premises Analysis Services Tabular mode instance to use it as a data source in the Power BI Preview.  You can alternatively use a virtual environment in Azure (IaaS).  The process is similar.

    Unlike caching the data in the Power BI service, with the new Analysis Services Connector you can connect from Power BI to your on-premises Analysis Services Tabular model & build dashboards, reports. The DAX queries issued by Power BI are sent to the Analysis Services Connector which in turn sends it to the tabular model. The DAX query is executed according to the role based permissions defined in the model. The connector will interactively query the on-premises cube to fetch the data using your Active Directory credentials.  With this new option, you can continue to manage and secure your data on-premises, removing the need to have data reside in the cloud.

    Power BI customers can now roll out self-service BI with enhanced user level data security Analysis Services provides. The Power BI user name gets passed from your Power BI site through to your on-premises Analysis Services. Analysis Services then resolves the user name to an authorized user via Azure Active Directory and applies appropriate role-based security to restrict data access.

    Secure data transfer between Power BI and the Analysis Services Connector happens through a Service Bus which uses a secure SSL channel. Service Bus in turn uses a shared secret mechanism to create a secure channel between the Power BI site and your on-premises data source through an outbound port.  It does not require an inbound port to be opened on the on-premises firewall.

    In this post

    Prerequisites

    Setting Up the Analysis Services Connector

    Things to be aware of when configuring the Analysis Services Connector

    Live Connecting to Analysis Service from Power BI Preview

    Using an existing Excel Power View workbook

    So how does this all work?

    Additional FAQs

    Prerequisites

    Currently this feature is only available Power BI accounts created in the U.S. Additional locations around the world will be added in the future.

    To configure the new Analysis Services Connector, you will need Analysis Services server administrator permissions.  Once configured, users will need to have at least Read permissions.  

    Note .NET Framework 4.5.1 or higher also needs to be installed.  The current version of Power BI Data Management Gateway cannot be installed on the same machine as the new Analysis Services Connector.  If you already have the Data Management Gateway installed, uninstall it before installing the Analysis Services Connector, or install the new connector on different computer.

    We currently only support connecting to Tabular models.

    You might need to configure Azure Active Directory DirSync. See the support article at http://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana. For organizations that use Office 365, this is often already set up. If you do not have a federated domain to Azure, Greg Galloway shared a tip on setting up a demo Azure domain that you can refer to for setting up a demo or test environment.

    Setting Up the Analysis Services Connector

    Now the fun begins!  Log into your Power BI Preview account and download the latest Analysis Services Connector from the top right DOWNLOAD menu under the gear icon.

    You can also download the Analysis Services Connector from here - http://www.microsoft.com/en-us/download/details.aspx?id=45333

     

    Double-click on the PowerBIASConnector.exe file or click Run on the downloaded file pop up me to start running the installation wizard.  After installing, click Launch to open the Power BI Analysis Services Connector Wizard. The wizard will guide you through configuring a connector to your server instance.

     

    Once Set Up completes, click next to continue to log into Power BI. 

     

    Next you will enter the Analysis Services Server name & the credentials that you want to use for Power BI connectivity to your Analysis Services server.

    Enter the Windows user name in the form of username@company.com or domain/username

    Keep in mind:

    1. You need to be an admin on the Analysis Services Server to wish to connect to
    2. If this password expires, you will need to update it in the connector for connectivity to remain functional. 

     

    Next: enter the friendly name – the name of the connection that shows up in the Power BI site. A nice friendly name makes it easier for users to find the server they want to connect to.

    Description – provide a nice description for the server

    Friendly error message – when Power BI cannot connect to the Connector due to the Connector going offline or not accessible, this error message will be shown to the users in Power BI

     

    If the connection has been successfully configured, you should see “Success! Your Power BI Analysis Services Connector (Preview) is configured” shown. 

     

    Also the Analysis Services service and Analysis Services Connector service needs to remain running. The Analysis Services Connector service has the label Data Management Gateway Host Services in your Services panel.

    You can now close the Analysis Services Connector window and give it a whirl from the Power BI Preview site. 

    Things to be aware of when configuring the Analysis Services Connector

    1. The Analysis Services server must be domain joined. We rely on Active Directory to translate the Power BI username to the on-premises user name.
    2. The Analysis Services Server and the Analysis Services Connector should be joined to the same domain
    3. It is not necessary to install the Analysis Services Connector on the same computer as the Analysis Services Server, but from a performance standpoint it is good to do so. This will avoid an extra hop from the Connector to the Analysis Services Server.

    Live Connecting to Analysis Service from Power BI Preview

    In the Power BI Preview site, click Get Data and navigate to the Analysis Services connection type.  Click on the Connect button. 

     

    A lists of available Analysis Services data sources will be displayed.  This list shows the friendly name & description you defined in the Connector.

    Choose your newly configured server and the available cubes will be shown.  Click on the cube you want to use. 

     

    Your Analysis Services connection should now be displayed under the Datasets list.   To use it create reports, click on the three dots (…) then click EXPLORE on the shown menu.  Or click on the name of the dataset to get into report authoring view.

    Now you should see the report authoring user interface for your self-service BI community to create reports from your blessed Analysis Services single version of the truth.  Simply drag and drop dimensions and measures onto the report canvas.  To add a chart to your dashboard, simply choose the Pin icon next to the visual.

    Using an existing Excel Power View workbook

    You can also publish Excel 2013 Power View reports to the Power BI Preview site that use the exact same Analysis Services Tabular connection and they just work.  You do need to make sure that the Analysis Services connection string in your Excel file matches the Analysis Services connection that you published to your Power BI Preview site when you configured direct connectivity. 

    To set up a connection in Excel 2013 to an Analysis Services Tabular model, use the Data tab in Excel.  Once connected, then create your Power View reports. 

     

    To publish your Excel 2013 Power View report to the Power BI Preview site, you will upload the Excel 2013 file.  On the Power BI Preview site, click on Get Data and then choose the option to upload an Excel file. 

     

    After your Excel file is uploaded, the Power BI site service will automatically match your defined Analysis Services connection embedded in the Excel 2013 Power View report with the already published Analysis Services data source connection on the Power BI Preview site for live interactive querying.  How cool is that?

    So how does this all work?

    The whole story relies on two key things:

    • The EffectiveUserName connection string property supported in Analysis Services Server
    • A domain joined Analysis Services Server can get from Active Directory the on-premises user name equivalent  of the Power BI user name in the cloud

     

    1. When user interacts with a report connected to on-premises Analysis Services server, a DAX query is generated & is sent to the connector along with the effectiveusername of the user.

    A quick inspection of the SQL profiler show this:

    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

              <CATALOG_NAME>AdventureWorks_prod</CATALOG_NAME>

              <VERSION>2.0</VERSION>

            </RestrictionList>

     

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

              <Catalog>AdventureWorks_prod</Catalog>

              <Cube>InternetSales</Cube>

              <EffectiveUserName>anna@contoso.com</EffectiveUserName>

              <SspropInitAppName>PowerBI</SspropInitAppName>

              <LocaleIdentifier>1033</LocaleIdentifier>

              <ClientProcessID>3408</ClientProcessID>

              <Content>SchemaData</Content>

              <Format>Tabular</Format>

              <DbpropMsmdActivityID>381f4fdf-48cf-eb13-a5bd-f585cd05dc5d</DbpropMsmdActivityID>

              <DbpropMsmdRequestID>ed28257d-b516-427e-a299-be5d60f14427</DbpropMsmdRequestID>

            </PropertyList>

    Notice the EffectiveUserName property?

    1. The Connector sends the DAX query along with effectiveusername property to the Analysis Services Server.
    2. The Analysis Services Server queries Active Directory (AD) to get the on-premises equivalent of ‘anna@contoso.com’
    3. Since anna@contoso.com is also the on-premises domain username AD can resolve the email ‘anna@contoso.com’ to ‘<domain/username>’ and returns this to Analysis Services.
    4. Analysis Services then executes the DAX query with user set as ‘<domain/username>’
    5. The result set of the query is returned to Power BI via the Connector

     

    Additional FAQs

    1. What if I am using .onmicrosoft.com email address?

    You will need to configure Azure Active Directory DirSync. See the support article at http://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana. For groups that use Office 365, this is often already set up. If you do not have a federated domain to Azure, Greg Galloway shared a tip on setting up a demo Azure domain that you can refer to for setting up a demo or test environment. 

    2. What if the Connector and the Analysis Services Server are not on the same domain?

    We cannot guarantee this will work. It all depends on the trust relationship between the two domains. If the two different domains are in a trusted system model, then the Connector can connect to the Analysis Services Server and the effective user name can be resolved. If not, either the connection or the effective user name resolution may fail.

    3. How do I remove the connector from showing up under ‘SQL Server Analysis Services Server’ in the Get Data list?

    Simply uninstall the connector & this will remove the entry from Power BI

    4. My reports have suddenly started failing & connector is not working too. What should I do?

    Most common reason for this is the invalid password in the Connector. When the password of the Windows account used to configure the Connector expires, the Connector will stop to function. To update the password, launch the Connector, click on the Edit option & update the password.

    An easy way to diagnose the Connector issues is to look in the Event Viewer for errors or warnings:

     

    5. When I share with another user my dashboard connected to on-premises Analysis Services Server, the recipient continues to see the same data as I see. Why isn’t the role based permission applied here?

    This is the current behavior in Power BI. Sharing means “share your own data”. Having said this, we are working on changing this experience in the case of on premises SSAS.

    6. I am using my work email address, but it still doesn’t work 

    OR

    I see this below error in the event viewer, what does it mean?

     

     

    This means login using EffectiveUserName is not working. This will happen for one of the following reasons:

    • You are using a onmicrosoft.com email & you don’t have AD synced with AAD via Dir Sync
    • You are using a work email address but the username returned by AAD does not match the username AD.
    • You are using a work email address but the domain names in AAD & AD don’t match

    You can test this locally from the machine that the connector is running on:

    • Using SSMS: In SSMS for the Analysis Services database set AdditionalConnectionProperties (set EffectiveUserName=<your work email address used in Power BI>). Then execute a DAX query. If this does not work, the connector is not going to work either.
    • Using whoami/UPN: In command prompt, type ‘whoami /upn’ (without quotes). If the result does not match with the email address used with Power BI account, the connector is not going to work

    There are many reasons why this does not work: multiple domains, asymmetric trust-relationships between domains, etc. In these cases, if the SSAS server is not able to resolve the username of the cloud identity, the connector will not work.

    7. How do I keep my tiles connected to my on-premises AS server up-to-date?

    Well, you don’t have to! We do it for you. We refresh these tiles every 10 minutes. You do not have to worry about tile refreshes.



    #Power BI#Business Intelligence#data visualization#Analysis Services#Connector#SSAS

    Wed, 11 Mar 2015 15:00:00 GMT byPower BI Team12 Comments

    Read full post
  • 6 New Updates to the Power BI Designer Preview

    Try what's coming next for Power BI!

    This week we are releasing a new version of the Power BI Designer Preview. This update is packed with lots of new things for you to try: from new connectors, transformations and Report view features to significant usability, quality and performance improvements.

    You can download this update from the Power BI Designer download page.

    What’s New in the Power BI Designer

    The following features have been added or improved in this update:

    1. Performance improvements.
    2. Dynamics CRM Online connector.
    3. Navigator Dialog improvements:
      1. Better preview experience for multi-dimensional sources (Analysis Services and SAP BusinessObjects).
      2. Show Selected Items option.
      3. Improved Search capabilities in the Navigator tree.
    4. New Transformations:
      1. Age and Subtract operations for Date/Time columns.
      2. Aggregate Columns: Option to disable new columns’ prefix.
    5. Field List improvements:
      1. Expand/Collapse tables.
      2. Hide/Unhide fields.
      3. Optimized layout (spacing, margins, and fonts).
    6. Report Pages Navigation - Keyboarding support
    7. Lots of other bug fixes.

    You can watch the following video or read below for more details about each feature.

    Performance Improvements

    We’ve made significant Performance improvements to the Power BI Designer in a couple of areas:

    -  Query Load: Performance of loading queries has improved by about 2x-3x in this release, according to our benchmarks. That is, queries that used to take ~10 minutes before this update, take now just 3-4 minutes. We encourage you to try your own scenarios and let us know if you’re still hitting performance issues.

    -  Excel Workbook Import: We’ve improved latency when connecting to Excel Workbooks from the Power BI Designer. You should see lower times to load previews, which will translate to a more responsive experience in the Query view.

    We continue working on performance improvements in various areas of the Power BI Designer. Expect to see more improvements coming in the next few months!

    Dynamics CRM Online Connector

     In this update, we are introducing a new connector for Dynamics CRM Online. Until now you were able to connect to Dynamics CRM Online using the OData feed connector, however, this was not very discoverable for the majority of our users.

    With the new Dynamics CRM Online connector, users are given guidance on what the URL of their Dynamics CRM Online feed looks like. User input is also validated and users won’t be able to connect until the URL they provide is correct.

    Navigator Dialog improvements

    We’ve made a few improvements to the Navigator dialog layout:

    -  Improved output preview for multi-dimensional sources (such as Analysis Services and SAP BusinessObjects): Output preview for these sources is now always visible as users select items from the tree. This makes it easier for them to preview the data that they will get when clicking Load/Edit Query.

    -  “Show All” vs. “Show Selected”: No more “Select multiple items” option. Instead, the default mode in the Navigator is to select multiple items. This makes it easier for users to select multiple tables. The “Selected Items” area at the bottom-left of the dialog is replaced with “Show All” & “Show Selected” options above the tree. This optimizes the space in the default view to display a larger number of items in the tree.

    -  Improved Search capabilities in the navigator tree: Search experience within the navigator tree has been improved. It’s now much easier to search for an item and click on it in the list or remove the search term. The position in the tree is kept so users do not lose the context for their selection.

    New Transformations

    A few improvements have been made in the Query view to make some transformations easier. These transformations were already possible via custom formulas, but now have been made much more usable:

    Age and Subtract operations for Date/Time columns: When working with Date/Time columns, it’s often useful to calculate the difference between two Date/Time columns (for example, Order Date & Ship Date), or to calculate the Age or Date/Time difference between a given date and “now”. These options are now available in the Date menu under Transform and Add Column ribbon tabs.

    Aggregate Columns – Option to disable column name prefix: An option to disable column name prefix has been added to the Aggregate Columns menu. This option was added a few months ago to Expand Columns but it was missing for Aggregate. With this option, users can decide upfront whether to include a name prefix based on the original column name to the Aggregate Columns output.

    Field List Improvements

    This month we’ve made improvements to the Field List based on your feedback:

    Expand/Collapse Tables: We’re adding the ability to expand or collapse tables in the Field List. This improves navigation and usability when dealing with multiple tables in this pane.

    Hide/Unhide/View Hidden Fields: Now you can also hide specific fields in the Field List to simplify this view. You can also look at hidden fields or unhide them (one by one or all at once).

    - Improved Layout: We’ve made some adjustments to the spacing, margins and fonts in the Field List to make it easier for you to work on it and handle more items.

    Report Pages Navigation – Keyboarding Support

    You can add multiple pages to your reports and navigate those pages using the left pane in the Report view. In this update, we’re adding keyboard support so it is much easier for you to move between pages:

    -  Up/Down Arrow keys: Use these two keys to move up and down in the list, one page at a time.

    -  Page Up/Down keys: Similarly to the previous item, move to the previous or next page in your report.

    -  Home/End keys: Move to the first or last page in your report with a single gesture.

    Bug Fixes and Quality Improvements

    Finally, we’ve also fixed lots of bugs and issues reported by many of you via “Send a Frown”. We encourage you to continue sending us feedback about the product and reporting any issues that you may find while working with the Power BI Designer.

    We hope that you enjoy this update. Stay tuned for more updates coming soon…

     

    #Power BI#Business Intelligence#data visualization#power BI Designer#Microsoft

    Thu, 19 Feb 2015 17:00:00 GMT byMiguel.Llopis13 Comments

    Read full post
  • BA Marathon: Free Day of Online Analytics Learning Spotlights Power BI, Excel

    Guest blog post by: PASS VP of Marketing Denise McInerney – a Data Architect at Intuit – began her career as a SQL Server DBA in 1998 and now applies her deep understanding of data to enable analytic solutions to business problems. She is founder of the PASS Women in Technology virtual chapter, and a speaker at user group meetings and conferences. You can follow her on Twitter at @denisemc06.

    On your mark… Get ready… Get set for a free day of online analytics learning at the PASS BA Marathon webinar series Tuesday, Feb. 3, kicking off with an inside look at the new Power BI platform, redesigned for data professionals, business analysts, and business users.

    Featuring six back-to-back live webinars with an exceptional lineup of business analytics and data experts from around the world, the BA Marathon will preview a slice of top speakers and sessions coming up at the PASS Business Analytics Conference, April 20-22, in Santa Clara, CA.

    The BA Marathon is excited to have Jen Underwood, Sr. Program Manager of Microsoft Business Intelligence & Analytics, and Miguel Martinez, Product Marketing Manager for Power BI, participating in the webinar series and sharing how business analytics and BI professionals can get more from Power BI and Excel.

    Check out the full BA Marathon lineup, and register now for your favorite sessions – or all of them! And make sure to follow the conversation at #PASSBAMarathon.

    Then see how to continue your analytics learning at the PASS BA Conference, with 60+ real-world sessions from these Excel and Power BI all-stars and more. Get the best rate when you register by Feb. 16 – and save an additional $150 with discount code BAMSFTBL150. I look forward to seeing you in Santa Clara! 

    #events#PASS#PASS Business Analytics Conference#PASS BA Marathon

    Fri, 30 Jan 2015 23:00:00 GMT byPower BI Team1 Comment

    Read full post
  • 6 New Updates in Power Query

    Try what's coming next for Power BI!

    We hope all of you are enjoying 2015 so far! The Power Query team has been busy working on the latest Power Query update, which includes a new connector (or many new connectors, in this case!) and a bunch of other usability improvements.

    You can download this update from the Power Query download page.

    In this update

    The following features have been added or improved in this update:

    1. ODBC Connector
    2. “From Azure” ribbon dropdown menu
    3. Workbook Settings dialog
    4. Option to enable or disable relationship detection
    5. Column & Row counts in Query Editor Preview
    6. Confirmation dialog to delete a query when deleting a worksheet

    You can watch the following video or read below for more details about each feature.   

     

    ODBC Connector

    This new connector can be found under “From Other Sources > From ODBC” and it allows users to connect to their generic ODBC providers. This makes it possible for users to bring in data via Power Query from several data sources that are not natively supported. Users can connect and import data from ODBC-based data sources by specifying the connection string parameters and a SQL statement to execute.

     

    Note that credentials should still be entered on the credential page, rather than in the Connection String field above, to ensure credentials are not inadvertently shared with the query. Similar to other SQL Statement connections, Power Query will not be able to optimize query steps after the SQL statement so it is suggested that you optimize the original statement as much as possible (i.e. include “group by”, “join” operations, etc.)

    “From Azure” ribbon dropdown menu

    With the addition of several new data sources in the past few months, we started to hit issues related to the amount of entries under the “From Other Sources” dropdown menu. We considered sending bigger, higher-resolution monitors to all Power Query users as a gift, so everyone could see all entries in this menu. However, given the large amount of Power Query customers, this was not a viable solution. As a fallback plan, we have decided to create a new top-level menu for Azure-based data sources to balance the number of entries in each dropdown menu. This does not add any new data sources, but improves navigation, discoverability and ease of access for our data sources lists in the ribbon.

    Workbook Settings dialog

    In this update, we’re modifying the way in which Workbook Settings are exposed in the Power Query ribbon. Instead of having Workbook Locale and Fast Combine directly in the ribbon, we’ve moved them into a new Workbook Settings dialog. This allows us to provide better descriptions about these two settings, as well as make room for a new setting that we are adding this month: Enable/Disable Relationship Detection.

    Option to enable or disable relationship detection

    When loading queries into the Data Model, Power Query will try to detect relationships between them. These relationships might exist in the data source (i.e. SQL Server) or might be implicitly created by the user as part of their query steps. For instance, merging two queries would make Power Query detect a relationship between these two queries, which would be automatically created in the Data Model when these queries are loaded.

    As much as this is a useful feature, it is also one that may cause an increase in the load time for your queries. In many cases, users either do not want these relationships or know that there aren’t any relationships beforehand, which make this additional wait time unnecessary. Based on feedback from many of you, we have decided to add an option to turn this relationship detection off. Note that this setting is workbook-specific, so you will need to modify the default behavior (default is to continue trying to detect relationships) for each workbook via the Workbook Settings dialog.

    Column & Row counts in Query Editor Preview

    We have added counts for number of columns and rows in the Query Editor Preview. This will help you get a better sense for the shape and size of your data while defining transformations in your queries. Note that these counts are based on the Query Editor Preview, not the full data set. In particular, the number of rows will be smaller than the actual table row count in most cases. You will see these numbers change as more or less rows are displayed in the preview (for instance, you will see the row count increase when scrolling down in the preview).

    Confirmation dialog to delete a query when deleting a worksheet

    Before this update, deleting an Excel worksheet would result in all queries that had been loaded to this worksheet being automatically deleted as well. This caused accidental data loss for some users, unaware of this default behavior. Based on feedback, we have decided to add an additional confirmation for what to do with queries when the Excel worksheet that they were loaded to is deleted. There are two choices for the user: delete queries or modify their load settings to disable load to worksheet.

    That’s all for this month. We hope that you enjoy this update and find the new features valuable for you and your customers. Please send us your feedback or suggestions via Smile/Frown in Power Query.

     

    Download Power Query from our official download page.

     

    #update#power query#Power BI#Excel#Query Editor#Microsoft BI#azure#ODBC Connector#relationships#columns#rows

    Tue, 13 Jan 2015 17:00:00 GMT byMiguel.Llopis4 Comments

    Read full post
  • New Power BI features available for preview

    Today we are previewing new features for Power BI, our self-service business intelligence solution designed for everyone. Power BI reduces the barriers to deploying a business intelligence environment to share and collaborate on data and analytics from anywhere.

    Try what's coming next for Power BI

    We are introducing a number of new Power BI features available for preview including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premise SQL Server Analysis Services tabular models.

    These preview features are available for customers with a United States address. We’ll incrementally add new country support as we extend the preview globally in the coming months. Existing customers will find a preview option in their current Power BI sites. For those not currently using Power BI, you can sign up for a Power BI preview which includes the new features here.

    Now in Preview:

    Power BI dashboards

    Users can create personalized dashboards to monitor their most important data. A dashboard combines on-premises and cloud-born data in a single pane of glass, providing a consolidated view across the organization regardless of where the data lives. 

    Users can easily explore all their data using intuitive, natural language capabilities and receive answers in the form of charts and graphs. They can also explore data through detailed reports that target specific aspects of their business. Visuals from these reports can also be pinned to their dashboards for continuous monitoring. As part of this experience new visualizations have been added including combo charts, filled maps, gauges, tree maps, and funnel charts.  

    Out-of-the-box connectors for popular SaaS applications

    Power BI now provides “out of the box” connectivity to a number of popular SaaS applications. In addition to the existing seamless connection with Microsoft Dynamics CRM Online, with today’s release customers can also connect to their data in Salesforce, Zendesk, Marketo, SendGrid, and GitHub with many more to come in the months ahead. With an existing subscription to one of these services, customers can login from Power BI. In addition to establishing a data connection, Power BI provides pre-built dashboards and reports for each of these applications.  


    Excel BI and the Power BI Designer

    Excel 2013, which comes standalone and with Office 365 ProPlus, is Microsoft’s premier business analyst tool – it includes rich business intelligence features (Power Query, Power Pivot, Power View, Power Map) fully integrated with the powerful ad hoc analysis capabilities and familiar features of Excel – like Pivot Tables and Excel Charting. With Excel 2013, analysts can publish Excel Workbooks to Power BI, and share data, analysis and reports with Power BI users.

    For Power BI customers who don’t have Excel 2013, the new Power BI Designer provides a solution expressly designed for Power BI report creation. It can be used to import and model data, then author and publish Power BI reports to the Power BI service. 

     

    Connect live to on-premises Analysis Services models

    With the new Power BI connector for SQL Server Analysis Services, customers can realize the benefits of a cloud-based BI solution without having to move their data to the cloud. Customers can now create a secure connection to an “on-premises” SQL Server Analysis Services server from Power BI in the cloud. When users view and explore dashboards and reports, Power BI will query the on-premise model using the user’s credentials.

    With this hybrid solution, organizations can continue to retain, manage and secure their data on-premises, while securely enabling users to benefit from that data via Power BI.

    Stay connected from any device

    We are working on a wave of native mobile apps for Power BI. These apps will allow users to access their Power BI dashboards and reports through immersive mobile experiences for iPad, iPhone, and Windows tablets. The first of these - the iPad app is available today and can be downloaded from the Apple App Store, with other platforms following in the coming months. All Power BI mobile apps enable users to share insights and collaborate with colleagues so that they can take immediate action, from anywhere, anytime.

    We welcome you to preview the new features for Power BI and look forward to your feedback.

    #Power BI#Excel#data visualization#dashboard#Salesforce#power BI Designer#Marketo#Zendesk#Preview#Analysis Services#SaaS#Dynamics CRM

    Thu, 18 Dec 2014 22:50:00 GMT byPower BI Team21 Comments

    Read full post
  • Announcing New Excel 2013 BI Stability and Usability Improvements

    Recently we have announced a new public update to Excel 2013 that includes major stability and usability improvements in Self Service BI scenarios, especially when Power Query, Power Pivot & Power View are involved. For this release we eliminated over 120 bugs, crashes and hangs, and introduced two new important usability improvements that aim to make the analysts’ work smoother.

    If you are ready to get the update straight away, skip to the How to Download and Install the Update section below.

    Deferred Data Model Update

    In Excel 2013, any change to the Data Model caused Excel to immediately update every PivotTable, PivotChart, and Query Table in the workbook. 

    The users were forced to wait every time that they make a change to the Data Model, such as creation of a new measure or a calculated column. Sometimes, depending on the workbook and Data Model complexity, the waiting period could be pretty long. 

    To address this problem we have introduced the following changes:   

    1. When the user is within the Power Pivot add-in, we accumulate the changes performed by the user in the Data Model  without the Excel workbook being updated. 
    2. When the user moves the focus away from the Power Pivot window and in case the Data Model was changed, then the Excel workbook will be updated automatically.

    This change significantly enhanced the experience of the user doing modelling in Excel, so don’t miss on installing this update and experience it for yourself.

    Out-of-Memory Improvements

    Running out of memory in Excel 2013, in scenarios when the Data Model was involved, caused a computer freeze or Excel crash. In this release we targeted these scenarios and introduced a better and friendlier customer experience.

    Specifically, in the scenario when 64-bit version of Excel 2013 is running low on physical memory, or if the user is running a 32-bit version of Excel 2013 and getting close to the 2GB virtual memory limit, Excel will detect this, will stop all allocations and will show the “Out of Memory” error message to the user.

    Bug Fixes

    For this release we fixed over 120 bugs, crashes and hangs in different scenarios. We improved the stability of slice and dice operations, refresh, Data Model upgrade, connection editing and many more.

    We really encourage you to download the latest Excel update and try it yourself.

    How to Download and Install the Update

    If you have automatic updates enabled for your Office 365 subscription, you should receive the latest Excel update soon. If you don’t have automatic updates enabled in Excel, go to File > Account > Update Options > Update Now.

    If you are running a perpetual Office edition then you will need to install the following updates through Windows Update or Microsoft Download Center – KB2910929, KB2899498, KB2899505 and KB2920734.

    #update#power query#Power Pivot#Power View#Excel#performance

    Tue, 16 Dec 2014 17:00:00 GMT byPower BI Team18 Comments

    Read full post