Author: Agust

FileReview effeciency

Used FileReview to review 180 sql files. Started last Friday and finished yesterday. Had fun as this was the first time I took on a large review and experienced how efficient my little FileReview tool is. Took me less then 4 hours to review all the files. Granted, yes, many of the files are standard and didn’t need any review. But FileReview helps with that as it is very efficient with a keyboard shortcut to mark a file OK and see the next file.

In the next version there will be tooltips and smaller font in text boxes.

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.