Category Archives: SQL Server

April 20, 2014

TM1: Run SQL Server Job from TI process

By Greeny Dangerous

TI_sql_job_image

TM1 Turbo Integrator is quite limited when it comes to loading data from different sources and transforming and cleaning the data before it can be loaded to cubes. Therefore you might have a dedicated data repository for your TM1 solution for example a data warehouse. Sometimes you need to refresh the DW data before it can be loaded to your TM1 solution. Then you face the question: how to manually execute data warehouse refresh ETL jobs? Of course you can always use SQL Server Management Studio and run the jobs there but if there is a need for the end users to manually refresh the data during working hours it would be nice to provide them a single-platform solution do accomplish this.

So lets face it: maybe you already have some user interface for the end-users to execute some TM1 solution’s internal TI-processes. It could be via TM1 Perspectives web sheets on TM1Web,  straight from TM1 Architect etc. Wouldn’t it be nice to have the ability to run external DW loading processes from the same place? Here is how to accomplish it.

Set up the logins and jobs in SQL Server

TI_sql_job_image_login_properties

First of all you need to ensure that the user account that will be used when from TI process connects SQL Server has all the needed permissions in the SQL Server instance. At least the three following things must be ensured:

  • Login has SQLAgentUserRole checked in the msdb database. Then the login will have permissions on only local jobs and job schedules that it owns
  • Login has the required permissions to access the source files etc. That can be solved by defining a proxy account and I have described it in my previous blog post.
  • The job must be owned by the same user you will use when connecting from TM1. This is because we don’t want to throw admin permissions around and we want the user to have only rights to one or couple of jobs not all of them

 

Set up TI process to run the job: zSQLJob_StartJob

To accomplish we need two different TI processes: one to actually execute the job and one to determine whether the job execution has ended or not. Let’s start with the main process zSQLJob_StartJob. It has one parameter p_sql_job_name which will contain the name of the job that will be executed. The process itself is quite straightforward and here is the complete code:

TI_sql_job_image_zsqljob_start

What it does is basically just opens the ODBC connection to the msdb database of the SQL Server instance and then executes the job. The magic here is that the process won’t finish until the job on SQL Server has finished. To have this functionality we need to poll the msdb database every 5 seconds and find out if the job has finished or not.

Set up TI process to determine the status of the running job: zSQLJob_CheckJobStatus

To find out whether the job has finished or not we just query the job status directly from the msdb database. To do that we use the following SQL query and put it in the “Data Source” tab like this:


SELECT CASE
WHEN EXISTS (
select job.Name, job.job_ID, job.Originating_Server,
activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name = 'JOHIDW_Update_Publishing' ) THEN 1 ELSE 0 END AS JOB_RUNNING_STATE

TI_sql_job_image_zsqljob_checkstatus_datasource

The only magic with this process is to save the single result value to the global variable vnJOBSTATE_global that we defined in the previous master process. If the value is 1 it means that the job is still running and the master process keeps polling the msdb database every 5 seconds. When the job finishes the value turns to 0 and the master process can finish as well.

Conclusion

There may be times when you need to execute SQL Server jobs to make sure your data to be loaded to TM1 is up-to-date. With this example you can do a nice and easy integration between TM1 and SQL Server and allow end-users to execute simple SQL Server jobs if needed.

Advertisement

1 Comment   |  tags: , , , , , , , , | posted in Integration Services (SSIS), SQL Server, TM1

January 9, 2014

SQL Server 2012 Integration Services DLL Hell: Microsoft Visual Studio Tools for Applications Runtime 3.0

By Greeny Dangerous

One of the major drawbacks of the SQL Server Integration Services (SSIS) is the mixture of 32bit and 64bit environments:

  • Design & development time environment (SQL Server Data Tools) is running in 32bit though you can debug the package in 64bit mode
  • Production packages can be run in 32bit or 64bit whatever you choose in the package properties

Well, there are moments when this arrangement feels like a gift from god but for me it was once again a real pain in the ass. I was recently working in a fresh installation of SQL Server 2012 and Data Tools. When I was trying to edit a Script Task in my SSIS package I faced the following error message:

Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core

Fresh installation and everything seems like to be in order but no, the script editor won’t open up. After investigation I found out that when the moon and the stars are aligned perfectly there is a small possibility that you miss one assembly file if the installation of SQL Server and related components is done in the wrong order. The thing is that to be able to run the package in both 32bit and 64bit there has to be same assemblies respectively: ones for 32bit and the others for 64bit. My problem was that I was missing the 32bit version of Microsoft.VisualStudio.Tools.Applications.Core.

