tag:blogger.com,1999:blog-80356873503447104142024-03-08T06:39:33.989-05:00Angelia's SQL ReflectionsAngelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-8035687350344710414.post-78346486335539783702015-01-22T08:30:00.001-05:002015-01-22T08:34:05.972-05:00Azure Point to Site VPN with SQL Server and FileTables<br />
There are times when you have a hybrid environment and it’s not feasible to join Azure to your current active directory and you need a solution that allows Azure access to your on premise environment. While it’s straight forward to access a sql server instance residing on an Azure virtual machine it requires more effort for Azure to access your on premise sql server. One solution is a point to site VPN.<br />
Configuring an on premise instance of sql server to be accessible from an azure vm requires the following<br />
<ol>
<li>Azure Point to Site VPN
<li>SQL Server Configured for Remote Access
<li>FileTable Configured for Remote Access</li>
</li>
</li>
</ol>
<strong>Azure Point to Site VPN:</strong><br />
Microsoft gives in depth instructions for setting up a point to site VPN here:<br />
<a href="https://msdn.microsoft.com/en-us/library/azure/dn133792.aspx" title="https://msdn.microsoft.com/en-us/library/azure/dn133792.aspx">https://msdn.microsoft.com/en-us/library/azure/dn133792.aspx</a><br />
Once you’ve set up the VPN connect to the your virtual network. This blog will use MarinerNet.<br />
We’ll need to get the internal ip address of the on premise machine. Open Network and Sharing and click on MarinerNet to bring up the Status dialog.<br />
<a href="http://lh5.ggpht.com/-n4O8VtxijXQ/VMD7Lc2TTnI/AAAAAAAAAHQ/EirZzVwvn0s/s1600-h/image%25255B73%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-PMyo8rxt34Y/VMD7Ly0G0nI/AAAAAAAAAHY/fhdLSsaRkeg/image_thumb%25255B47%25255D.png?imgmax=800" height="439" style="border-width: 0px; display: inline;" title="image" width="523" /></a> <br />
Click details in the status box and retreive the internal ip address assigned by Azure.<br />
<a href="http://lh3.ggpht.com/-Yh5fDVdorKk/VMD7MU8tpPI/AAAAAAAAAHg/BJ8Nw30xUB8/s1600-h/image%25255B8%25255D.png"><img alt="image" border="0" src="http://lh3.ggpht.com/-pb2wDdEzxME/VMD7M-fXYYI/AAAAAAAAAHo/uGHookovoP8/image_thumb%25255B4%25255D.png?imgmax=800" height="276" style="border-width: 0px; display: inline;" title="image" width="376" /></a> <br />
<br />
<strong>Configure On Premise Sql Server for Remote Access:</strong><br />
In order to access the machine from Azure you’ll need to allow remote connections for the sql server instance and open the port that sql server is listening on.<br />
To allow remote connections open SSMS and open server properties. Click connection and check Allow remote connections.<br />
<a href="http://lh6.ggpht.com/-sKmlRT2Ifs4/VMD7NWdYM9I/AAAAAAAAAHw/FGoV29fY-nc/s1600-h/image%25255B13%25255D.png"><img alt="image" border="0" src="http://lh4.ggpht.com/-NNmL1wQmAtg/VMD7OMfrFTI/AAAAAAAAAH0/tE5S3XLz6Iw/image_thumb%25255B7%25255D.png?imgmax=800" height="379" style="border-width: 0px; display: inline;" title="image" width="459" /></a> <br />
Now that we’ve allowed remote connections we will need to allow the Azure VM through the firewall. This can be done by opening up port 1433.<br />
Go to Control Panel\System and Security\Windows Firewall and click Advanced Settings<br />
<br />
<a href="http://lh5.ggpht.com/-e5hDJlpKNSQ/VMD7Ocrr3eI/AAAAAAAAAIA/wXy2KUrXB7o/s1600-h/image%25255B18%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-M57lGlZ2c2w/VMD7Own23AI/AAAAAAAAAIE/Yp4F6lFiolM/image_thumb%25255B10%25255D.png?imgmax=800" height="331" style="border-width: 0px; display: inline;" title="image" width="557" /></a> <br />
<br />
In Windows Firewall and advanced security click Inbound Rules and then under Actions New Rule. The dialog below will appear for rule type select port.<br />
<a href="http://lh4.ggpht.com/-isib6HoJqmA/VMD7PSZObAI/AAAAAAAAAII/_NcYVxQN_4c/s1600-h/image%25255B29%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-9ODIkx4DPPE/VMD7P4UKEsI/AAAAAAAAAIY/VJlA6WDZf_c/image_thumb%25255B17%25255D.png?imgmax=800" height="409" style="border-width: 0px; display: inline;" title="image" width="547" /></a> <br />
<br />
Then for protocol and ports enter a specific local port which is 1433.<br />
<a href="http://lh3.ggpht.com/-_V51LFjToPk/VMD7QqsM_GI/AAAAAAAAAIc/G5DsGm3QrP0/s1600-h/image%25255B34%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-tNkmPuvgVUs/VMD7RKTBSGI/AAAAAAAAAIk/lZO0yXLyQeQ/image_thumb%25255B20%25255D.png?imgmax=800" height="395" style="border-width: 0px; display: inline;" title="image" width="601" /></a> <br />
Next select allow the connection when a machine attempts to connect on port 1433.<br />
<a href="http://lh4.ggpht.com/-UOApKp1TfcM/VMD7RocRRKI/AAAAAAAAAIs/sBfv6QZk3qM/s1600-h/image%25255B46%25255D.png"><img alt="image" border="0" src="http://lh4.ggpht.com/-K9lvTrhaxUQ/VMD7R-p4zdI/AAAAAAAAAI4/YLWe-pW3xuA/image_thumb%25255B26%25255D.png?imgmax=800" height="371" style="border-width: 0px; display: inline;" title="image" width="648" /></a> <br />
The rule will always apply<br />
<a href="http://lh4.ggpht.com/-paQGDpsP7B0/VMD7SYH2dFI/AAAAAAAAAI8/eWTxwCEElq4/s1600-h/image%25255B50%25255D.png"><img alt="image" border="0" src="http://lh3.ggpht.com/-NBUWoQgAyVI/VMD7SzVbnDI/AAAAAAAAAJE/VPoRVg3PMzo/image_thumb%25255B30%25255D.png?imgmax=800" height="469" style="border-width: 0px; display: inline;" title="image" width="667" /></a><br />
Now give it a name. I used Sql Port Inbound<br />
<a href="http://lh3.ggpht.com/-iZYOfFlyr0I/VMD7TOvK83I/AAAAAAAAAJQ/x4uyCG_bv5w/s1600-h/image%25255B54%25255D.png"><img alt="image" border="0" src="http://lh5.ggpht.com/-rQm6v4WyJs4/VMD7TsOu8qI/AAAAAAAAAJU/60KDnCshrdo/image_thumb%25255B34%25255D.png?imgmax=800" height="362" style="border-width: 0px; display: inline;" title="image" width="708" /></a> <br />
Connectivity has now been set up between the Azure VM and the on Premises Sql Server.<br />
Using the internal ip address obtained earlier and sql server authentication because the Azure VM and on Premises Sql Server are in two different domains we can connect to the On Premises Sql Server from the Azure VM.<br />
<a href="http://lh4.ggpht.com/-3dcuxCZI74o/VMD7TxEk6PI/AAAAAAAAAJc/6nXH3Kq5M0Q/s1600-h/image%25255B60%25255D.png"><img alt="image" border="0" src="http://lh3.ggpht.com/-aXCqP_UiwAs/VMD7UR99ypI/AAAAAAAAAJk/E3iXYqRCdHo/image_thumb%25255B38%25255D.png?imgmax=800" height="385" style="border-width: 0px; display: inline;" title="image" width="673" /></a> <br />
<br />
Now that we’re able to connect to sql server from Azure let’s also add access to the file tables on the on premise sql server instance.<br />
This requires allowing remote access to filestreams and creating a local user on the on premise sql server.<br />
On the FileStream tab of Sql Server properties in Sql Server Configuration Manager check the Allow remote clients access box.<br />
<a href="http://lh6.ggpht.com/-AKvF93Urh60/VMD7U_TyKMI/AAAAAAAAAJs/yN5Lqp9_S9c/s1600-h/image%25255B65%25255D.png"><img alt="image" border="0" src="http://lh3.ggpht.com/-DxqhgY5W4_g/VMD7VFi2yWI/AAAAAAAAAJ0/DFR_GTgbi-Q/image_thumb%25255B41%25255D.png?imgmax=800" height="339" style="border-width: 0px; display: inline;" title="image" width="548" /></a> <br />
Create a local user and give them access to the FileTable in Sql Server.<br />
All you need to do is plug in the URL <br />
<a href="file://10.0.0.2/mssqlserver/AdventureWorks2012/AdventureWorks2012FS">\\10.0.0.2\mssqlserver\AdventureWorks2012\AdventureWorks2012FS</a><br />
and enter the local user credentials when prompted.<br />
<a href="http://lh6.ggpht.com/-URX-U03JMWA/VMD7V6bOToI/AAAAAAAAAKA/-et68Dr7IUg/s1600-h/image%25255B72%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-NaQ9X7MbtGk/VMD7WY8oflI/AAAAAAAAAKE/083HsIirFeA/image_thumb%25255B46%25255D.png?imgmax=800" height="362" style="border-width: 0px; display: inline;" title="image" width="595" /></a> <br />
<br />
The main drawback to this approach is that the Internal IP Address can change each time the vpn connection is reestablished. One way around this is a VPN for each machine for example create MarinerNet1 for Sql Server1 that only has 10.0.0.2 as a possible address and MarinerNet2 for Sql Server2 that only has 10.0.0.3 as a possible address.Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-18549535569230811632012-01-27T06:11:00.001-05:002012-01-27T06:11:10.237-05:00Using SQL Partitions and the $partition function to define Cube Measure Group Partitions<p> </p> <p>Recently I partitioned a fact table in Sql Server and not surprisingly when it came to the cube this table’s measure group also needed to be partitioned. The sql server partition was a sliding month partition with each of the 12 months ,the previous year, and an Archive partition. </p> <p>Instead I allowed Sql Server to handle the partitioning for me by creating the measure group partitions based on views using the $partition function.</p> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><span style="color: #0000ff">Select</span> *<br><span style="color: #0000ff">From</span> Fact<br><span style="color: #0000ff">Where</span> $Partition.SlidingMonth(TransactionDate) <span style="color: #0000ff">in</span> (15,14,13)</pre><br></div><br /><p>The first three months were partitions 15,14,13 etc.</p><br /><p>I’ve also used the $Partition function to keep cube measure groups of relatively equal size.</p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><span style="color: #0000ff">Select</span> *<br><span style="color: #0000ff">from</span> Fact<br><span style="color: #0000ff">Where</span> $Partition.SlidingMonth(TransactionDate) % 4 = 2</pre><br></div><br /><p>Remember to make sure that the partitions in Sql Server and the cube are working in tandem and that you’re not adding unnecessary complexity. </p> Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-10054165280633660332011-05-17T19:00:00.003-04:002011-05-17T19:00:01.193-04:00Retrieving SSIS Error Column NamesI recently coded a data load for a client and they requested that I include the column names on which the package errored. The solution I came up with consisted of using the lineageids from the package’s XML. I’ll take you through the code I used.<br />
The first thing I did is load the document and namespace where package path is the location of the package.<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">doc.Load(Variables.PackagePath);
XmlNamespaceManager nsmgr = <span style="color: blue;">new</span> XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace(<span style="color: #006080;">"DTS"</span>, <span style="color: #006080;">"www.microsoft.com/SqlServer/Dts"</span>);</pre><br />
</div><br />
Then I retrieved each node with a lineageid and name using an xpath query.<br />
<br />
<div><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">foreach</span> (XmlNode childnode <span style="color: blue;">in</span> doc.SelectNodes(<span style="color: #006080;">"//*[@lineageId != '' and @name != '']"</span>))</pre></div><br />
<div></div><br />
<div>I then pulled out the TaskName, ColumnName, and LineageID. I included the TaskName because LineageIDs are not unique within a package.</div><br />
<div></div><br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">XmlNode ExecutableNode = childnode.SelectSingleNode(<span style="color: #006080;">"ancestor::DTS:Executable[1]"</span>, nsmgr);
TaskName = ExecutableNode.SelectSingleNode(<span style="color: #006080;">"DTS:Property[@DTS:Name='ObjectName']"</span>, nsmgr).InnerText;
ColumnName = childnode.Attributes[<span style="color: #006080;">"name"</span>].Value;
LineageID = Convert.ToInt32(childnode.Attributes[<span style="color: #006080;">"lineageId"</span>].Value);</pre><br />
</div><br />
<div>I then further deduped within the task by only inserting unique taskname, columnname, and lineageid pairs using a hashtable</div><br />
<div></div><br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DistinctColumnKey =LineageID+ColumnName+TaskName;
<span style="color: blue;">if</span> (!DistinctColumn.ContainsKey(DistinctColumnKey))
{
DistinctColumn.Add(DistinctColumnKey,DBNull.Value);
ColumnNamesBuffer.AddRow();
ColumnNamesBuffer.LineageID = LineageID;
ColumnNamesBuffer.ColumnName = ColumnName;
ColumnNamesBuffer.TaskName = TaskName;
}</pre><br />
</div><br />
<div>I usually insert the column names into a cache connection manager and then use throughout the package. Here’s the entire script.</div><br />
<div></div><br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">public</span> <span style="color: blue;">override</span> <span style="color: blue;">void</span> CreateNewOutputRows()
{
Int32 LineageID;
String ColumnName;
String TaskName;
XmlDocument doc = <span style="color: blue;">new</span> XmlDocument();
Hashtable DistinctColumn = <span style="color: blue;">new</span> Hashtable();
String DistinctColumnKey;
doc.Load(Variables.PackagePath);
XmlNamespaceManager nsmgr = <span style="color: blue;">new</span> XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace(<span style="color: #006080;">"DTS"</span>, <span style="color: #006080;">"www.microsoft.com/SqlServer/Dts"</span>);
<span style="color: blue;">foreach</span> (XmlNode childnode <span style="color: blue;">in</span> doc.SelectNodes(<span style="color: #006080;">"//*[@lineageId != '' and @name != '']"</span>))
{
XmlNode ExecutableNode = childnode.SelectSingleNode(<span style="color: #006080;">"ancestor::DTS:Executable[1]"</span>, nsmgr);
TaskName = ExecutableNode.SelectSingleNode(<span style="color: #006080;">"DTS:Property[@DTS:Name='ObjectName']"</span>, nsmgr).InnerText;
ColumnName = childnode.Attributes[<span style="color: #006080;">"name"</span>].Value;
LineageID = Convert.ToInt32(childnode.Attributes[<span style="color: #006080;">"lineageId"</span>].Value);
DistinctColumnKey =LineageID+ColumnName+TaskName;
<span style="color: blue;">if</span> (!DistinctColumn.ContainsKey(DistinctColumnKey))
{
DistinctColumn.Add(DistinctColumnKey,DBNull.Value);
ColumnNamesBuffer.AddRow();
ColumnNamesBuffer.LineageID = LineageID;
ColumnNamesBuffer.ColumnName = ColumnName;
ColumnNamesBuffer.TaskName = TaskName;
}
}
}
</pre><br />
</div><br />
<div><br />
</div>Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com3tag:blogger.com,1999:blog-8035687350344710414.post-6789747736380852512010-11-05T15:23:00.000-04:002010-11-05T15:23:44.087-04:00Using a CTE in a SSRS Hidden Cascading ParameterI had a situation where a client wanted to allow the report developer to tie each year of a report to a different dataset. The user would choose the report year and based on a hidden parameter set by the developer it would use the correct data set. The catch was I couldn’t create anything on the server. The solution I came up with was a hidden cascading parameter using a CTE.<br />
The visible parameter is @AcademicYear with the following parameter labels<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 75px;"><tbody>
<tr><td valign="top" width="73">20062007</td></tr>
<tr><td valign="top" width="73">20072008</td></tr>
<tr><td valign="top" width="73">20082009</td></tr>
<tr><td valign="top" width="73">20092010</td></tr>
<tr><td valign="top" width="73">20102011</td></tr>
</tbody></table>For the hidden parameter I created a dataset called SnapShot with the following query.<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">WITH SnapShots
(AcademicYear, Snapshot) AS
(SELECT '20062007','1'
UNION
SELECT '20072008','2'
UNION
SELECT '20082009','2'
UNION
SELECT '20092010','3'
UNION
SELECT '20102011','4'
)
SELECT AcademicYear, SnapShot
FROM SnapShots
WHERE AcademicYear=LTRIM(RTRIM(@AcademicYearLabel))</pre><br />
</div><br />
<div id="codeSnippetWrapper"><br />
</div><br />
The parameter @AcademicYearLabel is based on @AcademicYear. To create it choose parameters in the DataSet Properties dialog<br />
<br />
<a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TNLFa-q7W8I/AAAAAAAAADg/Oy6Py3V-06M/s1600-h/image7.png"><img alt="image" border="0" height="352" src="http://lh4.ggpht.com/_8UjjfYcs5JM/TNLFbClJ15I/AAAAAAAAADk/DoSkv-BlhXA/image_thumb3.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="551" /></a> <br />
<br />
<br />
Click the <a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TNLFbQRmaFI/AAAAAAAAADo/u-AezTFesQk/s1600-h/image%5B8%5D.png"><img alt="image" border="0" height="25" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TNLFbpSyt1I/AAAAAAAAADs/VdCj3zfjBHM/image_thumb%5B6%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="22" /></a> button to create an expression and choose the following<br />
<br />
Category – Parameters<br />
<br />
Item – All<br />
<br />
Values – AcademicYear<br />
<br />
Under Set Expression for: Value change <br />
<br />
<div><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">=Parameters!AcademicYear<span style="color: #cc6633;">.Value</span></pre></div><br />
<div></div><br />
<div>To</div><br />
<div></div><br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">=Parameters!AcademicYear<span style="color: #cc6633;">.Label</span></pre><br />
</div><br />
<div><br />
</div><br />
<div id="codeSnippetWrapper"><br />
</div><br />
<a href="http://lh3.ggpht.com/_8UjjfYcs5JM/TNLFbgNSCyI/AAAAAAAAADw/yjCstxs61cQ/s1600-h/image17.png"><img alt="image" border="0" height="306" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TNLFb8MpemI/AAAAAAAAAD0/p31IJ9-ZZqU/image_thumb9.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="534" /></a> <br />
<br />
<br />
Click OK. The parameter value should be updated.<br />
<br />
<a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TNLFcIfqOzI/AAAAAAAAAD4/SWXWvbj5-f0/s1600-h/image46.png"><img alt="image" border="0" height="488" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TNLFcSiRS1I/AAAAAAAAAD8/p771Pf7EohI/image_thumb26.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="542" /></a> <br />
<br />
<br />
Now we’ll create a parameter named @SnapShot to go with the dataset. Set the <em>Name</em> and <em>Prompt</em> to SnapShot, <em>Data Type</em> to Text and <em>Parameter Visibility</em> to Hidden. <br />
<br />
<a href="http://lh4.ggpht.com/_8UjjfYcs5JM/TNLFcjmDuuI/AAAAAAAAAEA/Lp99SErayKI/s1600-h/image47.png"><img alt="image" border="0" height="409" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TNLFc4r55DI/AAAAAAAAAEE/wUc1ddS1Bok/image_thumb27.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="545" /></a> <br />
<br />
<br />
Choose <em>DefaultValues</em>. Select <em>Get values from a query</em> and choose the SnapShot dataset and SnapShot Value field.<br />
<br />
<a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TNLFdLlLcpI/AAAAAAAAAEI/sEDuMyS_Lkc/s1600-h/image53.png"><img alt="image" border="0" height="590" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TNLFdUH7VDI/AAAAAAAAAEM/mAqm_hcdGMc/image_thumb33.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="543" /></a><br />
<br />
<br />
<br />
<br />
<br />
The report is now customizable by dataset.Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-45292332245946756452010-10-26T19:15:00.000-04:002010-10-26T19:15:00.443-04:00SSIS Cache Transform as Source Query during For Loop<p> </p> <p>Recently I had a relatively slow performing source query within a for loop. The for loop ran approximately 12 times each time running this query. I solved the problem by calling the query once, caching the results, and performing look ups instead of executing the query again.</p> <p>Here’s the control flow</p> <p><a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvDNdt9wI/AAAAAAAAACo/ZgnA709Ylmo/s1600-h/image2.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvDenIuvI/AAAAAAAAACs/Z9H5sycqWqw/image_thumb.png?imgmax=800" width="226" height="243"></a> </p> <p>Going into DFL Cache Data</p> <p><a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvDgNfiYI/AAAAAAAAACw/F9yTxmA4afs/s1600-h/image5.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvEUhcBFI/AAAAAAAAAC0/1EBzpqLa7QE/image_thumb1.png?imgmax=800" width="225" height="243"></a> </p> <p>In order to perform a lookup that returns all of the relevant rows the query for OLE_SRC School History Src needs to have a unique identifier.</p> <div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><span style="color: #0000ff">SELECT</span> ROW_NUMBER() <span style="color: #0000ff">OVER</span> (<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> RAND()) ID, *<br><span style="color: #0000ff">FROM</span> ComplexQuery</pre><br></div><br /><p></p><br /><p>Since I’m going to use year as the parameter in the for loop I’m placing the Cache Connection Manager index on ID and YearID.</p><br /><p><a href="http://lh3.ggpht.com/_8UjjfYcs5JM/TMcvEqFxOzI/AAAAAAAAAC4/Dr_bBFbBBVY/s1600-h/image11.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvEzV-tKI/AAAAAAAAAC8/--AyIb2lKg4/image_thumb5.png?imgmax=800" width="567" height="356"></a> </p><br /><p>Now that I’ve filled the cache I’m going to loop by year over the dataflow DFL Import DimSchool</p><br /><p><a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvDNdt9wI/AAAAAAAAADA/of4jkKxvfz8/s1600-h/image%5B1%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvDenIuvI/AAAAAAAAADI/av2BdMwi1T0/image_thumb%5B7%5D.png?imgmax=800" width="187" height="160"></a> </p><br /><p>Here’s DFL Import DimSchool</p><br /><p><a href="http://lh3.ggpht.com/_8UjjfYcs5JM/TMcvGCMr5OI/AAAAAAAAADM/zmbleep73ZA/s1600-h/image14.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TMcvGRreGcI/AAAAAAAAADQ/WHxnXq9atgI/image_thumb6.png?imgmax=800" width="212" height="244"></a> </p><br /><p>Next generate a list of numbers with the for loop variables. To do this create a variable called SQLCommand. Set EvaluateAsExpression to True with the expression as</p><br /><div><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">"<span style="color: #0000ff">WITH</span> Num1 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">UNION</span> <span style="color: #0000ff">ALL</span> <span style="color: #0000ff">SELECT</span> 1),<br>Num2 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num1 <span style="color: #0000ff">AS</span> X, Num1 <span style="color: #0000ff">AS</span> Y),<br>Num3 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num2 <span style="color: #0000ff">AS</span> X, Num2 <span style="color: #0000ff">AS</span> Y),<br>Num4 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num3 <span style="color: #0000ff">AS</span> X, Num3 <span style="color: #0000ff">AS</span> Y),<br>Num5 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num4 <span style="color: #0000ff">AS</span> X, Num4 <span style="color: #0000ff">AS</span> Y),<br>Num6 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num5 <span style="color: #0000ff">AS</span> X, Num5 <span style="color: #0000ff">AS</span> Y),<br>Nums (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> ROW_NUMBER() <span style="color: #0000ff">OVER</span>(<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> n) <span style="color: #0000ff">FROM</span> Num6)<br><span style="color: #0000ff">SELECT</span> n ID, " + (DT_WSTR, 4) @[<span style="color: #0000ff">User</span>::_Year] + " YearID<br><span style="color: #0000ff">FROM</span> Nums<br><span style="color: #0000ff">WHERE</span> n <= 100000"</pre></div><br /><div> </div><br /><div>@{User::_Year] is the variable used in the for loop so the value of YearID changes with each iteration.</div><br /><div> </div><br /><div>Choose Data access mode as SQL command from variable and select SQLCommand as the variable name. It results in the following query.</div><br /><div> </div><br /><div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><span style="color: #0000ff">WITH</span> Num1 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">UNION</span> <span style="color: #0000ff">ALL</span> <span style="color: #0000ff">SELECT</span> 1),<br>Num2 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num1 <span style="color: #0000ff">AS</span> X, Num1 <span style="color: #0000ff">AS</span> Y),<br>Num3 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num2 <span style="color: #0000ff">AS</span> X, Num2 <span style="color: #0000ff">AS</span> Y),<br>Num4 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num3 <span style="color: #0000ff">AS</span> X, Num3 <span style="color: #0000ff">AS</span> Y),<br>Num5 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num4 <span style="color: #0000ff">AS</span> X, Num4 <span style="color: #0000ff">AS</span> Y),<br>Num6 (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> 1 <span style="color: #0000ff">FROM</span> Num5 <span style="color: #0000ff">AS</span> X, Num5 <span style="color: #0000ff">AS</span> Y),<br>Nums (n) <span style="color: #0000ff">AS</span> (<span style="color: #0000ff">SELECT</span> ROW_NUMBER() <span style="color: #0000ff">OVER</span>(<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> n) <span style="color: #0000ff">FROM</span> Num6)<br><span style="color: #0000ff">SELECT</span> n ID, 2000 YearID<br><span style="color: #0000ff">FROM</span> Nums<br><span style="color: #0000ff">WHERE</span> n <= 100000</pre><br></div><br /><div>and the following output</div><br /><div> </div><br /><div>ID YearID</div><br /><table border="1" cellspacing="0" cellpadding="2" width="400"><br /><tbody><br /><tr><br /><td valign="top" width="200">1</td><br /><td valign="top" width="200">2000</td></tr><br /><tr><br /><td valign="top" width="200">2</td><br /><td valign="top" width="200">2000</td></tr><br /><tr><br /><td valign="top" width="200">3</td><br /><td valign="top" width="200">2000</td></tr></tbody></table><br /><div><br>The lookup is performed on the ID and YearID</div><br /><div> </div><br /><div><a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TMcvGtHjYjI/AAAAAAAAADY/BiH8rQi-pAQ/s1600-h/image19.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/_8UjjfYcs5JM/TMcvHMoyuxI/AAAAAAAAADc/oesZKJCIwxs/image_thumb9.png?imgmax=800" width="462" height="321"></a> </div><br /><p>I now have the same records I would’ve gotten by executing the query using the YearID as a parameter.</p> Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-76388802859309019302010-10-19T19:15:00.001-04:002010-10-19T20:40:58.513-04:00Majority Late Arriving Fact Lookups in SSISUsually when I load data into a data warehouse I retrieve only the changes. Since changes are normally applied to the most recent records doing a lookup on the natural key of the current record and a partial lookup for any that are not associated with that record for type 2 works out well. I recently had a situation where I needed to reprocess the entire table for every run. We won’t go into why this was the case. Needless to say it’s not good. Consequently performance was horrendous because 70% of the lookups were partial. <br />
My solution was to use a Merge and Conditional Split to look at the entire dimension table.<br />
<a href="http://lh4.ggpht.com/_8UjjfYcs5JM/TL22AA1K2WI/AAAAAAAAACQ/sK79QhKp7vE/s1600-h/image%5B3%5D.png"><img alt="image" border="0" height="462" src="http://lh6.ggpht.com/_8UjjfYcs5JM/TL22ApSS69I/AAAAAAAAACU/wb0QcLHoXZk/image_thumb%5B2%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="521" /></a> <br />
Let’s start with the dimension (OLE_SRC Dimension). We’ll use DimStudent as the dimension. Here’s the query I used<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> StudentID, StudentNaturalKey, EffectiveStartDate,
<span style="color: blue;">COALESCE</span>((<span style="color: blue;">SELECT</span> <span style="color: blue;">MIN</span>(EffectiveStartDate) <span style="color: blue;">FROM</span> DW.DIMstudent <span style="color: blue;">where</span>
EffectiveStartDate>s.EffectiveStartDAte <span style="color: blue;">and</span> StudentNaturalKey=s.StudentNaturalKey),<span style="color: #006080;">'12/31/2099'</span>) NextEffectiveStartDate
<span style="color: blue;">FROM</span> DW.DimStudent s
<span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> StudentNaturalKey</pre><br />
</div><br />
<br />
I’m pulling the surrogatekey (StudentID), Natural Key (StudentNaturalKey) , EffectiveStartDate, and determining the NextEffectiveStartDate instead of using EffectiveEndDate because the data warehouse may have gaps or overlap in the dates. I’m going to join on the NaturalKey in the Merge Transformation so I’m using it to order by.<br />
<br />
This is the source import query<br />
<br />
<div><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SELECT</span> <span style="color: blue;">DISTINCT</span> StudentNaturalKey, RecordDate
<span style="color: blue;">From</span> Import.Student <span style="color: blue;">WITH</span> (NOLOCK)
<span style="color: blue;">Order</span> <span style="color: blue;">by</span> StudentNaturalKey</pre></div><br />
<div></div><br />
<div>I’m pulling back the NaturalKey and RecordDate from the source and ordering by StudentNaturalKey for the Merge Transformation.</div><br />
<div></div><br />
<div>Here’s the Merge Transformation joining on natural key</div><br />
<div></div><br />
<div><a href="http://lh5.ggpht.com/_8UjjfYcs5JM/TL22AnRNGII/AAAAAAAAACY/pPbprOgUl6E/s1600-h/image6%5B3%5D.png"><img alt="image" border="0" height="553" src="http://lh4.ggpht.com/_8UjjfYcs5JM/TL22BOJya-I/AAAAAAAAACc/Tz1KBL2yq4A/image6_thumb%5B2%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="569" /></a> </div><br />
<div></div><br />
<div>Next there’s the conditional split with the following condition to determine the correct record</div><br />
<div></div><br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">ISNULL(RecordDate) || ISNULL(StudentID) || (RecordDate >= EffectiveStartDate && RecordDate < NextEffectiveStartDate)</pre><br />
</div><br />
<br />
If RecordDate is null then the source record has no date and consequentially there is no corresponding record in the dimension table. If StudentID is null then there was no corresponding record in the dimension. Otherwise it checks to see if the RecordDate is between the EffectiveStartDate and the NextEffectiveStartDate.<br />
<br />
I then load the matching records into a cache connection manager. This isn’t the only way but because of the complexity of the transformation dataflow I’d have to use the sort transformation for the merges so caching and then using the lookup transformation performed much better.<br />
<br />
<a href="http://lh3.ggpht.com/_8UjjfYcs5JM/TL22Bq6LW2I/AAAAAAAAACg/mun_UPcIbNI/s1600-h/image1%5B2%5D.png"><img alt="image" border="0" height="522" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TL22B3TCsiI/AAAAAAAAACk/NolDTeiONsI/image1_thumb%5B1%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="582" /></a> <br />
<br />
The cache consists of the natural key, record date, and StudentID. I look up on the natural key and record date to get the surrogate key. This allows me to keep the number of records to a minimum as records are often loaded in batches with the same record date.Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-85809941957090750272010-10-05T19:15:00.001-04:002010-10-19T14:33:46.814-04:00Missing IndexesI’m back from vacation. It was wonderful. Here’s the code I use to help me get a jump on indexes that may need to be created before I get complaints about system performance.<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SELECT</span>
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) <span style="color: blue;">AS</span> improvement_measure,
<span style="color: #006080;">'CREATE INDEX [missing_index_'</span> + <span style="color: blue;">CONVERT</span> (<span style="color: blue;">varchar</span>, mig.index_group_handle) + <span style="color: #006080;">'_'</span> + <span style="color: blue;">CONVERT</span> (<span style="color: blue;">varchar</span>, mid.index_handle)
+ <span style="color: #006080;">'_'</span> + <span style="color: blue;">LEFT</span> (PARSENAME(mid.<span style="color: blue;">statement</span>, 1), 32) + <span style="color: #006080;">']'</span>
+ <span style="color: #006080;">' ON '</span> + mid.<span style="color: blue;">statement</span>
+ <span style="color: #006080;">' ('</span> + ISNULL (mid.equality_columns,<span style="color: #006080;">''</span>)
+ <span style="color: blue;">CASE</span> <span style="color: blue;">WHEN</span> mid.equality_columns <span style="color: blue;">IS</span> <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span> <span style="color: blue;">AND</span> mid.inequality_columns <span style="color: blue;">IS</span> <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span> <span style="color: blue;">THEN</span> <span style="color: #006080;">','</span> <span style="color: blue;">ELSE</span> <span style="color: #006080;">''</span> <span style="color: blue;">END</span>
+ ISNULL (mid.inequality_columns, <span style="color: #006080;">''</span>)
+ <span style="color: #006080;">')'</span>
+ ISNULL (<span style="color: #006080;">' INCLUDE ('</span> + mid.included_columns + <span style="color: #006080;">')'</span>, <span style="color: #006080;">''</span>) <span style="color: blue;">AS</span> create_index_statement,
migs.*, mid.database_id, mid.[object_id]
<span style="color: blue;">FROM</span> sys.dm_db_missing_index_groups mig
<span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> sys.dm_db_missing_index_group_stats migs <span style="color: blue;">ON</span> migs.group_handle = mig.index_group_handle
<span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> sys.dm_db_missing_index_details mid <span style="color: blue;">ON</span> mig.index_handle = mid.index_handle
<span style="color: blue;">WHERE</span> migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
<span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC</pre><br />
</div><br />
You’ll find queries like it all over the internet but not necessarily an explanation of what it’s telling you. The SQL Server DMVs are based on the same concepts used in query plans and query optimization.<br />
<br />
<u>sys.dm_db_Missing_Index_Group_Stats</u> – Updated By Every Query Execution<br />
<br />
<ol><li>Avg_Total_User_Cost – A number representing the cost of queries for which the index could have been used</li>
<li>Avg_User_Impact – Percentage by which the average query cost would drop if index was implemented</li>
<li>User_Seeks – Number of seeks caused by queries for which this index could have been used</li>
<li>User_Scans – Number of scans caused by queries for which this index could have been used</li>
</ol><br />
<u>sys.dm_db_Missing_Index_Details</u> – Updated Every Time Query is Optimized by the Query Optimizer<br />
<br />
<ol><li>Statement – Table where the index is missing</li>
<li>Equality_Columns – Columns used in equality predicates (Column=’a’)</li>
<li>Inequality_Columns – Columns used in a predicate that’s anything except equality such as ></li>
<li>Included_Columns – Columns need to cover the query</li>
<li>Database_ID – Database</li>
<li>Object_ID – Table</li>
</ol><br />
The higher the improvement_measure the greater the possibility for improvement. As always with indexes make sure you look at all of the pros and cons for the index.Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-35193537836801658372010-09-14T19:15:00.002-04:002010-09-14T19:15:00.312-04:00Master Data Services Tutorial<blockquote>I’m on vacation this week and next so I’ll just let you know that if you want a quick overview of SQL Server 2008 R2 Master Data Services there’s a series of 15 minute tutorials on msdev.com. Here’s the link<br />
<a href="http://www.msdev.com/Directory/SeriesDescription.aspx?CourseId=155">http://www.msdev.com/Directory/SeriesDescription.aspx?CourseId=155</a><br />
The Master Data Services team also has a blog with links to even more info.<br />
<a href="http://blogs.msdn.com/b/mds/" title="http://blogs.msdn.com/b/mds/">http://blogs.msdn.com/b/mds/</a><br />
My next posting should be Sept. 28th.</blockquote>Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-42095638780874602322010-09-07T19:15:00.001-04:002010-09-07T19:15:00.618-04:00Adding Parameter for Analysis Services Data Source in SSRS<div id="codeSnippetWrapper"></div>I recently need to add a parameter to a report using the Analysis Services Data Source. I didn’t find the process to be straight forward so I’m documenting the steps I took.<br />
<ol><li>Go into the query designer’s MDX query mode. <a href="http://lh3.ggpht.com/_8UjjfYcs5JM/TIUVa0cKl9I/AAAAAAAAABc/A_0DCHwMfo4/s1600-h/image%5B88%5D.png"><img alt="image" border="0" height="401" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TIUVbei6lzI/AAAAAAAAABg/SZPrJN4CRDk/image_thumb%5B135%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="451" /></a> </li>
<li>Click the Query Parameters button on the menu bar. <a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TIUVbQZvXZI/AAAAAAAAABk/Sg6-y8s_EpA/s1600-h/image%5B48%5D.png"><img alt="image" border="0" height="33" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TIUVbvuX56I/AAAAAAAAABo/Jc_-XRvb1I4/image_thumb%5B83%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="25" /></a> </li>
<li>Enter parameter, dimension, hierarchy, and default values. Click OK.<a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TIUVb3fl4II/AAAAAAAAABs/fcOdI8usUrk/s1600-h/image%5B55%5D.png"><img alt="image" border="0" height="264" src="http://lh5.ggpht.com/_8UjjfYcs5JM/TIUVcbCa0QI/AAAAAAAAABw/MyrESAvIcks/image_thumb%5B93%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="589" /></a> </li>
<li>Add the parameter to the query. Click OK. <a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TIUVc95VLbI/AAAAAAAAAB0/hvK1loJBg34/s1600-h/image%5B82%5D.png"><img alt="image" border="0" height="369" src="http://lh4.ggpht.com/_8UjjfYcs5JM/TIUVdc5dmGI/AAAAAAAAAB4/HfXSwzRHs_c/image_thumb%5B126%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="557" /></a> </li>
<li>I don’t want to give the user the choice of every member in the hierarchy so I have to prevent the parameter from auto updating. To do this switch to the report's code view. </li>
<li>Find the entry for the parameter’s dataset and add SuppressAutoUpdate <br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; height: 783px; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 132.38%;"><span style="color: blue;"></</span><span style="color: maroon;">DataSet</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">DataSet</span> <span style="color: red;">Name</span><span style="color: blue;">="EnrolledFromYear"</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Fields</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Field</span> <span style="color: red;">Name</span><span style="color: blue;">="ParameterValue"</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span><span style="color: red;">&lt;</span>?xml version="1.0" encoding="utf-8"?<span style="color: red;">&gt;</span><span style="color: red;">&lt;</span>Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /<span style="color: red;">&gt;</span><span style="color: blue;"></</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Field</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Field</span> <span style="color: red;">Name</span><span style="color: blue;">="ParameterCaptionIndented"</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Value</span><span style="color: blue;">></span>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value<span style="color: blue;"></</span><span style="color: maroon;">Value</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Field</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Field</span> <span style="color: red;">Name</span><span style="color: blue;">="ParameterCaption"</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span><span style="color: red;">&lt;</span>?xml version="1.0" encoding="utf-8"?<span style="color: red;">&gt;</span><span style="color: red;">&lt;</span>Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /<span style="color: red;">&gt;</span><span style="color: blue;"></</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Field</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Field</span> <span style="color: red;">Name</span><span style="color: blue;">="ParameterLevel"</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span><span style="color: red;">&lt;</span>?xml version="1.0" encoding="utf-8"?<span style="color: red;">&gt;</span><span style="color: red;">&lt;</span>Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" /<span style="color: red;">&gt;</span><span style="color: blue;"></</span><span style="color: maroon;">DataField</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Field</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Fields</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">Query</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">DataSourceName</span><span style="color: blue;">></span>EdDWDataMart<span style="color: blue;"></</span><span style="color: maroon;">DataSourceName</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">CommandText</span><span style="color: blue;">></span>WITH MEMBER [Measures].[ParameterCaption] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Enrolled From Date].[Academic Calendar].ALLMEMBERS ON ROWS FROM [EdDW]<span style="color: blue;"></</span><span style="color: maroon;">CommandText</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">rd:DesignerState</span><span style="color: blue;">><</span><span style="color: maroon;">QueryDefinition</span> <span style="color: red;">xmlns:xsd</span><span style="color: blue;">="http://www.w3.org/2001/XMLSchema"</span> <span style="color: red;">xmlns:xsi</span><span style="color: blue;">="http://www.w3.org/2001/XMLSchema-instance"</span> <span style="color: red;">xmlns</span><span style="color: blue;">="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"</span><span style="color: blue;">><</span><span style="color: maroon;">CommandType</span><span style="color: blue;">></span>MDX<span style="color: blue;"></</span><span style="color: maroon;">CommandType</span><span style="color: blue;">><</span><span style="color: maroon;">Type</span><span style="color: blue;">></span>Query<span style="color: blue;"></</span><span style="color: maroon;">Type</span><span style="color: blue;">><</span><span style="color: maroon;">QuerySpecification</span> <span style="color: red;">xsi:type</span><span style="color: blue;">="MDXQuerySpecification"</span><span style="color: blue;">><</span><span style="color: maroon;">Select</span><span style="color: blue;">><</span><span style="color: maroon;">Items</span> <span style="color: blue;">/></</span><span style="color: maroon;">Select</span><span style="color: blue;">><</span><span style="color: maroon;">From</span><span style="color: blue;">></span>EdDW<span style="color: blue;"></</span><span style="color: maroon;">From</span><span style="color: blue;">></</span><span style="color: maroon;">QuerySpecification</span><span style="color: blue;">><</span><span style="color: maroon;">Query</span><span style="color: blue;">><</span><span style="color: maroon;">Statement</span><span style="color: blue;">></span>WITH MEMBER [Measures].[ParameterCaption] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Enrolled From Date].[Academic Calendar].ALLMEMBERS ON ROWS FROM [EdDW]<span style="color: blue;"></</span><span style="color: maroon;">Statement</span><span style="color: blue;">></</span><span style="color: maroon;">Query</span><span style="color: blue;">></</span><span style="color: maroon;">QueryDefinition</span><span style="color: blue;">></</span><span style="color: maroon;">rd:DesignerState</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">rd:AutoGenerated</span><span style="color: blue;">></span>true<span style="color: blue;"></</span><span style="color: maroon;">rd:AutoGenerated</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">rd:Hidden</span><span style="color: blue;">></span>true<span style="color: blue;"></</span><span style="color: maroon;">rd:Hidden</span><span style="color: blue;">></span>
<span style="color: blue;"><</span><span style="color: maroon;">rd:SuppressAutoUpdate</span><span style="color: blue;">></span>true<span style="color: blue;"></</span><span style="color: maroon;">rd:SuppressAutoUpdate</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">Query</span><span style="color: blue;">></span>
<span style="color: blue;"></</span><span style="color: maroon;">DataSet</span><span style="color: blue;">></span></pre><br />
</div><br />
<style type="text/css">
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style></li>
</ol><br />
7. Go back to the report's design view. Right click on the datasource in the Report Data view and choose show hidden datasets. The EnrolledFromYear dataset will appear and can be edited <a href="http://lh6.ggpht.com/_8UjjfYcs5JM/TIUbBhY6hUI/AAAAAAAAACE/Xgto-_8BVjc/s1600-h/image%5B93%5D.png"><img alt="image" border="0" height="196" src="http://lh3.ggpht.com/_8UjjfYcs5JM/TIUbCJCh5MI/AAAAAAAAACI/6Iuu4FQKRAI/image_thumb%5B138%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="508" /></a>Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-69196418114023788312010-08-31T19:15:00.000-04:002010-08-31T19:15:00.221-04:00Sum with empty(null) measuresWhen I was working on a report I ran into a problem when I tried to sum a measure that contained an empty cell. I expected the empty cells to be treated as zero but that wasn’t the case. The result returned was null. Here’s the sample data.<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 400px;"><tbody>
<tr><td valign="top" width="199">District</td><td valign="top" width="199">Attendance Rate</td></tr>
<tr><td valign="top" width="199">A</td><td valign="top" width="199">(null)</td></tr>
<tr><td valign="top" width="199">A</td><td valign="top" width="199">0.0%</td></tr>
<tr><td valign="top" width="199">A</td><td valign="top" width="199">94.5%</td></tr>
</tbody></table>My first solution was just to apply a filter to remove the empty cell from the set.<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">With</span> Member Total <span style="color: blue;">AS</span>
<span style="color: blue;">SUM</span>(EXISTING(FILTER([Attending School].[District], <span style="color: blue;">NOT</span> ISEMPTY(Measures.[Attendance Rate])),Measures.[Attendance Rate])
</pre><br />
</div><br />
<br />
The result was still null. I tried a variety of things to remove the empty cells from the calculated measure or transform them into zero but with no success. The result was always the same, null for the sum instead of 94.5.<br />
<br />
Consequently I moved away from manipulating the calculated member itself and came up with a solution in a subcube. I was able to remove those cells without an attendance rate from the cube before summing.<br />
<br />
<div id="codeSnippetWrapper"><pre id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">WITH</span> MEMBER Total <span style="color: blue;">AS</span>
<span style="color: blue;">SUM</span>(Existing([Attending School].[District]),Measures.[Attendance Rate]))
<span style="color: blue;">SELECT</span> Measures.Total <span style="color: blue;">ON</span> COLUMNS,
[Attending School].[District] <span style="color: blue;">ON</span> <span style="color: blue;">ROWS</span>
<span style="color: blue;">FROM</span> (<span style="color: blue;">SELECT</span> [Enrolled <span style="color: blue;">From</span> <span style="color: blue;">Date</span>].[Academic Calendar].[Academic <span style="color: blue;">Year</span>] <span style="color: blue;">ON</span> COLUMNS,
Filter([Attending School].[District] <span style="color: blue;">NOT</span> ISEMPTY([Measures].[Attendance Rate])) <span style="color: blue;">ON</span> <span style="color: blue;">ROWS</span>
<span style="color: blue;">FROM</span> [EdDW])
<span style="color: blue;">WHERE</span> [Enrolled <span style="color: blue;">From</span> <span style="color: blue;">Date</span>].[Academic Calendar].[Academic <span style="color: blue;">Year</span>].&[20082009]</pre><br />
</div><br />
<blockquote><pre class="code"> </pre></blockquote><a href="http://11011.net/software/vspaste"></a>Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com0tag:blogger.com,1999:blog-8035687350344710414.post-42796285441236872192010-08-26T10:20:00.000-04:002010-08-29T12:06:32.347-04:00IntroI'm a MCITP DBA and MCTS in Business Intelligence who's been working with SQL Server for over 10 years, starting as a VB programmer becoming a SQL Analyst graduating to a SQL Developer and finally a SQL DBA. I'm currently employed by Mariner in Charlotte, NC a company specializing in BI where I hope to gain competancy in Analysis Services to add to my SQL portfolio. While I worked with SSIS and SSRS in my former position SSAS was not a direction they wished to go.<br />
<br />
I've decided to start this blog with the hope that others can learn from my experiences (good and bad). I plan to post once a week.Angelia Thompkinshttp://www.blogger.com/profile/06871300180258368034noreply@blogger.com1