Advertisements

Working with Project “Madeira” Content Pack for Power BI

In today’s post we will see how to use Power BI Content Pack for Madeira.

The content pack is preconfigured to work with sales data and financial data from the demonstration company that you get when you sign up for the Project “Madeira” preview.

To see your Project “Madeira” data in Power BI, you must have the following:

  • Access to Project “Madeira”.
  • Access to Power BI.

Before we start with connecting we will collect few information that we will require while connecting the Power BI to fetch data from Madeira.

User Name

Web Service Acess Key

Note down both value, if you don’t have Web Service Access Key Generate one and set appropriate Expiration date or Set Never Expire as shown below.

PowerBi_Madeira-1

Next open the Web Service Page.

Make sure all selected Web Service is running as in below screen.

The data is extracted from your Project “Madeira” company using web services to read live data. In Project “Madeira”, the Web Services window lists the web services that have been set up for you, including the following that are consumed by the content pack in Power BI:

  • ItemSalesAndProfit
  • ItemSalesByCustomer
  • powerbifinance
  • SalesDashboard
  • SalesOpportunities
  • SalesOrdersBySalesPerson
  • TopCustomerOverview

Note: If you change the name of any of these web services, the data will not show up in Power BI.

Note your URL of OData. Edit it to follow the format one shown in below screen.

PowerBi_Madeira-2

Now we have all our required information.

Open Power BI site and Get Data from Services as shown in below screen.

PowerBi_Madeira-3

Select Project “Madeira” Content Pack and click on Get.

PowerBi_Madeira-4

Enter the URL of Web Service we get in above step.

PowerBi_Madeira-5

Select Next.

Select Authentication method as Basic.

In User Name enter your user name as obtained in above step.

For Password ented the Web Service Access Key as obtained in above step.

PowerBi_Madeira-6

Choose Sign In.

After few minutes/seconds churning you will be ready with your first Dashboard from Madeira.

PowerBi_Madeira-7

Troubleshooting

“Parameter validation failed, please make sure all parameters are valid”
If you see this error after you enter your Project “Madeira” URL, make sure the following requirements are satisfied:

“Login failed”
If you get a “login failed” error when you log in to the dashboard, using your Project “Madeira” credentials, then this can be caused by one of the following issues:

  • The account you are using does not have permissions to read the Project “Madeira” data from your account.

    Verify your user account in Project “Madeira”, and make sure that you have used the right web service access key as the password, and then try again.

  • The Project “Madeira” instance that you are trying to connect to does not have a valid SSL certificate. In this case you’ll see a more detailed error message (“unable to establish trusted SSL relationship”).

    Note: Self-signed certificates are not supported.

“Oops”
If you see an “Oops” error dialog after you pass the authentication dialog, this is most frequently caused by a problem connecting to the data for the content pack.

Thats all for today’s post.

I will comeup with more details in my upcomming posts.

Til then keep exploring and learning.

 

Advertisements

Microsoft Power BI – My Organization Content Pack Library

Continuing from my previous post. Today we will discuss about Content Pack Library (My Organization) in Power BI.

In case you have missed my previous posts here I present the link to all previous posts below:

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

Microsoft Power BI – Part – X

Analyse in Excel (preview)

PowerBI-47

Login to Power BI using your credentials.

OrgPowerContent-1

Select Get Data.

OrgPowerContent-2

From Content Pack Library section select Get Data under My Organization.

OrgPowerContent-3

Select Create content pack.

OrgPowerContent-4

This is the part where majority of work to be performed.

In First Section – Choose who will have access to this content pack

You have 2 option

  1. You can share with specific users or Groups, since your AD is in sync with your in premise AD you can select them.
  2. You can share with your entire Organization.

Select the Group or Users if you go with first option.

In Second Section – Provide Title for you Content Pack

In Third Section – Provide detailed Description for your content pack.

OrgPowerContent-5

In Fourth Section – You can select Image for your content pack.

Finally in Fifth Section you will define what all content you want to share for the user using this content pack.

Here all available contents are divided under 3 sections (Dashboards, Reports & Datasets).

When you select Dashboard all included Reports and Dataset will be shared by default.

When you select Reports all included Datasets will be shared by default.

When you select Dataset only that will be shared.

You can select combination as you desire.

I will provide write up why we will select selective contents to share with case study, in my future post some time other.

Once you are done with all above step you are good to Publish the Content Pack.

OrgPowerContent-6

If we revisit the Get Data option again for My Organization, we can view our Published Content Pack which we shared with others.

User can customize as per his need and Share with others in form of New Content Pack or as-it-is depends.

Good Example will be we have one Content Pack for My Organization which include all modules Dashboard, Reports and Datasets.

My IT will create Module wise Content Pack (Finance, Sales, Purchase, Warehouse, Manufacturing etc…) and share these content packs with concern department Heads. Further each Heads will create sub-content pack and share with their team with information which is related to them and so on.

 

