Tag Archives: microsoft

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

June 19, 2012

Managing hierarchies being used in IBM Cognos TM1 – precautions

By Greeny Dangerous

TM1 doesn’t like duplicate names in hierarchies

IBM Cognos TM1 is an excellent tool when it comes to planning, budgeting and forecasting. It also has hierarchy management capabilities but often you want to import hierarchies and data from an enterprise MDM solution such as Master Data Services (MDS) in SQL Server.

When  importing hierarchies and data to TM1 one must be careful with the business codes, ids and names of the entities being imported to TM1 since it has an interesting “limitation” (or feature, depends on from who you ask): all the elements within a dimension must have not only a unique id but also a unique business code and a unique name. It means that the following cost center source data example cannot be imported because the elements have duplicate names (although the business codes differ):

Type Code Name Parent
Consolidated 1000 Administration
Leaf 1 General 1000
Leaf 2 Purchasing 1000
Consolidated 2000 Production
Leaf 3 General 2000

To prevent situations like this and prevent having problems when importing data to TM1 one must be sure that there are no duplicate names or codes in the source data. In MDS this situation can be handled nicely by using MDS business rules.

Deploy Business Rules to prevent duplicates

In MDS you can define business rules to automatically keep your data in shape and also make some automatic processing to your data if wanted. Now we want to prevent duplicate names in the cost center data (codes are automatically unique so we don’t need to think about them).

Force the cost center names to be unique

Force the cost center names to be unique

Now that the business rule is defined and published we can apply it to the data itself:

Apply the business rule to existing data

Apply the business rule to existing data

As we can see two cost centers have the same name “General” and that violates the business rule definition.

Two cost centers have the same name

Two cost centers have the same name

The red flag indicates that there is a problem with the data and it needs to be fixed. You can also define business users to have notification emails when the validation rules fail so that someone can instantly fix the issues before they are causing errors in the downstream systems such like TM1.

After the the names are fixed and the business rules are applied again the situation is OK and the data meets the quality needs of TM1 and its ready to go!

No duplicates exist - the data is good to go

No duplicates exist – the data is good to go

Cost center hierarchy after fixing the data

Cost center hierarchy after fixing the data

Extra notes

Although we can prevent duplicate names within leafs and consolidated members independently the business rules cannot be used to prevent them in both simultaneously: you can still have leaf member with name “General” and a consolidated member with name “General”. And that’s also bad for TM1.

In most cases the hierarchy data goes first into a data warehousing system before it’s loaded to TM1. If that’s the case you can brute force the uniqueness of the names by storing both the consolidated and leaf members into a single table and then defining a unique constraint on the name column. That will keep your data well-formed. It’s not the most beautiful way to do it but a very effective one. Let’s hope that we can tackle this situation also in the future versions of MDS.

1 Comment   |  tags: , , , , , , , , , | posted in TM1

May 11, 2012

Master Data Services 2012 is a must – Huge productivity improvements

By Greeny Dangerous
Compared to the 2008R2 version you can now save even 75% of development time.

Microsoft just released the new SQL Server 2012 version. There are lots of new features included but one major improvement deals with Master Data Services (MDS) application. It has now moved to its second major release and it is now shipped with SQL Server 2012 Enterprise and Business Intelligence licenses. I don’t go into details that much but what I want to point out here is that the new 2012 version of MDS has remarkable improvements when it comes to user experience and the productivity of development work.

Biggest and maybe the most important improvement is the new Excel-add-in that can be used to modify data but also to create new entities and to import new data from scratch. Where the 2008R2 version relies only on the (clumsy) database staging tables in 2012 one can now use Excel to import batches of new data. This is of course good news for new users that are already familiar with Excel. The second big improvement is the new Silverlight-based web user interface. It has been completely renewed and I can tell that now it is really ready for end users. Unfortunately the initial 2008R2 version still suffers from nasty bugs and the usability is quite poor.

