Author: Root

SAS ODBC-connection directly in SAS

The SAS libname statement below shows you how to make an ODBC-connection to an SQL-server through ODBC without first having to create the ODBC-connection in the OS.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; TRUSTED_CONNECTION=yes" schema=<Name of schema in database> access=readonly;

‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

‘access=readonly‘ is done for precaution. If the user is only suppose to read the data then this should be set on the datasource – not on the libname.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; UID=<name of user>; PWD=<Password for user>" schema=<Name of schema in database> access=readonly;

It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).

proc sql noprint;

connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; TRUSTED_CONNECTION=yes");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;

‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

proc sql noprint;

connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; UID=<Your username>; PWD=<Your password>");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;

It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.

proc sql noprint;

connect to odbc (DSN=<Your DSN name>&nbsp;USER=<Your username>; Password=<Your password>);
execute (

<Your SQL sentence>

) by odbc;
disconnect from odbc;
quit;

Below is a link to an article from SAS explaining different ways of using SAS ODBC-connection directly in SAS . It stats that NOPROMT can’t be used with the SQL Server ODBC Driver, but I haven’t experienced that.

Usage Note 52777: Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server database without configuring an ODBC data source name
http://support.sas.com/kb/52/777.html

Use SAS with multiple cores in a CPU – and other hardware considerations

SAS does use multiple cores in some of it’s procedures eg. PROC SORT, PROC SQL (order by and group by) and others. The use of parallel execution was introduced with SAS version 9. A description of how SAS uses threads can be found here.

It is possible to test the use and impact of executing SAS using multiple cores in parallel by using the OPTION NOTHREADS. This option tells SAS, not to use parallel execution when possible. OPTION THREADS is on by default. It is also possible to do some experimenting with OPTIONS CPUCOUNT where it is possible to set the number of cores for SAS to use in it’s execution of the SAS-program.
Experience says that SAS works best in a 4 core environment. Which probably has something to do with  Amdahls law. That is of course not the case if your system handles multiple users or you start multiple SAS-processes eg. through the use of RSUBMIT with multiple tasks in one SAS-session or a script that starts multiple SAS-sessions.
If you are operating in an Intel environment the type of processor you use can have a big impact on the performance of your SAS-program. Intel XEON X5000-series performs a lot better than the Intel XEON X7000-series. Rule of thumb is that for big jobs a high clockfrequency is preferable.

Another rule of thumb is to use 4GB of RAM per core on a 64bit system and 2GB of RAM per core on a 32bit system of course depending on your individual use of the available memory. Paging of the memory should of course be avoided.

Reading from SSD disks connected to the server is of course very fast but still expensive considering that you probably need some sort of RAID configuration. Writing temporary files and log files to SSD disks could give a performance advantage, but with long seriel writes cheaper disk maybe connected through a SAN might give a better performance than SSD disks.

Securing the Tabular BI Semantic Model

Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.

Download here

Opening PowerPivot data with Excel in Sharepoint 2010

It’s is not possible to use Excel through Sharepoint 2010 on PowerPivot data, if you are using a claims based authentication provider.

Although classic mode sign-in is not required for the more common data access scenario (where PowerPivot data is extracted from the same Excel workbook that renders it) do not attempt to use PowerPivot for SharePoint with SharePoint web applications that are configured to use other authentication providers. Doing so will result in a connection failure whenever users try to connect to PowerPivot workbooks as an external data source.
Source: http://msdn.microsoft.com/en-us/library/ee210621.aspx

If anyone has a solution to this please let me know by commenting this post.
One possible solution could be to use a third party OLAP-browser for example the freeware version of Ranet Uilibrary Olap or the commercial version of Ranet Uilibrary Olap.

Bulk delete in MS-SQL server

If you want to simulate bulk-deleting in Microsoft SQL-server this can do the trick. I use it to avoid getting a transaction log that fills up the harddisk.
The example uses a stored procedure, but you don’t have to do this. The stored procedure is given a year-variable that is used in the SQL-sentences that selects what has to be deleted.

USE [YOUR_DATABASE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
AS
BEGIN

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.

SET NOCOUNT ON;

— Insert statements for procedure here
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT

SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
COMMIT TRAN
END
END
GO

It’s excecuted in the following manner: exec sp_BULK_DELETE_AREA_YEAR ‘2011’

I haven’t figured out how to use a variable that contains the tablename. So I don’t think it’s possible. You have to write the table name.
The solution was found here.

Enable xp_cmdshell in Microsoft SQL-server

Disclaimer: Beware of the possible security threat when you enable xp_cmdshell usage. You can also take a look at this article, it’s at bit more complicated, but a lot safer.
The article pretty much walks you through, what you need to do to use SQLCLR. I tried it out and got stuck when i got a security threat. I got the threat because you have to give the assemblies that you use (‘alter assemblies…’) a given state – safe, unsafe and external in the SQL-server.

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

It’s also possible to use ‘sp_oacreate’.

Problem playing MKV on WDTV (Gen1)

If your WDTV (Gen1) freezes when you want to play a MKV-movie you can try the following.

  1. Download and install mkvtoolnix.
  2. Start MKVmerge GUI
  3. Load/Add the MKV-file your having problems playing.
  4. Change the video-stream. Press the video-stream. Go to the ‘Extra options’-tab and change the compression to ‘None’.
  5. Do the same for the audio-stream.
  6. Press ‘Start muxing’

Solution found at here.

Sony Ericsson X10 Mini Pro as WIFI-hotspot eg. for an Ipad

Below is a guide as to how it is possible to turn your Sony Ericsson X10 Mini Pro into a WIFI-hotspot eg. for your Ipad.

Disclamer: You need to root your phone. Read all about the consequences of this action on the Internet before you proceed. The below guide worked for me this doesn’t mean that it will work for you.

  1. On your phone check “debbuging mode” on Setting > Applications > Development
  2. You need to download an install Flashtool.
  3. Go to the folder, where Flashtool gets installed eg. C:\Flashtool\drivers and run Flashtool-drivers.exe to install the correct drivers for Flashtool.
  4. Install the correct drivers for your Sony Ericsson X10 Mini Pro.
  5. Start Flashtool.
  6. Connect your phone to your computer via the USB-cabel and Flashtool will recognize it.
  7. When Flashtool is ready press the open padlock symbol.
  8. Flashtool now roots your Sony Ericsson X10 Mini Pro and reboots it.
  9. After that you will now be able to see the SuperUser icon on your phone.
  10. Go to Android Marked and download ‘Barnacle Wifi Tether’ (http://szym.net/barnacle/).
  11. You probably need to press the ‘Associate’-button in ‘Barnacle Wifi Tether’ on your phone a couple of times when you are connecting with the Ipad before it connects. When it connects ‘Barnacle Wifi Tether’ will notify you and show your connection under ‘Clients’.

I have tried using the WEP-encryption. I doesn’t seem to be able to connect very well. You have to press the “Associate”-button a lot of times. If you leave the connection unencrypted it seems to connect very well. BUT this leaves you with a security issue!
In the settings for ‘Barnacle Wifi Tether’ it is possible to choose ‘Auto Associate’ it will auto associates every 5 seconds until a client connects.