That’s all for today, will come up with more information in my future posts.

Till then keep exploring and learning.

 

 

Microsoft Power BI – Part VIII

Continuing from my previous post. Today we will see usage of Power Content Pack to Refresh in Premise Navision Installation using Personnel Gateway.

Please see my previous post it will help you in continuing with this Walkthrough.

In case you have missed here I present the link to all previous posts here below.

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Step -1

Check your Service is up & running, here we are not going to use SLL in fact Personnel Gateway.

PowerBI-86

Step -2

Make sure you Imported your Query Objects required for Power BI Dynamics Nav Content Pack. If you are not using NAV-2016, in my case I am using NAV-2015 for this Walkthrough.

PowerBI-87

Step -3

Make sure your Web Services are published.

PowerBI-88

Step -4

You have installed the Power BI Gateway – Personal and Login to it using your Credentials.

PowerBI-89

Step – 5

Login to PowerBI using your Credentials and choose Get Data from Bottom Left Corner in your Navigation Bar.

PowerBI-90

Step – 6

Connect Your Service using MS Dynamics Content Pack.

PowerBI-91

PowerBI-92

Notice here I am not using https:// also remove any ‘/’ and Service name from the URL.

PowerBI-93

Set your Authentication Mode and Sign In.

PowerBI-94

It will take some moment and your Dataset, Report & Dashboard will be available to you.

Graphical representation and data may vary depending upon data in your database.

We are done.

For further detail on any step you can refer previous posts for any clarifications, Links provided above.

Will come up with more details in my upcoming posts.

Till then keep trying and practicing.

 

Microsoft Power BI for Beginners Table of Content

In March I have written few posts on Power BI keeping in mind who are beginners.

In case you have missed here I present the link to all those posts here below.

 

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Microsoft NAV Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

Microsoft Power BI – Part – X

ANALYZE IN EXCEL

I will come up with more features and other important details in my future posts.

You can find lots of materials on Power BI here : https://powerbi.microsoft.com/en-us/

https://powerbi.microsoft.com/en-us/learning/

PowerBI-85

 

 

 

 

 

Microsoft Power BI – Part – VI

Continuing from my previous post, if you have not seen yet please check it out it will help you continuing with this post.

You can check:

Microsoft Power BI – Part – I

Microsoft Power BI – Part – II

Microsoft Power BI – Part – III

Microsoft Power BI – Part – IV

Microsoft Power BI – Part – V

Today we will discuss how to use Power BI Content Pack:

PowerBI-60

Few things to keep in mind to get report Generated Successfully.

Point-1 You have required objects for this content pack in your database.

If you are using 2016, you can skip this as these objects are available in 2016. If you are using 2015 or 2013 then export these objects from 2016 and import in your database.

PowerBI-61

Above Pages will be present in your previous version of database, you need to import the 6 Query objects. These are necessary as these are used as source to fetch data from your data base by this content pack.

Point-2 Web Services need to be published.

PowerBI-62

Make sure you use same name for Services as you may get error while connecting and refreshing/ fetching data using this content pack.

Point-3 SSL port and web address is used to access the services.

https is used as this will not work on http.

Self-signed certificate will not work which we use for test purpose for publishing services and testing. It will throw Trusted relationship connection type error. If you have one use the same instead using Self-signed certificate.

No worries, don’t be disappointed. I will give you tip to overcome this requirement just follow as below:

You may remember that in our previous post we installed and used Power BI Gateway, actually we got a Trusted Certificate which we can use for this purpose.

PowerBI-63

Open IIS Manager on your system.

PowerBI-64

Select Serve/ Machine in Left Navigation Tree and Select Server Certificates.

PowerBI-65

Locate the Data Management Gateway SSL Certificate, this got installed when you installed Gateway. Double click to open the Properties of the certificate.

PowerBI-66

Switch to Details Tab, Scroll down to find Thumbprint, and Copy the Thumbprint and paste to Notepad finally remove all space leading, trailing and any in between.

It should look like above right bottom corner notepad window no space in Thumbprint signature.

Copy this Value and use as Thumbprint in you Navision Service.

PowerBI-67

Select Your Service.

Edit.

Paste the Certificate Thumbprint.

Make Sure SSL is Enabled for your OData Services.

Save.

Restart the Service.

We are done with the basic requirements now let us connect using Power BI Miscrosoft Navision Content Pack.

Now Login with your credentials to Power BI.

PowerBI-68

From Left bottom corner Select Get Data.

PowerBI-69

Select Get Data from Services.

PowerBI-70

Scroll to find Microsoft Dynamics NAV Services and Select Connect.

PowerBI-71

Copy your OData Service URL. Make sure you remove Service name from the URL and trailing ‘/’ after company name from the URL.

