Wednesday, October 11, 2017

Cisco Information Server (CIS) and MS OLAP

Ok, so my recent issue was integrating CIS with Microsoft OLAP cubes. The normal way to do this is to set up MS OLAP (SSAS) with a website and use XML/A to connect. I started down that path and decided to try a different tack based some StackOverflow articles. Here is my approach that worked great:


The trick here is to use a Linked Server in the middle of the connection. For this, you need a SQL Server
instance.


Steps



Open SSMS. Create a new query










In a SQL Server instance, set up a Linked Server. Here is an example:

EXEC sp_dropserver 'TEST_OLAP'
EXEC master.dbo.sp_addlinkedserver
@server='TEST_OLAP',
@srvproduct='MSOLAP',
@provider='MSOLAP',
@datasrc='APSEPXXXX',
@catalog='Commitments'




EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='TEST_OLAP',
@useself='False',
@locallogin=NULL,
@rmtuser='MS\XXXXX',
@rmtpassword='***********'
GO

• Create a query to go against the Linked Server and pass the MDX query:
select
"[Policy].[Master Policy].[Master Policy].[MEMBER_CAPTION]" AS MasterPolicy,
"[Due Date].[Calendar].[Year].[MEMBER_CAPTION]" AS Year,
"[Due Date].[Calendar].[Quarter Name].[MEMBER_CAPTION]" AS Quarter,
"[Due Date].[Calendar].[Month Name].[MEMBER_CAPTION]" AS Month,
"[Policy].[Policy Number].[Policy Number].[MEMBER_CAPTION]" AS Policy,
"[Measures].[Commitments Closed Within TAT]" As CommitmentsClosedWithinTAT
from openquery
(
'TEST_OLAP,
'SELECT NON EMPTY { [Measures].[Commitments Closed Within TAT] } ON COLUMNS, NON EMPTY
{ ([Policy].[Master Policy].[Master Policy].ALLMEMBERS *
[Due Date].[Calendar].[Month Name].ALLMEMBERS *
[Policy].[Policy Number].[Policy Number].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Due Date].[Calendar].[Year].&[2015] } ) ON COLUMNS FROM [Commitments
Advocate4Me])'
)


• NOTE: If you need to determine the odd MDX variables coming back, run this query:
select * INTO #TEMP_A4M from openquery
(
'TEST_OLAP,
'SELECT NON EMPTY { [Measures].[Commitments Closed Within TAT] } ON COLUMNS, NON EMPTY
{ ([Policy].[Master Policy].[Master Policy].ALLMEMBERS *
[Due Date].[Calendar].[Month Name].ALLMEMBERS *
[Policy].[Policy Number].[Policy Number].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Due Date].[Calendar].[Year].&[2015] } ) ON COLUMNS FROM [Commitments
Advocate4Me])'
)


select * from tempdb.sys.columns where object_id =object_id('tempdb..##TEMP_A4M');


  1. Set up your SQL Server Data Source
• Put it into Packaged Query and do Design by Exampl

PROC FORMAT Error with CNTLIN

What I was doing:

Using a SQL Server table to feed a PROC FORMAT using CNTLIN.

Error message seen:

ERROR: Cannot mix missing and nonmissing values in the same range

Cause:

START column was defined as character and END column was defined as a numeric (decimal)


Wednesday, June 28, 2017

CTRL+Z does not generate EOF in Windows 10

In Windows 10, when I was trying to generate an EOF for a Java program, the CTRL+Z did not work. After doing some research (and help from forum members), it was determined that there is a new option in the cmd prompt in Windows 10:






Check that, relaunch cmd, and it then works.

Monday, December 30, 2013

SAS and Bing Maps API

Here is a method for using SAS and Bing Maps:

  1. Signed up for a key on Bing Maps API. Create a basic key and copy it somewhere. I will demonstrate it in the SAS code as [BINGKEY] since I cannot share mine publicly: https://www.bingmapsportal.com/

  2. Use the following code:
  3. %let z1=47.64054;
    %let z2=-122.12934;
    %let BINGKEY=%NRSTR(&key=[BINGKEY]);

    filename x url "http://dev.virtualearth.net/REST/v1/Locations/&z1.,&z2.?o=xml&BINGKEY";
    filename z "c:\temp\BingMap.xml";

    data _null_;
    infile x ;
    file z ;
    input;
    put _INFILE_;
    run;

  4. Parse the xml that is produced as needed. You can also output it as JSON if you change the word xml in the URL to json.

