danieljarolim

IBM has provided a dll to support VBA logging into TM1 using CAMPassport integrated authentication within Excel. The dll is 32 bit only and the supplied excel file does not run in 64 bit Excel.

Hopefully one of these is a permanent link to the article and link https://www.ibm.com/support/pages/uid/swg21959177 https://www.ibm.com/support/pages/tm1-api-excel-vba-coding-and-library-automatically-connect-tm1-server-ibm-cognos-cam-security-mode-4-and-5

These are the steps required to get the xlsm file provided by IBM working in 64 bit office. Or the updated VBA module is in the second file of this gist.

When opening TEST_CAM_CONNECT.xlsm the system throws up errors that the funtions are not compatible with 64 bit office.

• Search Replace "Declare Function" with "Declare PtrSafe Function"

danieljarolim

This is a manual workaround because vpn-policy-routing has intermitent issues and is causing constant dropouts.

This relies on hotplug.d to update the routing table any time the wireguard tunnel resets or reconnects. Anything on the WG_ROUTED_SUBNET will go through the WireGuard interface.

Below: wg is the name given to the wireguard interface setup in luci wg is also the routing table name assigned to 202 WG_ROUTED_SUBNET will get routed through the wg interface

danieljarolim

#region Help:  Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services data flow.
 *
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script component. */
#endregion

#region Namespaces
// Add References: Microsoft.CSharp, System.Net.Http, System.Web.Extensions

danieljarolim

SSIS Range Lookup

Range lookups are used for slowly changing dimensions where there is a need to attach a dimension to a fact based on a key and a date range. There is no stock component to do this but there are three ways to carry out range lookups. The first is a merge join followed by a conditional split pattern. Second is a third party component, and last is a script component.

Merge Join Conditional Split Pattern

This requires both the facts and the joining dimension to be sorted by the dimension's natural key. For large fact tables the sort component is restriceted by available memory. This can be easily overcome by using a sort in the source query and marking the source component as sorted by that column but it means only a single SCD can be used in a data flow. It's also slow. The mertge join multiplies the number of fact rows by the number of SCD entires for each key, and gets reduced again by the following conditional split which drops entries that don't fit in the required date range. It me

danieljarolim

SSIS SalesForce Integration using SOAP

Integration with SalesForce without using a custom component requires use of a Script Component as the data source but is fairly straightforward. If you are not limited with an old version of SSIS and have access to .NET newer than 3.5 then you should try using the Salesforce REST API documented here: https://gist.github.com/danieljarolim/1b6e2c2575f17d8a477f3135d36f99c9

Let the wsdl utility (part of the .NET 3.5 SDK) do the hard work of generating all the required classes with correct field names and data types from the SalesForce WSDL.

Use SalesForce documented login and data extract query patterns to get the data.

Note: using the web service component of SSIS will not work. Two calls are required just to log in.

danieljarolim

SSIS SAP Integration

This describes how to consume data from SAP without using a custom component. There are several prerequisites to get this working.

1. SAP adapter packs from the MS Biztalk server.
2. SAP client DLLs from SAP.
3. Acess to BAPIs in SAP to call.

Step 1 Install ADO.NET SAP Adapter packs

Download the Biztalk server demo (BizTalk Server 2013 R2 Evaluation Edition) from http://www.microsoft.com/en-au/download/details.aspx?id=43381

danieljarolim

Updating Cognos User Preferences Using a Hyperlink

I had a need to bulk update user profiles in Cognos and didn’t have immediate access to a bulk update tool like Motio Pro from http://www.motio.com or the time to try and learn the API.

Googling I found this discussion http://www.cognoise.com/index.php?topic=22530.0 , where some resourceful people managed to dissect the user preference form and translate it from a POST to a GET request, but unfortunately missed a step so it doesn’t quite work (see last post).

I did more desperate digging. Here is the working version. Take any of the three following URLs — depending on the configuration of your Cognos environment — and only append settings that need to be updated.

PS. The CAF firewall needs to be disabled for this to work (disable through cogconfig.bat).

danieljarolim

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using Microsoft.SqlServer.Dts.Pipeline;
using System.Security.Cryptography;
using System.Text;