SSIS development tools compatibility can be difficult to navigate. Here is an excellent article on that:
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
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.
Issue in SSIS: Unable to cast COM object of type
To fix the problem do the following:
- Open the command prompt (run it as administrator)
- Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
- Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll
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 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),’’)
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.
So – if the authentication does not work or does not show the option you expect, make sure D365O is up and running.
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.
- Click on [Edit credentials]
- Select oAuth2 – and log in with the correct credentials.
- 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:
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.
Here is how to group by a field in PowerQuery and list the first value of another column: