27.02.2015, 15:11
|
#1 |
|
Участник
|
axsa: Power BI and Dynamics AX: Part 5: PowerBI.com and On Premise Data (Preview)
Источник: http://blogs.msdn.com/b/axsa/archive...a-preview.aspx
============== This is the final part of the Power BI and Dynamics AX blog series for now, the earlier posts focused on the current functionality available within Power BI for Office 365. This blog post is going to talk about new functionality, that at time of writing, is only available in preview. This the topic in this post should not be applied to a production environment as it relates to preview functionality. The new version of PowerBI.com has introduced a lot of new functionality; Dashboards, new visualisations, new apps for iOS and Windows devices all making for an extremely rich PowerBI experience on the web. This post is focused however on the new Live Connectivity functionality for On Premise Data.  The new connector that has been released allows a live connection to an On Premise Analysis Services Tabular Database. While unfortunately at the time of writing the SSAS Database shipped with Dynamics is a Multi-dimensional database you can't connect directly, but you can create a Tabular Database to "flatten" the multi-dimensional database into a tabular Database for use with PowerBI.com. The latency of your data in PowerBI.com will be determined by how often you're processing your SSAS Cube. As an organisation you need to determine the best connectivity method for you and PowerBI, this may be through SSAS or through OData as previously described. There are limits to the On Premise option at the moment, given the nature of the Q&A Natural Language Queries and the processing that is required, Q&A is not currently supported on On-Premise data, you must still upload data into the data model for Q&A to work. For more information, start with the documentation from the PowerBI team - Which Power BI experience is right for me? For this example we are going to use:
We are going to create our Tabular Database through Visual Studio using a Business Intelligence Project Template that is installed as part of the SQL Server Data Tools pack. For the example today we are going to create a Project analysis tabular model with basic project information along with some key measures. To start, launch Visual Studio. We are going to create a new SSAS Tabular Project by clicking "New Project", and selecting "Business Intelligence", "Analysis Services Tabular Project"  After selecting the project and entering a name, you will be prompted to connect to an instance of SSAS, this is where you connect to your newly created SSAS Tabular Instance. Once we have our new project you have a few options of how you would like to create the model, depending on your technical ability. For this example we are going to use the wizard as it's the easiest option. To get started, select "Model" > "Import from Data source". You'll be prompted with the list of data sources you can import from. You have the option of connecting directly to the AX relational DB, but I find the cubes easier as fields like enums, references, etc have been cleaned up and are a lot easier to work with. You also get the benefit of leveraging the calculations already in place in the standard cubes.  For our purposes today, we will use Microsoft Analysis Services. In the next screen you'll enter the connection details for the SSAS Multidimensional Database (Dynamics AX Standard Cubes).  After entering credentials, you'll be prompted for the MDX query that the tabular database should use for this model. You can start with MDX if you wish, or use the "Design" option to launch the visual designer. From this screen we will select the data we want form the cube to be available in our Tabular model. You can drag and drop fields from cubes on the left hand side into the pane on the right. As you add fields you will see your tabular model refresh to give you a preview of the data that will be available.  Once you've selected the information you want available, click Ok – you can now give your query a friendly name and then click Finish. If you had provided incorrect credentials, you will receive an error – you will need to back up to the credentials and update them with an account that has access to the cubes. Once you click finish the MDX query will be processed, once finished, close the window and you will see the results of your MDX query. You can take this time to change column names if you wish to make the data a little friendlier once we load it into PowerBI.  You can now close and save your Model. If you would like to double check its deployment, you can open up SQL Management Studio and you should see your newly created tabular DB. The SSAS On Premise model uses security from SSAS, so this is where you would apply your role security to your SSAS data model. Users need to have access to this DB to be able to explore the data in PowerBI (This is a key difference to the OData/Workbook model previously discussed)  The last step On Premise is to install the PowerBI Analysis Services Connector (Preview). You can find a detailed guide of how to download and install the connector here. The installation will require your PowerBI.com login details (Your Office 365 credentials) as well as the details for the Tabular instance of SSAS. Now we are ready to expose the new tabular database to PowerBI. You can log into the Preview here. At the time of writing this preview is only available to customers within the United States. Once you've logged in, select "Get Data" > "SQL Server Analysis Services" and then "Connect".  You will be presented with a list of all the SSAS connectors published within your organisation. Find and select yours in the list. You will then see a list of Models which are available on your instance. Click the model you had created earlier and click "Connect"  Once connected, you will now have a new Dataset available which is your On Premise Data source. (Note: The name will be your SSAS instance, you can rename it in PowerBi.com if required)  Now your data is available to create reports and dashboards against like any other data source. From the ellipsis menu on the Dataset click "Explore" and you be taken to a blank PowerView page to start building your report. If you're familiar with creating visualisations in PowerView you can follow the same process, if not you can find a detailed guide here. Below is an example of a Project Profitability analysis report based on On-Premise Data on Dynamics AX. The Invoiced Revenue, Cost, Hours and Gross Profit are all based on calculated measures defined in our standard Dynamics AX SSAS Cubes. You can find a detailed reference of the information available in the cube here.  One of the key benefits of the new PowerBI.com is the ability to create dashboards. Dashboards allow visualisations from multiple reports to be pinned to a single dashboard to give you a quick and easy overview of multiple sets of data. You can then drill into that specific report by clicking the visualisation from the dashboard.  This was a very simple example of exposing some Dynamics AX data to explore the preview; users of PowerBI should consider the best connection method for them, along with planning around what data should and should not be exposed. The PowerBI technology is changing at a great pace at the moment, it's important to keep up to date with what is coming and how it can help shape your ongoing Business Intelligence Strategy. For information on the new PowerBI.com platform, try these resources: Hopefully this has been a help insight to some of the new functionality out in preview at the moment, and how it can be applied to Dynamics AX. Thanks, Clay. Источник: http://blogs.msdn.com/b/axsa/archive...a-preview.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
Линейный вид