Select Next.

PowerBI-72

Select the Authentication Method and then Sign In.

PowerBI-73

Wait for few minutes you dataset will be loaded.

As a result you will get: Dataset, Report & Dashboard ready for you in few minutes depending upon the data volume and connection speed.

PowerBI-74

The report and dashboard view will be as per the available data in your database.

Hurray!!!

You are done with your practice session.

This is a Holi Gift for my reader from my side.

I will come up with more features explanation and other Tips & Tricks in my upcoming posts, till then keep practicing and learning from blogs and helps available online.

 

PowerBI.com content package for Microsoft Dynamics NAV 2016

Microsoft Dynamics NAV 2016 includes an entirely new integration with Microsoft Power BI.

In Microsoft Dynamics NAV 2016, using the new Power BI Content Pack, Microsoft Dynamics NAV end users can connect a correctly configured Microsoft Dynamics NAV instance to PowerBI.com and immediately be able to see full business performance insights in a dashboard.

The PowerBI.com content package for Microsoft Dynamics NAV 2016 empowers you to share and present data across an organization. Data can be defined centrally but be consumed, modified and presented to the local needs of users, while everybody still sees data from the same overall picture.

Benefits:

  • Business performance dashboard  Data from Microsoft Dynamics NAV is presented in an easy-to-use dashboard, providing a complete overview and important insights
  • Finance and sales insights  Users can drill into more details in the sales and finance reports, facilitating decision making
  • Personalizing the insights  Users can create a personalized dashboard from the existing KPIs and charts in reports
  • Modify and extend With this package, users can start using and exploring their own data using Power BI in just a few minutes. The dashboard gives users KPIs and charts on their sales and finance data. The setup consists of three easy steps:

Users can create their own KPIs and charts, select data from queries, set filters, select visualizations and more to create their own dashboards

  • Sign in to PowerBI.com.
  • Select the content package for Microsoft Dynamics NAV.
  • Connect a Microsoft Dynamics NAV instance to the content package. Today I will discuss about below:

It takes less than five minutes to get going! Depends upon net speed and volume of data.

Preparation:
PowerBI-1

Make sure OData Service is enabled and Port for same is defined in your Server Instance of Navision. Also you use valid thumbprint and SSL is enabled.
PowerBI-2

Check that the Page 197 Service powerbifinance is published in Web Services window. Copy the OData URL we will be requiring it while connecting to content pack for Dynamics 2016 in BI.

 

Go to PowerBI.com and sign in or go through the easy account setup process.
PowerBI-3

Sign in to Power BI using your credentials.

You first get to a page to select your data source.

The data sources are OData feeds, such as OData web services from any Microsoft Dynamics NAV solution.

  • To import your Microsoft Dynamics NAV data into Power BI, you need to have permissions to the tables where data is retrieved from, in this case sales and finance data. You sign in your account credentials.
  • The Power BI Microsoft Dynamics NAV content pack uses Microsoft Dynamics NAV’s web services to retrieve your data. If you have a lot of data in your Microsoft Dynamics NAV instance, a suggestion to minimize the impact on your web service usage is to change the refresh frequency from the current default which is once a day to only refresh on weekdays or every other day depending on your needs. Another suggestion is to have one admin create the content pack and share it with the rest of the team instead of having every admin in your organization create their own

In this case select Services to get to content packages. Refer below how to connect.
PowerBI-4

Select the content package for Microsoft Dynamics NAV.
PowerBI-5

You will be prompted to add the URL to your Dynamics NAV instance. 

An easy way to obtain this URL is in Dynamics NAV to go to Web Services, find the powerbifinance web service and copy the Odata URL, but leaving out the “/powerbifinance” from the URL string. 

When you paste in the copied URL, you must remove the last part of the web service-specific URL: “/powerbifiance” – you only need the company-specific part of it

After entering the URL You will be prompted to select an authentication method. 

Select the appropriate method and provide the relevant user credentials.
PowerBI-6

Here you provide the OData URL we saved above. Enter your Authentication Mode and provide your User ID and password to get connected to the data.

When you select Log on, it will take a few minutes for the content package to connect to your Dynamics NAV instance, get the data and create the dashboard and reports.

Troubleshooting

 

“Parameter validation failed, please make sure all parameters are valid”

If you see this error after typing your Microsoft Dynamics NAV URL. Make sure the following requirements are satisfied:

– The URL follows exactly this pattern https//instance.navserver.com:7048/DynamicsNAV90_Instance1/OData/Company(‘CRONUS%20International%20Ltd.’) replacing ” instance.navserver.com” with your NAV Server name, “DynamicsNAV90_Instance1” with your NAV Server Instance name and “Company(‘CRONUS%20International%20Ltd.’)” with your NAV Company name.

– Make sure all the letters are lower case.

– Make sure the URL is in ‘https’.

