Fetch RemitTo address

Sql to fetch RemitTo address for a vendor:

select vt.PARTITION, vt.DATAAREAID, vt.ACCOUNTNUM, max(v.Address) RemitToAddress, max(v.LOCATIONNAME) RemitToName
from VENDTABLE vt
join DIRPARTYLOCATION p on p.PARTY = vt.PARTY
join DIRPARTYLOCATIONROLE l on l.PARTYLOCATION = p.RECID
join LOGISTICSLOCATIONROLE r on r.RECID = l.LOCATIONROLE
join DIRPARTYPOSTALADDRESSVIEW v on v.PARTYLOCATION = l.PARTYLOCATION
where r.TYPE = 10–We only want remit to
group by vt.PARTITION, vt.DATAAREAID, vt.ACCOUNTNUM–To ensure only one row per vendor in case there are more than one remit-to addresses

oData for SSIS

Microsoft now has oData available as a datasource for SSIS packages. For my version of SSMS, it meant downloading and installing the SQL 2014 feature pack “ODataSourceForSQLServer2014-amd64.msi” file.

This means being able for example to pull in data from F&O or SharePoint, directly into a SSIS package.

SSIS Unable to cast COM object of type

Issue in SSIS: Unable to cast COM object of type

To fix the problem do the following:

  1. Open the command prompt (run it as administrator)
  2. Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
  3. Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll

AX 2012 dimension sql code snippet

To fetch an AX 2012 dimension into a sql report it is sometimes a good choice to prepare a CTE for each dimension and then use the cte in the main query below:

cteRegion as (
select distinct v.partition, v.valuecombinationrecid, b.value, b.name
from DimAttributeOMBusinessUnit b
left join dimensionattributelevelvalueallview v on
v.dimensionattribute = (select top 1 recid from dimensionattribute WHERE DIMENSIONATTRIBUTE.NAME = ‘Region’)
and v.entityinstance = b.recid)

A duplicate attribute key has been found

A fairly common error in SSAS: Analysis Services Execute DDL Task: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing

Reason? The most common one is special characters in a dimension attribute string. The solution is to modify the sql code that loads your data to remove such strings like this:

REPLACE(REPLACE(REPLACE(ColumnName, CHAR(9), ‘’), CHAR(10),’’), CHAR(13),’’)

Powerbi.com authenticating oData on D365O

Working with one of the D365O MS Power BI content packs and modifying the URL where data is fetched, which happens to be on an Azure Virtual Machine (VM). Noticed that the OAuth2 authentication method (which is the one to use) was not available in the drop down list when the VM was down.

PowerBI oData authorization method

So – if the authentication does not work or does not show the option you expect, make sure D365O is up and running.

Watch Movie Online Logan (2017)

Authenticating OData source on powerbi.com

Working on reports published to powerbi.com where the data comes through OData from Azure. Found that oAUth2 has to be used as credentials for each of the datasource on powerbi.com Also, for queries that depend on other queries – you may see that you need to fix the authentication of those before the child is resolved. For example below, you can see that the third from bottom has been fixed, but not the one below. As I proceed fixing the credentials for each one, eventually they will all be fixed.

OData Authentication

  1. Click on [Edit credentials]
  2. Select oAuth2 – and log in with the correct credentials.
  3. If it does not work – select the next one.

On the screen where you pick authentication method you can see the url of the datasource being configured:

OData authentication url

Click here to read MS documentation on this topic.

 

Power Query performance

Power Query is quite fast and the underlying data model engine in PowerBI and Excel has excellent performance. Pragmatic works has a nice pdf with tips. The single best thing to do in favour of performance is to remove any unneeded fields from the core queries. This means to only bring in those fields that are needed, eliminate others from the original query so that they are not brought over in the first place.

Query data from websites into Excel or PowerBI

Power Query works great to extract data from websites. For example, I have a Joomla site that has a list of articles in a table. I wanted to summarize article hits per year.

Using Excel and Power Query:

  1. Fresh Excel sheet – Data->New Query  Put in the path to the website, pick the correct table and load the data.
  2. Take time to set the correct data type for each column. This helps on later stages. Especially ensure that you pick whole numbers for columns that have whole numbers, datetype for such columns etc. Also, remove any unwanted columns etc.
  3. Save and close the query – and your data will be available in Excel. Now, create a PivotTable, join with other data etc.

This process would be very similar for PowerBI.