What I will now show you is a comparison between MDS2008R2 and MDS2012 and how they differ from each other when importing new data and creating entities.

Creating new entities and importing data

Let’s face it: I have a simple Product – structure that  grabbed from the Adventureworks – sample database. 5 entities stored in separate csv-files:

  • ProductCategory
  • ProductSubCategory
  • ProductModel
  • Color
  • Product

What we will next do is

  • Create a new Model to store the entities
  • Create 5 entities and the corresponding attributes + relationships: Product – SubCategory – Category, Product – Color and Product – ProductModel
  • Import data into entities
  • Create a derived hierarchy ProductCategory – ProductSubCategory – Product

Master Data Services 2008 R2

Create entities manually

In the old version we have to do it the hard way: first create the new entities in the web UI and then import the data via database staging tables using a proper ETL tool. I preffered SSIS.

This phase took me approximately 6 minutes to complete. Now I have the entities structure ready in MDS so it’s time to import some data.

Import data by using SSIS and the MDS staging tables

This is the most time-consuming part of the process. You have to create separate data flows for each entity and you also must do some unpivoting of the data when importing entity attributes. After each data flow you must go into the Import/Export  – page in the web UI and start the importing process manually. (OK, you can do all this automatically by using the web service calls and all that stuff but this approach is still the fastest way at this point).

Importing the attributes for each entity is bit tricky as you need to unpivot the source data into separate rows.

After a while and some serious SSIS-work we have 5 entities and a nice Product hierarchy set up in MDS:

The bad thing is that it took almost ~ 30 minutes to accomplish all this. With all the work that you have to do in the MDS Web UI we end up with appoximately total 40 minutes of work.

Master Data Services 2012

Create entities and import data

Now we’re talking. With MDS2012 you don’t actually have to separate the creating entities and importing data phases because now you can do them both at the same time! Starting with ProductCategory entity we just import the csv data into our Excel sheet and then connect to the MDS server and hit the Create Entity – button on the MDS ribbon.

Then we just choose right columns for code and name usage and we’re ready. As easy as that !

Handle domain-based attributes

The first thing that came to my mind when hearing about the new Excel-add-in was that if it was capable of dealing with domain-based attributes. And guess what, it sure is. Like we did with ProductCategory entity we do the same for ProductSubCategory. Notice here that there is a relation between SubCategory and Category and we have to handle it also correctly. That can be managed by using the Attribute Properties – functionality in the MDS ribbon.

Choose Constrained list (Domain-based) as the attribute type and populate values straight from the recently created ProductCategory entity:

Nice and easy! We now have succesfully created ProductCategory and ProductSubCategory entities and formed a relationship between. All this in ~ 3 minutes of time. After repeating the same steps for the rest of the entities (ProductModel, Color and Product) we end up with the same result as in 2008R2 but only in 10 minutes of development time. Saving 75% of time compared to the process in MDS2008R2.


MDS 2008R2 MDS 2012
Create model 1 min 1 min
Create entities 6 min –
Import data 30 min 10 min
Other 4 min –
Total 41 min 11 min

Extra mentions about MDS2012

When talking about how to automate data imports that’s where the staging tables are coming into picture also in MDS 2012. Now it’s also more user friendly since you don’t have to import entity rows and corresponding attributes in separate tables and you don’t have to unpivot the columns into rows in the tblStgAttributes – table. In MDS2012 there is a separate table for each entity and it is 1:1 with the entity definition.

What really completes the whole package is the also brand new Data Quality Services (DQS) application that works nicely together with MDS. More about DQS later …

Summary

When Microsoft launched the initial version of MDS in May 2010 it was a classic “first version” of the (recently acquired)  product: missing features and minor bugs here and there. Now the second major release really finds its place in the hearts of the users and developers since there are lots of really good improvements that make it a better product.

So, if you are planning to start a fresh MDS project my honest advice is: don’t start start with 2008R2, do it with SQL Server 2012.

-GD

Leave a comment   |  tags: , , , , , | posted in Master Data Services