Monday, November 18, 2013

SAS EG Add-Ins and WPF

Well, I finally made progress on getting my dynamic WPF add-in working. This was in large part to Chris Hemedinger helping out with some questions. What I have learned, I want to share so it is not lost.

WPF works fine with EG except for some graphics issues found when the form is displayed in EG. Not a major issue, a minor one.

The main thing is that it works, runs dynamically using web services on the backend, uses MEF (very cool Framework), and saves us a lot of effort.

Here are some of Chris' suggestions.

How to debug

 
To be effective at debugging, you need a professional version of Visual Studio.  The Express editions don't allow you to debug an application unless you built that app (the EXE) with Express.  

 

Assuming that you have the a Pro version, you can use this approach:

-          Copy the DLL and PDB (symbols) file into the directory where you are putting Custom tasks.

-          Start EG as normal, see the custom task in the Add-Ins menu.

-          Before you launch the task, use Visual Studio to attach to the process.  With your add-ins project loaded, select Debug->Attach to Process.  Find SEGuide.exe and attach.

-          Set breakpoints in your add-ins code as needed.  You can also select Debug->Exceptions, check "Break when exception is thrown" for managed code.

 

I don't recommend throwing exceptions from your task unless you also have other code that will catch the exception.  If you allow an exception to bubble up to the SEGuide.exe app, it won't be good for your end user.  Potentially work will be lost in the project.  It's best to catch the exceptions that you can control/react to, and contain them in your add-in.

Adding Nodes Programmatically



To prevent the task from appearing in the process flow, implement it as a modeless window and return ShowResult.Cancel immediately after your task window is displayed.  I think the catalog explorer example does this, as does the SAS Macro Viewer example.  You will have to add logic to guard against project changes (while your task is running) and re-entrant logic (prevent more than one instance of your task per session).  

 

Now, to add a program node to the project – that's trickier.  If you create a program file (.SAS file on disk), you can trick EG into opening it in the current session.  See the "Program Manager (C#)" example that you can find here:

 


 

Beyond that, there is not a supported method for creating a new program node (or any node) programmatically via the task APIs.

 

Monday, October 28, 2013

SAS EG "add-in is an invalid file type"

I am working on a SAS EG Add-In right now. As part of it, I encountered this error:

"add-in is an invalid file type"

Ok. Great. How do I solve it?

For my project, it turned into a missing dll. Simple? Yes, but it took hours to track down. Hopefully this post helps out the next person.

Chris H. suggested the following means of finding the cause of an error:



You can "debug" the issue with the logging.config approach, which I show in the custom tasks book.  The generated log file will show the DLLs that are loaded and provide more details about problems encountered.

To turn on logging:


Thanks Chris.

Thursday, March 14, 2013

SOAP Error on Encoding

If you are trying to get a web service operational and encounter errors similar to the following:


SOAPUI error:

The message could not be processed. This is most likely because the action 'http://tempuri.org/ISas9Users/GetSasUsers' is incorrect or because the message contains an invalid or expired security context token or because there is a mismatch between bindings. The security context token would be invalid if the service aborted the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase the Receive timeout on the service endpoint's binding.

SAS error:

ERROR: org.springframework.ws.client.WebServiceTransportException: Cannot process the message because the content type 'text/xml;

charset=UTF-8' was not the expected type 'application/soap+xml; charset=utf-8'. [415]

WCF Error: The caller was not authenticated by the service.
 
 
Make sure you use basicHttpBinding vs wsHttpBinding in your web.config if using WCF (which I am). 
 
 
Also, you may need to check your XML and make sure you have the encoding defined in the XML header:
 
 

Cisco Information Server (CIS) and MS OLAP

Ok, so my recent issue was integrating CIS with Microsoft OLAP cubes. The normal way to do this is to set up MS OLAP (SSAS) with a website a...