Vipul Shah's SQL Blogs

Want to know Business Intelligence and Business Analytics on Small Data, Big Data, All Data?

2013-06-06T16:08:01Z

Check out these series of videos..

http://www.youtube.com/watch?v=dBkzmwaSRUc A Growing World of Data - Part 1 of a 4 part series on Microsoft's unique approach to Business Intelligence and Big Data - Published on Jun 3, 2013 – 2.53 minutes

http://www.youtube.com/watch?v=UiIVUV8evtk The World of Big Data - Part 2 of a 4 part series on Microsoft's unique approach to Business Intelligence and Big Data - Published on Jun 3, 2013 – 3.11 minutes

http://www.youtube.com/watch?v=yFY5eF9XjBQ Big Data Get Ever Bigger - Part 3 of a 4 part series on Microsoft's unique approach to Business Intelligence and Big Data - Published on Jun 3, 2013 – 2.55 minutes

http://www.youtube.com/watch?v=Lbkuic-Dxww Microsoft and Big Data...Worlds Collide - Part 4 of a 4 part series on Microsoft's unique approach to Business Intelligence and Big Data - Published on Jun 3, 2013 – 2.02 minutes

Great stuff!

How to apply Filter in Data Explorer Formula

2013-05-07T14:29:42Z

I ran into below particular scenario.

My Sample Table:

Desired Output:

In PowerPivot DAX Formula, I could easily achieve this as this -

Actual Formula:

=[Market Value]/SUMX(FILTER(mySampleTable,mySampleTable[PlanName]="ABC"),mySampleTable[Market Value])

How can I achieve this in Data Explorer? Let’s see -

Step1: Open Data Explorer, select table and choose “From Table” option

Step 2:

Filter for value “ABC”

Step 3:

Copy the formula from the formula bar and paste in a notepad (we will use it later)

Step 4:

Remove filter and add a new Custom Column

Step 5: MAGIC – here is the main thing – write a formula in DE!