From the “Add / Remove Applications” of Windows I noticed that yes, I have the Microsoft Visual Studio Tools for Applications x64 Runtime 3.0 but what i was missing was the x32 version of the assembly. So grabbing the MSI installer file from the installation media (redist/VSTA/runtime/x86/VSTA_RT30.msi) and installing it fixed the problem.

Beware of DLL hell!

 

Leave a comment   |  tags: , , , , , , , , , | posted in Integration Services (SSIS), SQL Server

October 31, 2013

Configuring SharePoint 2013 with Business Intelligence features

By Greeny Dangerous

SharePoint-2013

Installing SharePoint 2013 and SQL Server 2012 are quite straightforward processes. When it comes to configuring them and enabling all the nice Business Intelligence features from scratch is a different thing. I recently took a ride on that Via Dolorosa and completed the whole process once again. Here is what you should remember when going through it yourself.

Chapter I: Prerequisites

Before the whole installation process can begin one must install bunch of SharePoint 2013 installation prerequisites. There is a complete list of prerequisites in TechNet Blog Article so I don’t go through them detailed here. What you should do is download all of them and place them in one directory eg. E:\sp2013_pre and run the following command:


D:\prerequisiteinstaller.exe /SQLNCLi:E:\sp2013_pre\sqlncli.msi /PowerShell:E:\sp2013_pre\Windows6.1-KB2506143-x64.msu /NETFX:E:\sp2013_pre\dotNetFx45_Full_x86_x64.exe /IDFX:E:\sp2013_pre\Windows6.1-KB974405-x64.msu /Sync:E:\sp2013_pre\Synchronization.msi /AppFabric:E:\sp2013_pre\WindowsServerAppFabricSetup_x64.exe /IDFX11:E:\sp2013_pre\MicrosoftIdentityExtensions-64.msi /MSIPCClient:E:\sp2013_pre\setup_msipc_x64.msi /WCFDataServices:E:\sp2013_pre\WcfDataServices.exe /KB2671763:E:\sp2013_pre\AppFabric1.1-RTM-KB2671763-x64-ENU.exe

Pretty much all of the prerequisites can be installed manually but the AppFabric installation and configuration is error-prone so it’s better to leave it (and all of the prerequisites) to the built-in prerequisite installer.

NOTE! The built-in prerequisite installer wants SQL Server 2008 R2 Native client by default. I was using the latest SQL Server 2012 so I tricked the installer and replaced the sqlncli.exe with the SQL Server 2012 Native Client installer (same filename different driver).

Chapter II: Installation and SharePoint Product Configration Wizard

Before installing make sure that you have some domain accounts available for various service accounts used by SharePoint.  Then its pretty much “Next-next-next” : just run through the installer and when it comes to the end of installation please follow the instructions and start SharePoint Product Configuration Wizard.

SharePoint_config_wizard_ (2)SharePoint_config_wizard_ (3)SharePoint_config_wizard_ (4)First we create a new server farm and then specify the database settings for the configuration database and a domain username to access the database followed by giving a secure farm passphrase.

SharePoint_config_wizard_ (5)SharePoint_config_wizard_ (6)SharePoint_config_wizard_ (7)The last thing to do is choose the port for Sharepoint Central Administration Web Application. If you need to choose a specific one now it’s time to do that. For security we go for NTLM.

Chapter III: Configuring the farm in SharePoint Central Administration Web Application

After the Configuration Wizard finishes it’s time to use SharePoint Central Administration Web Application.

First we start the Farm Configuration Wizard and create a dedicated domain user to be used as the Service Account.

Then it’s time to choose which service applications you want to run in your SharePoint Farm. Make sure that you check at least the following services:

  • Excel Services Application
  • PerformancePoint Service Application
  • Secure Store Service

SharePoint_Farm_Config_wizard_ (1) SharePoint_Farm_Config_wizard_ (2) SharePoint_Farm_Config_wizard_ (3)

As it states the actual configuration phase “shouldn’t take long” but when installing to a virtual machine that may not have the appropriate resources this could take long.

SharePoint_Farm_Config_wizard_ (5)SharePoint_Farm_Config_wizard_ (4)

Chapter IV: Create new (Business Intelligence) site collection

After the Farm Configuration Wizard finishes it’s time to create our first site collection. In the create site collection wizard you must provide the name for the site, URL and the template that you’d like to use with your site. As we are creating Business Intelligence solutions we choose “Business Intelligence Center” (under Enterprise tab).

CreateSiteCollection

Now we have our site in place and next thing to do is install the SQL Server PowerPivot for Sharepoint instance.

Chapter V: Install SQL Server PowerPivot for SharePoint instance

