Why I cannot download .pbix files from powerbi.com

Power BI, , ,

If like me you thought “Hey, it’s a new feature, I can finally create reports in powerbi.com service and then when I need to mashup with data from other sources, then I just have to download the Power BI desktop file (.pbix), do my mashup and then publish it again.”, then you are sorely mistaken. 🙂

I tried to download one pbix file but got this error message:

Basically you would need to check that your report/dataset does not fall into these limitations:

  • To download the file, you must have edit access to the report
  • The report must have been created using Power BI Desktop and been published to the Power BI service, or the .pbix must have been uploadedto the service.
  • Reports must be published or updated after November 23, 2016. Reports published prior to then are not downloadable.
  • This feature will not work with reports originally created in the Power BI service, including content packs.
  • You should always be using the latest version of Power BI Desktop when opening downloaded files. Downloaded .pbix files may not open in non-current versions of Power BI Desktop.
  • If your administrator has turned off the ability to export data, this feature will not be visible in the Power BI service.

Refer to https://docs.microsoft.com/en-us/power-bi/service-export-to-pbix#limitations-and-considerations

I wish you can be able to just simply start off in powerbi.com and then download the .pbix file later when you need to but not at the present moment it seems.

Dynamics 365 AX – Creating Data Entities for BYOD Azure Database including those without a natural key

Business Intelligence, Microsoft Dynamics AX, , ,

We, BI professionals, sometimes take for granted that we can access the database of an ERP system quite easily, especially one that is made by Microsoft. However, the live Dynamics (365) AX database cannot be assessed directly currently.

The diagram above shows that the Primary AX DB is copied into the Secondary AX DB for reporting purposes. However, the Primary and Secondary DB cannot be accessed directly in the Live system. The Entity Store, however, can be accessed either via OData feeds or by synching with an Azure Database.

Creating a Data Entity

Creating a Data Entity is easy. Start by logging into your VM. Start Visual Studio 2015 as an Administrator. 

Continue reading →

When not to use Management Reporter

Financial Reports, Management Reporter, Microsoft Dynamics AX

Management Reporter is easy to use to create Management Reports and it does not require a developer to do it. But there are times when Management Reporter is not the right tool for the job.

Here are some reasons why you should not use Management Reporter:

1. The data you need is not in General Ledger or Budgets module in Dynamics AX

This is kind of the kill all of Management Reporter. If your report asks for things not in the General Ledger or Budgets, then no matter how hard you try, it cannot be done, even if the data you need is in the subledgers.

Common examples are if you need customer, supplier or project detail. These are stored in the Sales Ledger, Purchase Ledger and Projects module respectively and the data cannot be found in General Ledger. 

Continue reading →

Using Organization Hierarchies as Reporting Unit filters with Management Reporter

Management Reporter, Microsoft Dynamics AX, ,

Well, hello. It has been more than three years since my last blog, since then I have been really busy at work and have a baby in the meantime.

That being said, I wonder how much joy you guys have with playing with MR.

I have been using Dynamics AX and MR for more than 3 years now and I am still able to learn new things. One of them is that MR is able to use the organizational hierarchies of Dynamics AX as the reporting tree.

For those who are clueless, in your organisation, you can define the organisational hierarchies of not just the standard organisation but for all your financial dimensions. The idea is that when you select an organisational hierarchy as the reporting tree, you do not need to create it from scratch. Furthermore, when the hierarchy changes in AX (I dropped the word “organizational” as it just makes typing too long), MR is able to reflect that based on the period selected for the report. I thought that was pretty cool.

What happens when you want to use the hierarchy as a filter in the column definition?

Terry: why in blazers would anyone want to do that?

Well, it so happened that I had a customer who needed to do just that.

Imagine the need to have different columns represent the amounts not at leaf level, but at a middle hierarchy level. Sure, you can filter on dimensions and manually create it, but there is no need to re-invent the wheel.

Continue reading →

Using PIVOT function without a CTE

SQL, SSAS

There are some cases when you need to use a PIVOT function in SQL but you cannot use a CTE. One such case is when you create a named query in a SSAS cube.

To do so, place the first part of the CTE query as a subquery of the Non-CTE query as shown here. Remember to add an alias to the subquery. The PIVOT portion of the code remains the same.

PIVOTwithoutCTE

The basic understanding of the PIVOT function can be found here.

Note: It may increase query performance when joining another table to the pivoted table to use join hints. e.g. LEFT OUTER HASH JOIN

Microsoft Dynamics AX 2012 – Multiple Rows In Demand Forecast Table [FORECASTSALES] for each item