Insert [Market Value] and then rest of the formula as below (use the copy/paste from previously copied formula for filtering PlanName = “ABC”

You should something like this – Now the new added custom column is showing the % of ABC.

At this point you can accept the query and add into your model – DONE!

Amazing Bing Map Apps

2013-02-01T02:07:00Z

Did you know there are lots of free Bing Map Apps available?

Just go to http://www.bing.com/maps click on More Apps

You will some amazing apps available for free!

Changing System Administrator Account for Master Data Services–would need this after restoring MDS database to another instance of SQL Server

2012-11-06T03:58:33Z

When you try to restore MDS database from one server to another server in a different domain where you don’t share same system administrator account, you will need to change the internal sys admin account for MDS database after the restore is done, so that you can connect to restored DB’s MDS UI.

This article talks about how to change that internal system administrator account residing in mdm.tblUser table.

http://msdn.microsoft.com/en-us/library/ff487048.aspx

To change the administrator account

Open SQL Server Management Studio and connect to the Database Engine instance for your Master Data Services database.
In mdm.tblUser, find the user that will be the new administrator and copy the value in the SID column.
Create a new query.
Type the following text, replacing DOMAIN\user_name with the new administrator's user name and SID with the value you copied in step 2.

EXEC [mdm].[udpSecuritySetAdministrator]

@UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1

Here you will need to know the SID for the account (domain\user_name). To find out the SID, follow the instruction in this article -

http://support.microsoft.com/kb/154599 - How to Associate a Username with a Security Identifier (SID)

Open Registry Editor and navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion \ProfileList
Under the ProfileList key, you will see the SIDs. By selecting each one individually, you can look at the value entry and see what user name is associated with that particular SID.

After this step is performed, you should be able to open up the MDS UI that is configured successfully with restored MDS database.

How to handle Error: “Error - The member code is inactive.” while dealing with Consolidated Member in Master Data Services

2012-03-27T21:34:00Z

I ran into a situation where I accidently deleted a consolidated member through UI (soft delete – marked for Inactive). Later on I received an update for one of the attribute on that member, so I wanted to import that update (import type = 0), but I couldn’t import. I get “The Member code is inactive” error. If it was a leaf member, I could have used Import type = 4 and permanently deleted that member and then use import type = 0 to re-import it back, but instead I followed below approach to re-activate the deleted member -

From home page (UI), go to Version Management
Click on Transactions from top menu
Find that transaction that is marked De-activated and select it
Click on “Revert Transaction” button on top

“What’s New” webpart on SharePoint 2010 - list is not showing up in Target List drop-down?

2012-01-18T19:31:00Z

We have a SharePoint team site which is very heavily used. Lot of changes occur in many lists, libraries, etc. It is sometimes hard to keep up with what’s been changed. One way to keep up is to setup an alert that will send you an email message when the item has changed. But that could flood your mail box. Another alternate is to use “What’s New” webpart that comes with SharePoint 2010 - OOB feature.

Configuring this webpart is very easy – see the screenshot. Only thing I found out is that sometimes you may not see your list in “Target List” dropdown. In order to see your list or library in this dropdown you have to have an index created on column called “Modified”. Make sure you have an index created on “Modified” column. The way to create index on any list or library column is -

go to list or library settings
under the columns section – click on Indexed Columns and click on Create a new Index. Here you can choose “Modified” from the column list and hit create.

Once the index is created on Modified column, you should be able to see the list in a “Target List” dropdown on this webpart’s configuration.

You can find What'New Web part as follows:

Objects Overview in MDM database created by Master Data Services in SQL Server 2012 RC0

2012-01-10T23:51:44Z

There are so many objects in MDM database that gets created when you create a model in Master Data Services. Below outlines the significance of each object:

Schema Name

Type Of Object

Object Name Sample

Purpose/Comments

Created or Used or Referenced in/by User defined process?

Mdm

Tables

Mdm.tbl_N_N_XX

These are internal system tables and should not be altered or used in any queries.

Mdm

Tables

Mdm.tblXXXXX

These are also internal system tables and should not be altered or used in any queries.

Stg

Tables

Stg.EntityName_Leaf

· With SQL Server 2008 R2 onwards, Master Data Services has 1:1 staging leaf table for each entity in MDM.

· Columns in these tables maps to attributes in its corresponding MDM entity.

· It is only used during Entity Based Staging process for loading MDM system – once data is imported (updated/inserted/deleted) from these leaf tables into MDM entities, it can be truncated, as there is no linkage to MDM’s actual data that are showing up in UI.

Yes

Stg

Tables

Stg.EntityName_Consolidated

· These are consolidated members staging tables that maps to consolidated members in MDM

· Just like leaf tables, these are also used only during Entity Based Staging process to import Updates (insert/update/delete) for those consolidated members in MDM

Yes

Stg

Tables

Stg.EntityName_Relationship

· These staging tables are used to import the Parent Child relationship defined in an Explicit Hierarchy.

· Once the Relationships in Explicit Hierarchy imported from this staging table, it can be truncated

Yes

Mdm

Views

Mdm.SubscriptionViewName

Subscription views can be created over following MDM objects:

Object Type	Format Type
Entity	Leaf Members
Consolidated Members
Collection
Collection Members
Explicit Parent Child
Explicit Levels
Derived Hierarchy	Derived Parent Child
Derived Levels

Subscription Views are used to extract MDM data for any downstream application.

Yes

Mdm

Views

Mdm.viw_SYSTEM_xxx

These are system views and should be used or referenced in any downstream application

Stg

Views

Stg.viw_EntityName_MemberErrorDetails

These views are used to review import errors that are captured as part of the Entity Based Staging process.

Yes

Mdm

Stored Proceduers

Mdm.udpXXXXXX

These are system stored procedures generated and used by internal MDM system. These are not intended to be referenced in any downstream application. They should not be use or altered for any MDM queries.

Stg

Stored Procedures

Stg.udp_EntityName_Leaf

Stg.udp_EntityName_Consolidated

Stg.udp_EntityName_Relationship

These procedures are used during Entity Based Staging Process to import (process insert/update/delete based on Import Type Parameter) data from entity’s stage table into corresponding MDM entity or consolidated member or explicit relationship.

Yes

Mdm

User Defined Functions

Mdm.udfXXXXX

These are system functions and should not be used or referenced or altered in any downstream application.

Registering multiple servers in Central Management Server (CMS) using PowerShell Script

2011-06-13T18:10:25Z

Below are the steps using PowerShell script (learned from another Kalyan Yella, Microsoft):

Step 1: Open notepad and copy and paste below PowerShell script and save the file as “RegisterServers.ps1” at c:\scripts\ --- you can save anywhere you want – just make a note of it as you will use this in next step

param($server,$path = ‘SQLSERVER:\SQLRegistration\Central Management Server Group\TestGrp’,[bool]$allowDups=$false) SET-LOCATION $path if (!(Test-Path $(Encode-Sqlname $server))) {New-Item $(Encode-Sqlname $server) -itemtype registration -Value “server=$server;integrated security=true” }

Make sure to change the highlighted GroupName to whatever name you want to keep.

Step 2. Go to SSMS and right click on CMS server and click Start PowerShell – this will open up PowerShell window

Step 3: type following command:

invoke-sqlcmd -Query "select SQLServerName from ServerListTable where SQLServerName like '%DEV%' and SQLVersion like '9.%'" –serverinstance ‘<SQLInstanceWhere ServerListTable is stored>’-database '<DB where ServerListTable is stored>' | %{c:\scripts\RegisterServers.ps1 $_.SQLServerName}

Highlighted T-SQL Query can be anything that can provide you a list of multiple SQL Server Instances that you want to register.

Make sure the entire command below is in one line

Step 4: hit enter

This should register all the SQL Servers resulted from above T-SQL SQL query by using RegisterServers.ps1 script in to appropriate folder structure you choose in step 1. Verify by going to Registered Servers and expanding Central Management Server group

Importing Data from MS Access 2010 64-bit .accdb database into SQL Server using SSIS

2011-06-07T15:17:50Z

Problem:

You can’t connect using Native OLEDB \Microsoft Jet 4.0 OLEDB Provider. It fails with following error message:

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Users\vishah\Documents\myDB.accdb'.

Solution:

Download 2007 Office System Driver: Data Connectivity Components from here - http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
then choose Microsoft Office 12.0 Access Database Engine OLE DB Provider and point it to your .accdb file.

SSRS 2008 R2, SharePoint 2010 - Report Back button doesn't work beyond 1st level of drill through

2011-06-07T14:02:23Z

Problem:

I have three reports -

ParentReport - X, ChildReport- A, ChildReport - B of A.

Drill through from X to A - works great

Back Button from A to X – works great

Drill through from X to A to B – works great

Back button from B to A – works Great, but after that A to X Back Button doesn’t work. I see yellow exclamation mark at the bottom left corner of the page and clicking it gets me this errors:

Solution:

You want the SharePoint add-in update - 2008R2_RTM_RSShrPnt_CU7_2507770_10_50_1777_x64 from http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2507770&kbln=en-us