For storing user created Excel PowerPivot documents we need to have a separate SQL Server Analysis Services instance installed in PowerPivot for SharePoint mode. Previously this needed to be installed inside the SharePoint farm but with SharePoint 2013 it doesn’t matter where it is installed.

SQL_PowerPivot_SharePoint_ (1) SQL_PowerPivot_SharePoint_ (2)

Just run the SQL Server installer application, choose “Perform a new installation of SQL Server 2012”  and in the “Setup Role” page choose “SQL Server PowerPivot for SharePoint“.

SQL_PowerPivot_SharePoint_ (3) SQL_PowerPivot_SharePoint_ (4) SQL_PowerPivot_SharePoint_ (5)

Rest of the installation is straightforward. Just remember to create and use a specified user account for the Analysis Services service.

SQL_PowerPivot_SharePoint_ (6) SQL_PowerPivot_SharePoint_ (7) SQL_PowerPivot_SharePoint_ (8)

Chapter VI: Manage Excel Services data model settings

Next thing to do is to configure Excel Services settings. Just add the name of the previously installed SQL Server Anaylsis Services (PowerPivot for SharePoint) in the Manage Service Application section. Follow the detailed instructions found from

http://technet.microsoft.com/en-us/library/jj219780.aspx

Chapter VII: Enable Claims to Windows Token Service

If you now try creating a Excel spreadsheet and upload it to SharePoint document collection you may encounter this error message: External Data Refresh Failed.

ExternalDataRefresh

That is because we haven’t configured the Claims to Windows Token service yet. To achieve that follow the instructions found here:

http://technet.microsoft.com/en-us/library/ff487975.aspx

claimstotoken

Chapter VII: Configure EffectiveUserName with Excel Services

We are almost there. Next thing to do is to enable the usage of EffectiveUserName with Excel Services. Briefly the steps to do are:

  • Enable EffectiveUserName property check box in Manage Service Applications
  • Add the Excel Services application user account into the administrators of the SQL Server Analysis Services instance (via SQL Server Management Studio and Analysis Server properties)
  • Now the data refresh should work properly!

For detailed instructions see the following TechNet article: http://technet.microsoft.com/en-us/library/jj938164.aspx

Chapter VIII: Deploy PowerPivot for SharePoint 2013 add-in

To enable server-side data refresh processing, collaboration, and management support for PowerPivot workbooks go and download & install the Microsoft® SQL Server® 2012 SP1 PowerPivot® for Microsoft® SharePoint from here.

Chapter IX: Configure PowerPivot and Deploy Solutions

To get the PowerPivot Gallery, Schedule data refresh, Management Dashboard, and data providers features working properly next we must run the PowerPivot for SharePoint 2013 Configuration tool.

Follow the detailed instructions here: Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1)

PowerPivot_for_sp2013_config (2) PowerPivot_for_sp2013_config (3) PowerPivot_for_sp2013_config (4)

There may be warnings about Secure Store not configured but we don’t need to care about it at this phase.

PowerPivot_for_sp2013_config (5) PowerPivot_for_sp2013_config (6)

All set! Finally make sure you add the PowerPivot Service Application user to the administrators of the Analysis Services instances.

PowerPivot_for_sp2013_config (1)

Chapter X: Add a BI Semantic Model Connection Content Type to a Library (PowerPivot for SharePoint)

Adding the BI Semantic Model Connection Content Type to a libary allows the quick usage of eg. Power View straight from the SharePoint site. Detailed instructions how to add it can be found here.

To avoid future connection problems wise thing to do at this phase is to make sure that all the corresponding SharePoint Service Application user accounts are added as administrators to all the Analysis Services instances that’ll be used with SharePoint.

AdminaccountsSSAS

Chapter XI: Install and configure Reporting Services SharePoint Mode for SharePoint 2013

In today’s world there is a big hassle about Self Service BI and all the nice-looking analyzing tools. Microsoft has them all but there might still be a need for “reporting for the masses” in other words if you need standardized reports that are delivered to plenty of people then Reporting Services comes handy.  Next we go through the steps of installing and configuring Reporting Services SharePoint Mode for SharePoint 2013. For detailed instructions, please find them here:

Install Reporting Services SharePoint Mode for SharePoint 2013

SSRS_for_SP2013_installconfigure_ (1) SSRS_for_SP2013_installconfigure_ (2) SSRS_for_SP2013_installconfigure_ (3)

First we run the SQL Server 2012 installer once again and choose to “Add features to an existing instance of  SQL Server 2012“. In the Feature Selection page you must choose the following:

  • Reporting Services – SharePoint
  • Reporting Services Add-in for SharePoint products

In Reporting Services Configuration page choose Reporting Services in SharePoint Integrated Mode and Install only.

SSRS_for_SP2013_installconfigure_ (4) SSRS_for_SP2013_installconfigure_ (5)

All set, hit install and after a moment the installation is ready.

SSRS_for_SP2013_installconfigure_ (6) SSRS_for_SP2013_installconfigure_ (7) SSRS_for_SP2013_installconfigure_ (8)

Then comes the configuration part. First we create the Reporting Services Service Application in Manage Service Applications section in the Central Administration. You must provide a unique name for the service application and then create a new application pool and a specific user account for running the pool. Also choose the database server where to create the SSRS datastore. For authentication we go with Windows Authentication.

SSRS_for_SP2013_installconfigure_ (9) SSRS_for_SP2013_installconfigure_ (10)

That’s pretty much it and again we wait for SharePoint to get the configuration done.

SSRS_for_SP2013_installconfigure_ (11)

Next we Activate the Power View Site Collection Feature in the Site Collection Administration. Now that we want to leverage the BISM Data models with Power View make sure that the Power View Integration Feature is also activated.

The last thing to do with Reporting Services is to Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).

Chapter XII: Configure PerformancePoint Services

To enable visually nice-looking dashboarding capabilities to SharePoint we must enable and configure PerformancePoint Services.

Configure the unattended service account for PerformancePoint Services

You might hit the error message “The data source cannot be used because PerformancePoint Services is not configured correctly. Additional details have been logged for your administrator” If the PerformancePoint Services Unattended Service Account is not set you must enable it. The setting is located in “Manage service applications” in SharePoint Central Administration under the PerformancePoint Services management page.

PerformancePoint_unattended_service_ (1) PerformancePoint_unattended_service_ (2) PerformancePoint_unattended_service_ (3) PerformancePoint_unattended_service_ (4)

Here is how to do it: Configure the unattended service account for PerformancePoint Services

Check also: http://ericphan.net/blog/2010/4/19/performance-point-server-error-the-unattended-service-accoun.html

Why can’t SharePoint Dashboard Designer 2013 connect to SQL Analysis Services 2012?

By default when running Dashboard Designer and trying to connect to an SQL Server 2012 Analysis Services instance you only get empty values. If you go to Windows Event Viewer you can find out the reason behind this: Dashboard Designer 2013 needs ADOMD.net version 10 support!

Here is some more information on the matter:

Why can’t SharePoint Dashboard Designer 2013 connect to SQL Analysis Services 2012?
Can’t get SSAS databases to appear in Performance Point Dashboard Designer? Check you ADOMD.net version!

This is definitely something that Microsoft should have taken care of in advance. It turns out that Dashboard Designer for 2013 was written for the SQL 2008 R2 Analysis Services clients. So you need to download and add client support for ADOMD.net version 10 from http://www.microsoft.com/en-us/download/details.aspx?id=16978

ado2008

FINAL CHAPTER: Tips n’ Tricks and Troubleshooting

Enable DAX queries with Report Builder

http://sqlblog.com/blogs/marco_russo/archive/2012/01/05/use-parameters-in-your-dax-queries.aspx

Remember to use Data Tools instead of Report Builder since the dialog is not available there.

Publish a Report to a SharePoint Library

http://technet.microsoft.com/en-us/library/bb283155.aspx

SharePoint 2013: The server was unable to save the form at this time

http://www.peneveyre.com/blog/PermaLink,guid,eeacb91f-8cdb-49d1-a59a-59de40ffe18f.aspx

http://sharepointerthilosh.blogspot.fi/2013/03/the-server-was-unable-to-save-form-at.html

 

Leave a comment   |  tags: , , , , , , , , , , , , , , , , , | posted in Analysis Services (SSAS), Reporting Services (SSRS), SharePoint, SQL Server

July 1, 2013

SQL Server 2014 CTP1 In-memory OLTP issues with VirtualBox

By Greeny Dangerous

I just started getting touch to the brand new SQL Server 2014 with my VirtualBox virtual machine. The first thing to to with SQL Server 2014 is to test out the new in-memory OLTP functionality (of course!). So I did and immediately faced challenges. I followed the SQL Server team blog instructions and noticed that “MEMORY OPTIMIZED DATA” -section in Filegroup properties was missing completely. I tried to run the scripts provided and then got this error message:

Msg 41342, Level 15, State 1, Line 5

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors. See SQL Server Books Online for information on supported models.

Looks like I wasn’t the only one around having the same issue because it had to to something with VirtualBox default settings for a new virtual machine. So, if you want to get the new in-memory OLTP functionality working correctly with VirtualBox you need to run the following command with VBoxManage:

VBoxManage setextradata [vmname] VBoxInternal/CPUM/CMPXCHG16B 1

1 Comment   |  tags: , , , , , , , , , | posted in SQL Server