Business Intelligence, Microsoft Dynamics AX, SQL

Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012?

This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table.

Basically the overview line and each exploded line of the same item is stored in the same [FORECASTSALES] table. Therefore, if any measure in this table needs to be aggregated per item, filter out just the overview rows. You can do this by filtering the column [EXPANDID] = 0.

Continue reading →

Management Reporter Security

Business Intelligence, Management Reporter, Microsoft Dynamics AX

Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here.

In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped by how assigning different roles to users in Management Reporter does not quite turn out as they had expected. Both times I felt I could only give a partial answer. Thus, I decided to do a little experiment to find out the truth once and for all… or until Microsoft comes up with the next Rollup I suppose!

In this topic, I will cover:

Management Reporter is able to integrate with different flavours of Microsoft Dynamics but in this topic I will cover integration with Microsoft Dynamics AX 2012 only, and only for up to RU5, simply because that is what is available to me for my experiment!
Continue reading →

Conditional Coloured Bar Charts in Excel with Upper and Lower Targets

Business Intelligence, Excel

I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility.

It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which means I need to expand the depth of the topic.

Here is what I want to achieve:

How do I get the conditional colour formatting of the bars in the chart?
Continue reading →

What is Microsoft Management Reporter for Dynamics AX?

Business Intelligence, Management Reporter, Microsoft Dynamics AX

It is a collaborative and interactive reporting solution from Microsoft for finance data.

View more here:

Management Reporter 2012 Overview

How is it different from standard SSRS?

It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to share via lync or sharepoint, and to insert comments at defined points of the report.

The other massively useful function is the ability to drillthrough all the way to the Dynamics AX entry.

The other functions I think which are useful are:

  • Multilanguage support
  • NO IT involvement required once setup
  • auto-incremental update of data (NO ETL required)
  • Multisource consolidation
  • Integration with Dynamics AX

The documentation for Management Reporter is available now on Technet.

http://technet.microsoft.com/en-us/library/dn435963.aspx

Note:

Microsoft SQL Server 2014 is not currently supported.

Minimum database requirements:

  • Microsoft SQL Server® 2005 Express Edition with Service Pack 4
  • Microsoft SQL Server 2008 or newer is required for the data mart database, system database, and the company  database for the ERP.

Introducing Microsoft Dynamics AX 2012 with SSRS Reports

Business Intelligence, Microsoft Dynamics AX, SSRS

Hi all,

I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business.

Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to support BI in an AX environment.

SSRS is now the primary reporting platform for Microsoft Dynamics AX since the 2012 incarnation.

Here’s the quote from Books On Line:

Microsoft SQL Server Reporting Services is now the primary reporting platform for Microsoft Dynamics AX. The default, preconfigured reports that are included with Microsoft Dynamics AX run on the Reporting Services platform.

Good news is that everything you know about SSRS can be applied into the world of Dynamics AX now. All the flexibility of being able to tweak every little detail is available to the report.

On the other hand, it would be useful for AX developers to know more about the SSRS development environment which includes:

  • SSRS services provides two URLs – one for the Report Manager, and one for web services
  • Administrator rights is required for deployment of reports
  • Report Security has to be defined in Report Manager. A new security role is available for this. It is called: DynamicsAXBrowser

For SSRS developers, it is useful to know the following:

  • The No. 1 rule is not to edit the report without saving the project back to the Application Object Tree (AOT). This is because if the AOT is rebuilt, all changes will be wiped out.
  • To create a new report, create a new query in the AX Development Workspace first before creating a new report.
  • Create a new report in Visual Studio 2010 and use the new Microsoft Dynamics AX Template called Report Model.
  • Creating a new report this way requires a whole different process than a normal SSRS report. (Which I will discuss in a separate post)
  • You can find out the SSRS url either from the AX Development Workspace (Tools -> BI Tools -> Report servers, or the usual way – via SSRS Configuration Manager.
  • Save the report project into AOT.
  • Then deploy the reports. There are two ways – via Visual Studio (which provides more output messages which is useful for troubleshooting, or via the AX Development Workspace)

For more information of SSRS in Microsoft Dynamics AX 2012, visit: http://technet.microsoft.com/en-us/library/dd309644.aspx

First take on PowerPivot

Business Intelligence, PowerPivot

What is PowerPivot for Excel?

PowerPivot brings the power of Data Modelling into your PC.

Traditionally, the DataWarehouse is built on a database server (SQL) and a data model is built on top of it (SSAS). Then, a front end is used to present the data to users.

Now, data can come from different sources and pulled into the PowerPivot engine, relationships defined, calculations done and reports presented on Excel.


Continue reading →