– Make sure there are no trailing forward slash at the end of the URL.

 

“Login failed”

If you get a “login failed” error after using your Microsoft Dynamics NAV credentials to login, then the account you are using doesn’t have permissions to retrieve the Microsoft Dynamics NAV data from your account. Verify it is an admin account and try again.

Let’s see what data exactly this page provides:
PowerBI-7

Depending on whether you use your own database or the demo database, the charts in the dashboard might have different content or none at all.

 New Post with details available, please check out : https://msdynamicsnavashwinitripathi.wordpress.com/2016/03/23/microsoft-power-bi-part-vi/

 

We will see rest details in my upcoming post.

Designing Report Layouts from the Microsoft Dynamics NAV Development Environment

After you have created a dataset for a report, you design the report layout. The report layout determines how the report looks when it is viewed, printed, and saved from the Microsoft Dynamics NAV client. The report layout specifies which fields of the dataset are included in the report and how they are arranged, the format of text that appears on the report (such as font type and color), margins, background images, and more.

You generally display most data in the body of a report, and you use the header to display information before any dataset fields are displayed. For example, you can display a report title, company, and user information in the header of a report.

Report Layout Types

There are two types of report layouts: Word and Client Report Language Definition (RDLC). Word report layouts are based on a Word document (.docx file type) and are created and modified by using Word 2013. RDLC report layouts are .rdlc or .rdl file types that are created and modified by using Visual Studio 2013 or SQL Server Report Builder 3.0.

Built-in and Custom Report Layouts

In the Microsoft Dynamics NAV Development Environment, you can create both an RDLC report layout and Word report layout on a report. These layouts are referred to as built-in layouts because they are part of the report object in the database. This means, for example, if you export the report object as a .fob or .txt file, the RDLC report layout and Word report layout are included. A report can only have one built-in RDLC report layout and one built-in Word report layout. By default, the built-in RDLC report layout is used when the report is run in the Microsoft Dynamics NAV client unless there is only a built-in Word report layout, in which case, the built-in Word report layout is used.

Microsoft Dynamics NAV users can specify whether to use the built-in RDLC or Word report layout on a report from the Microsoft Dynamics NAV Windows client and Microsoft Dynamics NAV Web client. From the client, users can also create custom report layouts that are based on the built-in report layouts. This enables users to have several different layouts for the same report which they can switch among. Custom report layouts are managed from page 9650 Report Layouts in the Microsoft Dynamics NAV client. Unlike built-in report layouts, which are part of the report object, custom report layouts are stored in table 9650 Report Layouts of the database.

Report Layouts in a Multitenant Deployment

In a multitenant Microsoft Dynamics NAV deployment, the built-in report layouts are stored in the application database because they are part of the report objects. Therefore, built-in report layouts are available to all tenants. Custom report layouts are stored in the business data database; therefore they are specific to the tenant. This enables you to create separate report layouts for each tenant.

Add Fields from a Report Dataset to a Word Report Layout

A report dataset can consist of fields that display labels, data, and images. This topic describes procedure for adding fields of a report dataset to an existing Word layout of a report. You add fields by using the Word custom XML part for the report and adding content controls that map to the fields of the report dataset.

To display the Custom XML part for the Report in Word

  1. Open the Word report layout document in Word for editing.
  2. Show the Developer tab in the ribbon of Word.

By default, the Developer tab is not shown in the ribbon.

  1. On the Developer tab, choose XML Mapping Pane.
  2. In the XML Mapping pane, in the Custom XML Part list, choose the XML part for Microsoft Dynamics NAV report. The name of the XML part has the following format:

urn:microsoft-dynamics-nav/reports/report_name/ID

report_name is the name that is assigned to the report as specified by the report’s Name Property.

ID is the identification number of the report.
XMLDataSet
After you select the XML part, the XML Mapping pane displays the labels and field controls that are available for the report.

To add a label or data field

  1. Place your pointer in the document where you want to add the control.
  2. In the XML Mapping pane, right-click the control that you want to add, choose Insert Content Control, and then choose Plain Text.

AddDataField

Note

You cannot add a field by manually typing the dataset field name in the content control. You must use the XML Mapping pane to map the fields.

To add repeating rows of data fields to create a list

  1. If not already existing, add a table row that includes a column for each field that you want repeated.

This row will act as a placeholder for the repeating fields.

  1. Select the whole row.
  2. In the XML Mapping pane, right-click the control that corresponds to the report data item that contains the fields that you want repeated, choose Insert Content Control, and then choose Repeating.

AddRepeatingField

  1. Add the repeating fields to the row as follows:
    1. Place your pointer in a column.
    2. In the XML Mapping pane, right-click the control that you want to add, choose Insert Content Control, and then choose Plain Text.

InsertContentField

  1. For each field, repeat steps a and b.

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: