tag:blogger.com,1999:blog-78678584301651484882024-03-13T08:16:48.743-07:00Daily IT SolutionsDaily problems and solutions for Hadoop, SharePoint, WSS, SSIS, SSAS, SSRS, SQL Server, ASP.NET, PerformancePoint etc.Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-7867858430165148488.post-17022390105767965572015-03-13T13:36:00.001-07:002015-03-13T13:37:18.346-07:00App Timeline Server not starting Or Downgrade Ambari<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">This can also be used as a downgrade guide from Ambari 1.7.0 to 1.6.1.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;"><br /></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">If using HDP 2.1.2 with Ambari 1.7.0 your App Timeline Server does not start, you come to the right place. </span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">Symptoms: running the ATS from Ambari throws:</span></div>
<blockquote class="tr_bq" style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">Fail: Execution of ‘ls /var/run/hadoop-yarn/yarn/yarn-yarn-timelineserver.pid >/dev/null 2>&1 && ps cat /var/run/hadoop-yarn/yarn/yarn-yarn-timelineserver.pid` >/dev/null 2>&1′ returned 1.</span></blockquote>
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">All services work fine. I have set the recommended configuration for HDP 2.1.2</span></div>
<blockquote class="tr_bq">
<span style="font-family: inherit;">yarn.timeline-service.store-class = org.apache.hadoop.yarn.server.applicationhistoryservice.timeline.LeveldbTimelineStore</span></blockquote>
<br />
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">The History Server is running fine. </span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;"><br /></span></div>
<div style="background-color: #fbfbfb; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">Not the ideal solution but it worked for me. Since this is kind of related to Ambari versions I reverted back to 1.6.1, steps:<br style="box-sizing: border-box;" />1. Stopped and removed ambari server and all agents<br style="box-sizing: border-box;" />2. Deleted repo and any directories for ambari<br style="box-sizing: border-box;" />3. Downloaded and installed ambari 1.6.1<br style="box-sizing: border-box;" />4. Re-configured/installed cluster, as HDP version remained the same<br style="box-sizing: border-box;" />5. Formatted namenode and hbase<br style="box-sizing: border-box;" />6. Change the config: </span><br />
<br />
<blockquote class="tr_bq">
<span style="font-family: inherit;">yarn.timeline-service.store-class = org.apache.hadoop.yarn.server.applicationhistoryservice.timeline.LeveldbTimelineStore</span></blockquote>
<span style="font-family: inherit;">7. Start ATS, failed, checked logs for historyserver, error: </span><br />
<br />
<blockquote class="tr_bq">
<span style="font-family: inherit;">Permission denied on /hadoop/yarn/timeline/leveldb.timeline-store.ldb/LOCK</span></blockquote>
<span style="font-family: inherit;">8. Deleted the leveldb-timeline-store.ldb<br style="box-sizing: border-box;" />9. Restarted ATS, worked fine!</span></div>
<div style="background-color: #fbfbfb; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;"><br /></span></div>
<div style="background-color: #fbfbfb; box-sizing: border-box; color: #333333; line-height: 18px;">
<span style="font-family: inherit;">Usually I never got this issue for other cluster installs using HDP 2.2 and Ambari 1.7.0.</span></div>
</div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-10425128313889551442015-03-13T13:27:00.001-07:002015-03-13T13:27:22.203-07:00Changing Storage in Hadoop<div dir="ltr" style="text-align: left;" trbidi="on">
After I installed a HDP 2.1.2 cluster, I noticed that all the nodes were not using the drive partition planned for storage. The Linux boxes had OS partition and data data partition. Assigned during OS install one set to OS and other for data storage.<br />
<br />
Somehow the data storage was not available on cluster installation most probably since it was not mounted. Following are the steps performed to change HDFS storage location, along with any drive configuration needed.<br />
<br />
First format and optimized the partition or drive.<br />
<blockquote class="tr_bq">
mkfs -t ext4 -m 1 -O dir_index,extent,sparse_super /dev/sdb</blockquote>
<br />
Create a mount directory<br />
<blockquote class="tr_bq">
mkdir -p /disk/sdb1</blockquote>
<br />
Mount with optimized settings<br />
<blockquote class="tr_bq">
mount -noatime -nodiratime /dev/sdb /disk/sdb1</blockquote>
<br />
Append to fstab file so that the partition is mounted on boot (very critical)<br />
<blockquote class="tr_bq">
echo "/dev/sdb /disk/sdb1 ext4 defaults,noatime,nodiratime 1 2" >> /etc/fstab</blockquote>
<br />
Add folder for hdfs data<br />
<blockquote class="tr_bq">
mkdir -p /disk/sdb1/data</blockquote>
<br />
Location to store Namenode data<br />
<blockquote class="tr_bq">
mkdir -p /disk/sdb1/hdfs/namenode</blockquote>
<br />
Location to store Secondary Namenode<br />
<blockquote class="tr_bq">
mkdir -p /disk/sdb1/hdfs/namesecondary</blockquote>
<br />
Set these in hdfs-site.xml or through Ambari<br />
<blockquote class="tr_bq">
dfs.namenode.name.dir = /disk/sdb1/hdfs/namenode<br />dfs.namenode.name.dir = /disk/sdb1/hdfs/namesecondary<br />dfs.datanode.data.dir = /disk/sdb1/data</blockquote>
<br />
Set permissions<br />
<blockquote class="tr_bq">
sudo chown -R hdfs:hadoop /disk/sdb1/data</blockquote>
<br />
Format namenode<br />
<blockquote class="tr_bq">
hadoop namenode -format</blockquote>
<br />
Start namenode through ambari or CLI<br />
<blockquote class="tr_bq">
hadoop namenode start</blockquote>
<br />
Start all nodes and services. The new drive should be listed.<br />
<br />
References:<br />
http://www.slideshare.net/leonsp/best-practices-for-deploying-hadoop-biginsights-in-the-cloud<br />
<br />
<br /></div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-88155715005965279322014-12-11T09:21:00.002-08:002014-12-11T09:21:51.918-08:00RStudio setup on Hortonworks Hadoop 2.1 Cluster<div dir="ltr" style="text-align: left;" trbidi="on">
Here is a complete set of steps I performed to set up R and RStudio on a small cluster.<br />
<br />
Installing R and Rstudio<br />
-- R should be installed on the node which have Hive server<br />
-- RStudio can be installed anywhere. (I installed on edge node)<br />
<br />
sudo rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm<br />
sudo yum -y install git wget R<br />
ls /etc/default<br />
sudo ln -s /etc/default/hadoop /etc/profile.d/hadoop.sh<br />
cat /etc/profile.d/hadoop.sh | sed 's/export //g' > ~/.Renviron<br />
<br />
Check latest version of RStudio @<br />
http://www.rstudio.com/products/rstudio/download-server/<br />
(It should have installation steps, follow those)<br />
Listing them here for completion with current release version)<br />
$ sudo yum install openssl098e # Required only for RedHat/CentOS 6 and 7<br />
$ wget http://download2.rstudio.org/rstudio-server-0.98.1091-x86_64.rpm<br />
$ sudo yum install --nogpgcheck rstudio-server-0.98.1091-x86_64.rpm<br />
<br />
Create a new system user and set password<br />
sudo useradd rstudio<br />
sudo passwd rstudio<br />
>> hadoop<br />
<br />
Login to RStudio at http://hostname:8787<br />
<br />
Install required packages either from<br />
In RStudio >> Tool >> Install packages<br />
OR<br />
install.packages( c('RJSONIO', 'itertools', 'digest', 'Rcpp', 'functional', 'plyr', 'stringr'), repos='http://cran.revolutionanalytics.com')<br />
install.packages( c('bitops', 'reshape2'), repos='http://cran.revolutionanalytics.com')<br />
install.packages( c('RHive'), repos='http://cran.revolutionanalytics.com')<br />
<br />
Download latest rmr2 package from:<br />
https://github.com/RevolutionAnalytics/RHadoop/wiki/Downloads<br />
Winscp tar.gz file and install through Rstudio<br />
<br />
### Need to run every time RStudio is initialized or restarted<br />
Set environment variables in RStudio<br />
Sys.setenv(HADOOP_HOME="your hadoop installation directory here e.g. /usr/lib/hadoop")<br />
Sys.setenv(HIVE_HOME="your hive installation directory here e.g. /usr/lib/hive")<br />
XX Sys.setenv(HADOOP_CONF_DIR="/etc/hadoop/conf/") do not execute!<br />
<br />
Sys.setenv("RHIVE_FS_HOME"="your RHive installation directory here e.g. /home/rhive")<br />
This needs to be local directory on the node with hive installed, create one if doesnt exist. The user created (rstudio) have chown -R rights on this local directory.<br />
If not this is the error:<br />
Error: java.io.IOException: Mkdirs failed to create file:/home/rhive/lib/2.0-0.2<br />
<br />
library(RHive)<br />
rhive.init()<br />
rhive.connect(host="IP ADDRESS/Hostname", port=10000, hiveServer2=TRUE)<br />
<br />
If error<br />
Error: java.sql.SQLException: Error while processing statement: file:///rhive/lib/2.0-0.2/rhive_udf.jar does not exist.<br />
check if the jar file is in the said directory and rstudio user has permission on it.<br />
<br />
Hope it helps.<br />
<br />
Cheers!<br />
<br />
References and Thanks:<br />
<a href="http://jsolderitsch.wordpress.com/hortonworks-sandbox-r-and-rstudio-install/">http://jsolderitsch.wordpress.com/hortonworks-sandbox-r-and-rstudio-install/</a><br />
<br /></div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-56725539307973981042013-12-09T08:35:00.003-08:002013-12-09T08:35:33.683-08:00Excel + SQL Server Linked Server Troubleshooting<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Trebuchet MS, sans-serif;">Following is a great article to troubleshoot errors thrown by OLEDB when connecting an Excel file from OPENROWSET query:</span><br />
<h3 class="post-name" style="background-color: white; color: #333333; margin: 7px 0px 18px; padding: 0px;">
<span style="font-family: Trebuchet MS, sans-serif; font-size: small;"><a href="http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx">OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".</a></span></h3>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">Gives step by step resolution to multiple errors like:</span></div>
<div>
<span style="background-color: white; color: red; font-family: 'Courier New'; font-size: 11px; line-height: 18.15625px;">- Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".</span></div>
<div>
<span style="background-color: white; color: red; font-family: 'Courier New'; font-size: 11px; line-height: 18.15625px;">- Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".</span></div>
<div>
<span style="background-color: white; color: red; font-family: 'Courier New'; font-size: 11px; line-height: 18.15625px;">- </span><span style="background-color: white; color: red; font-family: 'Courier New'; font-size: 8pt; line-height: 18.15625px;">SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part</span></div>
<div class="MsoNormal" style="background-color: white; color: #333333; font-family: Arial; font-size: 12px; line-height: 18.15625px; margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="color: red; font-family: 'Courier New'; font-size: 8pt;">of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.<o:p></o:p></span></div>
<div class="MsoNormal" style="background-color: white; color: #333333; font-family: Arial; font-size: 12px; line-height: 18.15625px; margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="color: red; font-family: 'Courier New'; font-size: 8pt;">For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.</span></div>
<div>
<span style="background-color: white; color: red; font-family: 'Courier New'; font-size: 11px; line-height: 18.15625px;"><br /></span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">On client deployments my recurring issue was setting my access to temp folders for service accounts.</span></div>
</div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-17533522372783866832013-11-26T10:50:00.001-08:002013-11-26T10:51:25.433-08:00Build Hierarchy From Delimited String<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Trebuchet MS, sans-serif;">In this post we create a parent child hierarchy (n level) from a delimited string like [Parent_Child_LeafChild].</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:</span><br />
<div class="MsoNormal">
<span style="font-family: Trebuchet MS, sans-serif;">Stage1</span></div>
<div class="MsoNormal">
<span style="font-family: Trebuchet MS, sans-serif;">--Stage2</span></div>
<div class="MsoNormal">
<span style="font-family: Trebuchet MS, sans-serif;">----Stage3</span></div>
<br />
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">DECLARE</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@separator_position</span> <span style="color: blue;">INT</span>
<span style="color: green;">-- This is used to locate each separator character</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">DECLARE</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@array_value</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>1000<span style="color: grey;">)</span><span style="color: green;">-- this holds each array value as it is returned</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">DECLARE</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@separator</span> <span style="color: blue;">char</span><span style="color: grey;">(</span>1<span style="color: grey;">)</span> <span style="color: green;">--Used in WHERE clause</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">declare</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@StageLevel</span> <span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">)</span> <span style="color: grey;">=</span> <span style="color: red;">'Stage1_Stage2_Stage3'</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">declare</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@holder</span> <span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">)</span> <span style="color: grey;">=</span> <span style="color: teal;">@stagelevel</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">SET</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@separator</span> <span style="color: grey;">=</span> <span style="color: red;">'_'</span> <span style="color: green;">--Separator A.K.A.
Delimiter</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">SET</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@StageLevel</span> <span style="color: grey;">=</span> <span style="color: teal;">@StageLevel</span> <span style="color: grey;">+</span> <span style="color: teal;">@separator</span> <span style="color: green;">--append ',' at
the end</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: green; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">-- select
PATINDEX('%[' + @separator + ']%', @StageLevel)</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">Declare</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@Level</span> <span style="color: blue;">int</span> <span style="color: grey;">=</span> 0<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">Declare</span><span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: teal;">@ParentId</span> <span style="color: blue;">int</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"> <span style="color: blue;">WHILE</span> <span style="color: magenta;">PATINDEX</span><span style="color: grey;">(</span><span style="color: red;">'%['</span> <span style="color: grey;">+</span> <span style="color: teal;">@separator</span> <span style="color: grey;">+</span> <span style="color: red;">']%'</span><span style="color: grey;">,</span> <span style="color: teal;">@StageLevel</span><span style="color: grey;">)</span> <span style="color: grey;"><></span> 0<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">BEGIN</span> <span style="color: green;">-- patindex
matches the a pattern against a string</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">SELECT</span> <span style="color: teal;">@separator_position</span>
<span style="color: grey;">=</span> <span style="color: magenta;">PATINDEX</span><span style="color: grey;">(</span><span style="color: red;">'%['</span> <span style="color: grey;">+</span> <span style="color: teal;">@separator</span> <span style="color: grey;">+</span> <span style="color: red;">']%'</span><span style="color: grey;">,</span><span style="color: teal;">@StageLevel</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">SELECT</span> <span style="color: teal;">@array_value</span>
<span style="color: grey;">=</span> <span style="color: grey;">LEFT(</span><span style="color: teal;">@StageLevel</span><span style="color: grey;">,</span> <span style="color: teal;">@separator_position</span> <span style="color: grey;">-</span>
1<span style="color: grey;">)</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">set</span> <span style="color: teal;">@level</span>
<span style="color: grey;">=</span> <span style="color: teal;">@level</span> <span style="color: grey;">+</span>1<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: green;">--select @array_value, @level</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">IF</span> <span style="color: teal;">@level</span> <span style="color: grey;">=</span>1 <span style="color: green;">--This is parent node</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">Begin</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">insert</span> <span style="color: blue;">into</span> <span style="color: teal;">Hierarchy</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">StageLevelName</span><span style="color: grey;">,</span> <span style="color: teal;">StageLevel</span><span style="color: grey;">)</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: teal;">@array_value</span><span style="color: grey;">,</span> <span style="color: teal;">@Level</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">END</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">ELSE</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">BEGIN</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">insert</span> <span style="color: blue;">into</span> <span style="color: teal;">Hierarchy</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">StageLevelName</span><span style="color: grey;">,</span> <span style="color: teal;">StageLevel</span><span style="color: grey;">,</span> <span style="color: teal;">ParentID</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: teal;">@array_value</span><span style="color: grey;">,</span> <span style="color: teal;">@Level</span><span style="color: grey;">,</span> <span style="color: teal;">@ParentId</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">END</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">set</span> <span style="color: teal;">@ParentId</span> <span style="color: grey;">=</span> <span style="color: magenta;">@@IDENTITY</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">select</span> <span style="color: teal;">@ParentId</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: green;">--Moving to end of array</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">SELECT</span> <span style="color: teal;">@StageLevel</span>
<span style="color: grey;">=</span> <span style="color: magenta;">STUFF</span><span style="color: grey;">(</span> <span style="color: teal;">@StageLevel</span><span style="color: grey;">,</span> 1<span style="color: grey;">,</span> <span style="color: teal;">@separator_position</span><span style="color: grey;">,</span> <span style="color: red;">''</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;">
<span style="color: blue;">END</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt; mso-fareast-language: EN-CA;"><span style="color: blue;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Trebuchet MS', sans-serif;">The end result will be a table with each row as a recursive hierarchy. Plus it has level information for depth.</span></div>
<div class="MsoNormal">
<br /></div>
</div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-5977185962228721032013-10-16T08:06:00.000-07:002013-10-16T08:06:43.828-07:00Moving Closer To MCSA: SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-zrAnPRwRIy8/Ul6qrE6wvHI/AAAAAAAAASU/0OPvKd6arsk/s1600/70-457.PNG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="226" src="http://2.bp.blogspot.com/-zrAnPRwRIy8/Ul6qrE6wvHI/AAAAAAAAASU/0OPvKd6arsk/s320/70-457.PNG" width="320" /></a></div>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Last week I passed:</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">Exam 457 <span style="background-color: white; line-height: 20px;">Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 1</span></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><span style="background-color: white; line-height: 20px;"><br /></span>
<span style="line-height: 20px;">Which will help me to qualify for Microsoft Certified Solutions Associate for SQL Server 2012. Now am preparing for 458 (Part 2).</span></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><span style="line-height: 20px;"><br /></span>
<span style="line-height: 20px;">The exam was moderately hard and I had doubts about couple of questions. Anyways getting through the course material helped me a lot in knowing SQL Server 2012.</span></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><span style="line-height: 20px;"><br /></span>
<span style="line-height: 20px;">Cheers!</span></span></div>
Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1tag:blogger.com,1999:blog-7867858430165148488.post-32144433495786440042012-06-20T09:42:00.001-07:002012-06-20T09:44:18.252-07:00Content Organizer Rule Manager for SharePoint 2010<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">I am proud to release the beta version of Content Organizer Rule Manager (codename CORMa) for SharePoint 2010 today.</span><span style="font-family: "Trebuchet MS", sans-serif;"><br /></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">Background:</span><br />
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<span style="font-family: "Trebuchet MS", sans-serif;">Working on a SharePoint 2010 enterprise project we had tons of content types on differest site. Creating and tracking content organizer rules from SharePoint interface was tedious going back and forth between pages. There is no option in SharePoint Designer to manager content organizer rules. Lastly there was a need to bulk create rules as some of our libraries had multiple content types associated.</span></div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span></div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<a href="http://3.bp.blogspot.com/-WiaNZ8uDbWs/T-H9R2mBUGI/AAAAAAAAAQQ/qIxuk0BqdVw/s1600/CORMa.png" imageanchor="1" style="clear: right; cssfloat: right; float: right; margin-bottom: 1em; margin-left: 1em;"><span style="font-family: "Trebuchet MS", sans-serif;"><img border="0" height="233" rca="true" src="http://3.bp.blogspot.com/-WiaNZ8uDbWs/T-H9R2mBUGI/AAAAAAAAAQQ/qIxuk0BqdVw/s320/CORMa.png" width="320" /></span></a><span style="font-family: "Trebuchet MS", sans-serif;">CORMa to the rescue! </span></div>
<ul style="text-align: left;">
<li style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;"><span style="font-family: "Trebuchet MS", sans-serif;">List and create content organizer rules in bulk. </span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Delete rules.</span></li>
<li style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;"><span style="font-family: "Trebuchet MS", sans-serif;">Works for all field types inlcuing Taxonomy fields.</span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Export all site content types with associated lists to text file.</span></li>
<li style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;"><span style="font-family: "Trebuchet MS", sans-serif;">Check inherent SharePoint rules for rule creation.</span></li>
</ul>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<span style="font-family: "Trebuchet MS", sans-serif;">Download, test and give feedback...</span></div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<a href="http://corma.codeplex.com/"><span style="font-family: "Trebuchet MS", sans-serif;">http://corma.codeplex.com/</span></a></div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<a href="http://gallery.technet.microsoft.com/Content-Organizer-Rule-aa98ffab"><span style="font-family: "Trebuchet MS", sans-serif;">http://gallery.technet.microsoft.com/Content-Organizer-Rule-aa98ffab</span></a></div>
<div style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;">
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span></div>
</div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-88063347637628286082012-06-19T09:23:00.000-07:002012-06-19T09:38:00.038-07:00XSL Link for SharePoint 2010 Web Parts<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "Trebuchet MS", sans-serif;">When using a lot of Links list in SharePoint 2010 the view can be quite 'boring'. To add some spice I have created an XSL style sheet and applied to List webpart through web part properties. Easy and fast:</span><br />
<ol style="text-align: left;">
<li><span style="font-family: "Trebuchet MS", sans-serif;">Get the XSL style sheet.</span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Modify (if required)</span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Upload to Style Library > XSL Style Sheets</span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Edit web part and under Miscellaneous > XSL Link paste the path.</span></li>
<li><span style="font-family: "Trebuchet MS", sans-serif;">Set Appearance > Chrome Type to 'Border Only'. Apply and save.</span></li>
</ol>
<div style="text-align: left;">
<span style="font-family: "Trebuchet MS", sans-serif;"></span></div>
<div style="text-align: left;">
<span style="font-family: "Trebuchet MS", sans-serif;"></span></div>
<div style="text-align: left;">
<span style="font-family: "Trebuchet MS", sans-serif;"> Style classes are embedded in the XSL file which you may move on to your CSS files.</span></div>
<div style="text-align: left;">
</div>
<div style="text-align: left;">
<span style="font-family: "Trebuchet MS", sans-serif;">This would work on any list which is created on 'Link' list. I have added some logic to get list name (title) using XSL functions as it is pretty hard to do so. Let me know if someone knows an easier way, I can get the title in SharePoint Designer but on actual page it disappears. I think the View node is not available when XSL renders on page.</span></div>
<span style="font-family: "Trebuchet MS", sans-serif;"> </span> <br />
<span style="font-family: "Trebuchet MS", sans-serif;">Again, this XSL is targeted towards Links list in SharePoint 2010 but can be extended/reused for other libraries as well. Just change [@Columns] as per your CAML.</span><br />
<br />
<div>
<xsl:stylesheet version="1.0" <br />
xmlns:xsl=<a href="http://www.w3.org/1999/XSL/Transform">http://www.w3.org/1999/XSL/Transform</a> <br />
xmlns:msxsl="urn:schemas-microsoft-com:xslt" <br />
exclude-result-prefixes="msxsl" <br />
xmlns:ddwrt2="urn:frontpage:internal"> <br />
<xsl:output method='html' indent='yes'/> <br />
<br />
<xsl:template match='dsQueryResponse' xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"> <br />
<br />
<style> <br />
.QLinksHeader{ <br />
PADDING-BOTTOM: 2px; PADDING-LEFT: 2px; PADDING-RIGHT: 2px;<br />
DISPLAY: block; MARGIN-BOTTOM: 2px; BACKGROUND: #0d5995; COLOR: white; <br />
FONT-SIZE: 14px; PADDING-TOP: 2px } <br />
#QLinks { <br />
PADDING-BOTTOM: 0px; LIST-STYLE-TYPE: none; MARGIN: 0px; <br />
PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 0px; } <br />
#QLinks LI { <br />
BACKGROUND-IMAGE: url(/_layouts/images/icongo.gif); PADDING-LEFT: 2em; <br />
BACKGROUND-REPEAT: no-repeat; MARGIN-LEFT: 8px; FONT-SIZE: 12px; <br />
PADDING-BOTTOM: 5px } <br />
#QLinks LI a:hover{ text-decoration:underline; } <br />
</style> <br />
<br />
<div cellpadding="10" cellspacing="0" border="1" > <br />
<SPAN class="QLinksHeader"> <br />
<xsl:value-of select=" substring-before(substring-after(/dsQueryResponse/Rows/Row/@FileRef,'Lists/'),'/')" ><br />
</xsl:value-of> <br />
</SPAN> <br />
<ul id="QLinks"> <br />
<xsl:apply-templates select='Rows/Row'/> <br />
</ul> <br />
</div> <br />
</xsl:template> <xsl:template match='Row'> <br />
<li> <br />
<a href="{@URL}"> <br />
<xsl:value-of select="@URL.desc" ></xsl:value-of><br />
</a> <br />
</li> <br />
</xsl:template> <br />
</xsl:stylesheet></div>
</div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1tag:blogger.com,1999:blog-7867858430165148488.post-17152570726330719732011-12-02T09:02:00.001-08:002011-12-02T09:17:57.377-08:00Installing SQL Server 2012 RC0 on Windows 7<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="background-color: transparent;"><div style="background-color: transparent;"><span id="internal-source-marker_0.689332191599533" style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">SQL Server 2012 RC0, formerly known as “Denali”, is available to download. Got my copy and started installing on a copy on Windows 7 virtual box. The installation was smooth. </span></div><div style="background-color: transparent;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><br />
</span></div><div style="background-color: transparent;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Here are the steps:</span></div><div style="background-color: transparent;"><span class="Apple-style-span" style="font-family: Arial;"><span class="Apple-style-span" style="font-size: 15px; white-space: pre-wrap;"><br />
</span></span><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr"><table style="border-bottom-style: none; border-collapse: collapse; border-color: initial; border-left-style: none; border-right-style: none; border-top-style: none; border-width: initial; width: 624px;"><colgroup><col width="*"></col><col width="*"></col></colgroup><tbody>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="215px;" src="https://lh4.googleusercontent.com/PtkU4ftNyYV8QdkXzI4jMQVlJvl8Pf3fcep6blTMnZwl7Wb0egpzmX6przJgWMG5kZpLOjLBVPjgqu2LndWaKkNv6YlPbsPt7BoAAhV5RWT-zPQKbUU" width="287px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="220px;" src="https://lh4.googleusercontent.com/3a_AqB4u_cfWKuAGusYBgGmeJ3vwvYNSJu_LnchsxfC6_Srve0pbm30BRNa48927jFHNCXcWRVUEkEasftLbcukn1-Yr4UNfUQp2gHBkkWvwfIc6sDE" width="292px;" /></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">After extracting the download file, double click the setup.exe. Select the first option under installation to install a stand alone version.</span></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Some checks, no issues.</span></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="220px;" src="https://lh6.googleusercontent.com/sNO-Fh9uBU8CLs5LdAS0AvECCdeiBLlv49U8o5uuwLbXvwA9g-OwJ01VLo0FBOBvOxuhjFO4lmfGy6yDwO6--pcSZJjMJBWtcH8OQVg6UBWN2Lxa3FY" width="294px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="222px;" src="https://lh6.googleusercontent.com/JLi702hhO9oSvp0z5O6QF4P_N5dZODXJ-H5JOZ6S9KxfABuVWZnNOSPAko9tvq918_aHoJAuTkZgVy7YHRYQlCot2KcGSUgbtjfMM4esyjNH27dtMI0" width="296px;" /></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Choose what type of edition you want to get.</span></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Some more checks.</span></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="219px;" src="https://lh4.googleusercontent.com/GXDqCrkHdRQ5hrjRylTkODBsKKqgJpHuw7xWAe8JXEA0wVh6Bz2Jg9LTQYv_W1LB6BRl8QiNlFOfgxt420VlWVCdbf_zHwKroQEIiU7h6G7Oy8oY-Mw" width="292px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="221px;" src="https://lh4.googleusercontent.com/VXkwRdzgbhU-VJ3wv8bX_R8c1GJQQGrSS4dfNS1gd0BYYMwtqwXsxYXYp-GzA7pGUoG1QXWSOnBd0n1YWfUyAxu0NBSXMxtj7oXo5j5hVu2LbLf-8ac" width="295px;" /></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Standard feature installation.</span></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Select the components.</span></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="221px;" src="https://lh4.googleusercontent.com/ib6SLeF4xrvFpgAaGnGFEksIIfa5msZZurSU7cM8UortrgbDWIfuUYLrjF8dQOVhwdhvNY4s7O6pV_IIcVs8NvzkmmFlk9xSeU80bec1_SMkEtv99JU" width="295px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="219px;" src="https://lh6.googleusercontent.com/Lx22_36JUhUygS_uYImfWGoeFnqft0H33nve0Wn8KXbke45msEoi7xgTUYa2D1bLXrZW9p73yTYGd4p3lar7Hqsn-xq9GiXmZfo1Hq5bZjb_zwmK3X4" width="292px;" /></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Wondering where is BIDS; it’s been named to SQL Server Data Tools.</span></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Setup the instance id.</span></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="221px;" src="https://lh6.googleusercontent.com/p1oAC6PGkHSB5VxSMWPInroxV6-DLi-RutpNQrjlazCI5gOPy0-h0E0evJs6i-jmq-JTv0OROfKDocXPqf7sTefApYyUi94hzWEi0xrGpuWTPazonSk" width="295px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="219px;" src="https://lh4.googleusercontent.com/J18BWWpno_yDZPcLUFHT82g8F079vfjRVlR0oNaBE9f1kG03a755GlK8CblLxuySzrDIubOB8-jYX7_mKK9x-qzo0SLPg9uTwbcMvp-hRROGJky6_BQ" width="292px;" /></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Configure roles to be used.</span></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Analysis services configurations.</span></td></tr>
<tr style="height: 0px;"><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><img height="222px;" src="https://lh6.googleusercontent.com/8IOZuhpbYdnMs5huQesWxXH5Mvcflcs6COiHpUHzlj-alqgKnEkxv-IITHP1FlA3Iqn2ngipdiXX4NjRlsRlUrxZ4c_oYoST2Dyn20GuAmuGssQaslI" width="296px;" /></td><td style="border-bottom-color: rgb(170, 170, 170); border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: rgb(170, 170, 170); border-left-style: dotted; border-left-width: 1px; border-right-color: rgb(170, 170, 170); border-right-style: dotted; border-right-width: 1px; border-top-color: rgb(170, 170, 170); border-top-style: dotted; border-top-width: 1px; padding-bottom: 7px; padding-left: 7px; padding-right: 7px; padding-top: 7px; vertical-align: top;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Reporting Services settings.</span><br />
<span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<span style="background-color: transparent; font-family: Arial; font-size: 15px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Hit ‘Next’, rest of the steps are linear and at the end you have a successful SQL Server 2012.</span></td></tr>
</tbody></table></div></div></div><div style="background-color: transparent;"></div><br />
<div style="background-color: transparent;"></div></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-32489467437737462022011-12-01T14:03:00.000-08:002011-12-01T14:03:58.344-08:00AdventureWorks and SQL Server 2012 RC0 and Access Denied<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">While attaching AdventureWorksDWDenali_Data.mdf to SQL Server 2012 RC0 if you get this error:</span><br />
<blockquote class="tr_bq"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Unable to open the physical file "C:\Data\</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">AdventureWorksDWDenali_Data.mdf". Operating system error 5: "5(Access is denied.)".</span></blockquote><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-T5G3hK-Buus/Ttf48FgWotI/AAAAAAAAAN0/N1zjgoCBW_0/s1600/error+fix.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="215" src="http://3.bp.blogspot.com/-T5G3hK-Buus/Ttf48FgWotI/AAAAAAAAAN0/N1zjgoCBW_0/s320/error+fix.png" width="320" /></a></div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">To fix Right Click --> Properties --> Security</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Give Full Control access to the account you would be using.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Attach to database again.</span></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1tag:blogger.com,1999:blog-7867858430165148488.post-1208758883572884712011-11-21T07:42:00.000-08:002011-11-21T07:49:39.531-08:00Find out SQL Server Edition<div dir="ltr" style="text-align: left;" trbidi="on">Here is a query to find out what version and edition of SQL Sever you are using:<br />
<br />
<div style="background-color: #cccccc;">SELECT<span class="Apple-tab-span" style="white-space: pre;"> </span>SERVERPROPERTY('edition') as Edition,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SERVERPROPERTY('productversion') ProdVersion,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SERVERPROPERTY('productlevel') as Lvl</div></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-71168579255165469552011-08-29T11:37:00.000-07:002011-09-01T12:52:06.364-07:00Clear Analysis Services Cache<div dir="ltr" style="text-align: left;" trbidi="on"><div style="background-color: transparent;"><div dir="ltr" id="internal-source-marker_0.6573647034820169" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">To test execution time of calculated measures from cold cache instead of warm cache run this query on Analysis Services MDX query editor:</span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"></batch></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span class="Apple-style-span" style="white-space: pre-wrap;"></span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: small;"></span></span><br />
<div style="background-color: transparent;"><div dir="ltr" id="internal-source-marker_0.6573647034820169" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> <ClearCache></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-tab-span" style="white-space: pre;"> </span><Object></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> <span class="Apple-tab-span" style="white-space: pre;"> </span><DatabaseID>NameOfCube</DatabaseID> </span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-tab-span" style="white-space: pre;"> </span></Object></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </ClearCache></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: x-small;"><span style="background-color: transparent; color: black; font-family: 'Courier New'; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></Batch></span></span></div></div></div></div></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-40232867835264327722011-08-29T11:32:00.000-07:002011-08-29T11:32:38.905-07:00Analysis Services Default Member Through Stored Procedure<div dir="ltr" style="text-align: left;" trbidi="on"><div style="background-color: transparent;"><div dir="ltr" id="internal-source-marker_0.6038876357488334" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Today, I had to dynamically assign default member value to an analysis services dimension. Scenario: Each user (AD login) has a preferred or default value assigned on time basis. So depending on which date data is sliced it always comes up with the value default of the current user.</span></div><div dir="ltr" id="internal-source-marker_0.6038876357488334" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">For this we would need to create a mapping table in Relational database with date, user and default value.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Create a basic User Stored Procedure (</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">uspGetUserDefaultValue</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">) which takes username (AD Login) as a parameter, selects on the mapping table based on username passed and returns a single value.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Now we create a solution in Visual Studio 2008. This can be of class project template. We would be creating a custom DLL and register with SQL Server Analysis Services solution. Where SSAS solution would use function calls exposed by custom DLL. I created the project as part of my SSAS solution for better manageability.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">We would need to add:</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Microsoft.AnalysisServices</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Microsoft.AnalysisServices.AdomdClient</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="background-color: white; font-family: Verdana; font-size: 13px; white-space: pre-wrap;">as reference to our custom dll project. These can be of version 10 (SQL 2008) or version 9 (SQL 2005). Works fine with either, seamlessly interchangeable.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Now we create/rename our namespace and class to like</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Project/Assembly: </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">AnalysisServicesCustomLibrary</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Namespace: </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">CustomMethodCollection</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Class: </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">CustomMethods</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">or whatever you like.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Create a public static function like:</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">public static string GetUserDefaultValue(string userName)</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">{</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt; text-indent: 31.5pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">OleDbConnection connection;</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> try</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> {</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> <span class="Apple-tab-span" style="white-space: pre;"> </span>connectionString ="provider=sqloledb;server=</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><yourservername></yourservername></span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">;database=</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><yourdatabasename></yourdatabasename></span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">;trusted_connection=yes";</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> connection= new OleDbConnection(connectionString);</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> connection.Open();</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> OleDbCommand command= new OleDbCommand("uspGetUserDefaultValue", connection);</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> command.CommandTimeout = 0;</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> command.CommandType = CommandType.StoredProcedure;</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> command.Parameters.Add(new OleDbParameter("@UserLogin", OleDbType.VarChar, 50));</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> command.Parameters[0].Value = userName;</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> DataTable dataTable = new DataTable("DefaultValue");</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> OleDbDataAdapter dataAdapter= new OleDbDataAdapter(command);</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> dataAdapter.Fill(dataTable);</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> connection.Close();</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> if (dataTable.Rows.Count == 0)</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> return "empty";</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> string defaultValue = dataTable.Rows[0][0].ToString();</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> return "[Dimension].[Dimension Attribute].&[" + defaultValue + "]";</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> }</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> catch (Exception ex)</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> {</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> errorMessage = "Some error: " + ex.Source + " Message: " + ex.Message;</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> throw new Exception(errorMessage, ex);</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> }</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> }</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: small; white-space: normal;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">return</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> part is critical since here we would be formatting the return value into a MemberUniqueName for the associated dimension attribute. Else is basic stuff, feel free to add better exception handling etc.</span></span></span><br />
<span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: small; white-space: normal;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><br />
</span></span></span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Do a quick test to see if you are getting the desired output from Stored Procedure and Class Function.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Now we move to Analysis Services solution. First step is to add reference to </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">AnalysisServicesCustomLibrary </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">project under </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Assemblies</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> folder.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Next move to the dimension where you would like to have a default value. Go to properties of the attribute that would have the default value. Under </span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">DefaultMember</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> enter:</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">STRTOMEMBER( AnalysisServicesCustomLibrary.CustomMethodCollection.CustomMethods.GetUserDefaultValue(UserName))</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Here UserName is an Analysis Services keyword to get current logged in user’s account.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">STRTOMEMBER</span><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> converts the string passed by custom library’s function in a member.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Build/Deploy/Process cube. Run a query or browse the cube and you should have the default value against logged in user.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span class="Apple-style-span" style="background-color: white; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;"><br />
</span><br />
<span class="Apple-style-span" style="background-color: white; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">DEBUG</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: white; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">To debug your code, move to your custom library project. Go to Debug-->Attach to Process and select </span><span style="background-color: transparent; color: black; font-family: Verdana; font-size: 10pt; font-style: italic; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Msmdsrv.exe</span><span style="background-color: transparent; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> process. Add a breakpoint and try querying the dimension using the function.</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: transparent; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> </span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><span style="background-color: transparent; color: black; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">References:</span></div><div dir="ltr" style="margin-bottom: 0pt; margin-left: 4.5pt; margin-right: 4.5pt; margin-top: 0pt;"><a href="http://msdn.microsoft.com/en-us/library/ms174763.aspx"><span style="background-color: white; color: #000099; font-family: Verdana; font-size: 10pt; font-style: normal; font-variant: normal; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap;">Debugging Stored Procedures</span></a></div></div></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-23035731581759221352011-01-28T04:33:00.000-08:002011-01-28T04:33:44.695-08:00From Date To Date in PerformancePoint Analytical Chart<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal"></div><div class="MsoNormal">Today, we add From Date and To Date parameters to Performance Point Server Dashboard Designer’s Analytical Reports. Many times this is a requirement to allow users to select a starting and ending date to view charts/reports. This gives dynamic behavior to time range as compared to using pre-defined date attributes like Month, Week etc.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Start with creating a Report and select Analytical Chart. Add your required Dimensions and Measures including Time dimension.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Once done, move to the Query tab of the report it should be something like below depending on what artifacts you drop :</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK1pmTqQPI/AAAAAAAAANA/EJxP2IlFaas/s1600/QueryEd.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="215" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK1pmTqQPI/AAAAAAAAANA/EJxP2IlFaas/s320/QueryEd.png" width="320" /></a></div><br />
</div><div class="MsoNormal">For demo I am using Time dimension on Bottom Axis and Total Time Decimal on Series. You are free to move time to any other axis. No worries, same process would apply.<br />
</div><div class="MsoNormal">From the bottom of the page add parameter from the Parameters section. Name them what you like, for purpose I use FromDate and ToDate. Give default values, these can be any valid dates.</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_gR7S4_fJPaI/TUK1jplna4I/AAAAAAAAAMo/6hrNK2tPihQ/s1600/AddParam.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="131" src="http://1.bp.blogspot.com/_gR7S4_fJPaI/TUK1jplna4I/AAAAAAAAAMo/6hrNK2tPihQ/s320/AddParam.png" width="320" /></a></div><br />
</div><div class="MsoNormal">With every parameter you add you would find a value added to your query canvas like <fromdate><<fromdate>> and <todate><<todate>>. For now it does not matter where they are just add parameters.</todate></todate></fromdate></fromdate><br />
</div><div class="MsoNormal">Move to your query and where ever your time dimension is used replace that with:</div><div class="MsoNormal">HIERARCHIZE ( {<<fromdate>>:<<todate>><fromdate><todate>} )</todate></fromdate></todate></fromdate><br />
</div><div class="MsoNormal">Your final query would be like:</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1qG-CqPI/AAAAAAAAANE/UuaD8wOBdVs/s1600/QueryUpdate.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="199" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1qG-CqPI/AAAAAAAAANE/UuaD8wOBdVs/s320/QueryUpdate.png" width="320" /></a></div><br />
</div><div class="MsoNormal">Save/Publish your report.<br />
</div><div class="MsoNormal">In your dashboard section add filters from Filters tab. I am using Time Intelligence Calendar filters but any type of filter can be used.</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK2rNztFsI/AAAAAAAAANI/IEjEyOTvYQ8/s1600/Filters.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="70" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK2rNztFsI/AAAAAAAAANI/IEjEyOTvYQ8/s320/Filters.png" width="320" /></a></div><br />
</div><div class="MsoNormal">If you opt to use any other type of filter remember to set your values in Report query respectively.<br />
</div><div class="MsoNormal">Now we have our filters and report. We add them to the dashboard page like so:</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1nWGoX3I/AAAAAAAAAMs/81CknZttMgE/s1600/Dash.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="136" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1nWGoX3I/AAAAAAAAAMs/81CknZttMgE/s320/Dash.png" width="320" /></a></div><br />
</div><div class="MsoNormal">Now we start linking filters and report. First link FromDate filter like so:</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK1oiPaObI/AAAAAAAAAM4/zU9keB3t8uI/s1600/FromDate.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/TUK1oiPaObI/AAAAAAAAAM4/zU9keB3t8uI/s320/FromDate.png" width="312" /></a></div><br />
</div><div class="MsoNormal">In source value select your data source, here TestFromTo is my data source. Next press Filter Link Formula and type in Day. This defines what level of a hierarchy we would be referencing. Remember to setup Time properties in your data source.</div><div class="MsoNormal">Similarly we link ToDate filter like:</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1jMUwzkI/AAAAAAAAAMk/o8f5lezR8nI/s1600/ToDate.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="238" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1jMUwzkI/AAAAAAAAAMk/o8f5lezR8nI/s320/ToDate.png" width="320" /></a></div><br />
</div><div class="MsoNormal">All done. Save/Publish and Preview.</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1pOFyELI/AAAAAAAAAM8/SGTITXHdGu8/s1600/InitialDash.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1pOFyELI/AAAAAAAAAM8/SGTITXHdGu8/s320/InitialDash.png" width="302" /></a></div><br />
</div><div class="MsoNormal">Here dates selected in 14<sup>th</sup> September, 2009 and the report displays data for that day. Now changing the ToDate to 18<sup>th</sup> September, 2009 would show:</div><div class="MsoNormal"><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1oBMwzGI/AAAAAAAAAM0/_lyu4YjVI8w/s1600/FinalDash.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1oBMwzGI/AAAAAAAAAM0/_lyu4YjVI8w/s320/FinalDash.png" width="307" /></a></div><br />
</div><div class="MsoNormal">And we have From Date and To Date filters working on an Analytical Report.</div><div class="MsoNormal"><a href="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1n4gTfRI/AAAAAAAAAMw/y3nKTDQm9VI/s1600/downside.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="106" src="http://3.bp.blogspot.com/_gR7S4_fJPaI/TUK1n4gTfRI/AAAAAAAAAMw/y3nKTDQm9VI/s320/downside.png" width="320" /></a>This is very intuitive way of showing data to end users. A lot of stakeholders ask for this feature especially for Trend Charts/Reports.</div><div class="MsoNormal">Downside to this implementation is that we lose the right click contextual features of analytics like drill downs, decomposition tree etc.</div><div class="MsoNormal"><br />
</div></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com2tag:blogger.com,1999:blog-7867858430165148488.post-68914869901240608592010-10-06T08:53:00.000-07:002010-12-30T23:09:00.783-08:00MDX Date dimension in Descending Order<div class="MsoNormal"><span style="font-family: "Trebuchet MS",sans-serif;">Scenario: </span></div><div class="MsoNormal" style="font-family: "Trebuchet MS",sans-serif;">The client is refusing to make the change to the filters to allow more than 500 entries due to concern that the performance will be degraded for all of their performancepoint dashboards. Would it be possible to change the date filters so that the most recent dates appear first in the list? This would cause the older items to be truncated rather than the most recent.</div><div style="font-family: "Trebuchet MS",sans-serif;"><br />
</div><span style="font-family: "Courier New",Courier,monospace;">select </span><br />
<span style="font-family: "Courier New",Courier,monospace;">[Measures].[Some Units] on 0,</span><br />
<span style="font-family: "Courier New",Courier,monospace;">ORDER ( </span><br />
<span style="font-family: "Courier New",Courier,monospace;">nonempty(</span><br />
<span style="font-family: "Courier New",Courier,monospace;">[time].[year-quarter-month-week-date].members,[Measures].[Some Units])</span><br />
<span style="font-family: "Courier New",Courier,monospace;">,[time].[</span><span style="font-family: "Courier New",Courier,monospace;">year-quarter-month-week-date</span><span style="font-family: "Courier New",Courier,monospace;">].CurrentMember.MemberValue , desc) on 1</span><br />
<span style="font-family: "Courier New",Courier,monospace;">from [MyCube]</span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">This would retain the hierarchies and sort the date filters with most recent date first. So that the past values would be automatically filtered out (more than 500 ones).</span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">No need to change the web.config!</span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">The date dimension is SSAS business intelligence time dimension (auto generated). </span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">Reference:</span><br />
<h3 class="post-name" style="font-family: "Trebuchet MS",sans-serif; font-weight: normal;"><a href="http://blogs.msdn.com/b/performancepoint/archive/2008/01/17/changing-the-limit-on-the-number-of-items-returned-in-a-filter.aspx"><span style="font-size: x-small;">Changing the limit on the number of items returned in a filter</span></a></h3><span style="font-family: "Trebuchet MS",sans-serif;"> </span>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-18354771002362307002010-05-20T02:02:00.000-07:002011-11-23T09:57:57.449-08:00Pass Parameter to Analytical Report PPS 2010<div dir="ltr" style="text-align: left;" trbidi="on">Questions: Can we pass a parameter value to an analytical report in our new SharePoint 2010's PerformancePoint Services environment? For example passing a numeric factor to report and calculate a new measure based on user input.<br />
<div class="separator" style="clear: both; text-align: center;"></div><a href="http://lh5.ggpht.com/_gR7S4_fJPaI/S_Tqb8WxZbI/AAAAAAAAALI/0bH-qDqz9bY/datasource.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="104" src="http://lh5.ggpht.com/_gR7S4_fJPaI/S_Tqb8WxZbI/AAAAAAAAALI/0bH-qDqz9bY/datasource.jpg" width="200" /></a>Answer: Yes. I would assume that you have already a working environment of SharePoint 2010 along with PerformancePoint Services. We begin by opening the PerformancePoint Dashboard Designer.<br />
<br />
Right click 'Data Connections' or from 'Create' ribbon select 'Data Source'. Configure to your already deployed cube.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh6.ggpht.com/_gR7S4_fJPaI/S_Tqb9IurpI/AAAAAAAAALE/RjKXJNY6wdU/analyticalreport.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="101" src="http://lh6.ggpht.com/_gR7S4_fJPaI/S_Tqb9IurpI/AAAAAAAAALE/RjKXJNY6wdU/analyticalreport.jpg" width="200" /></a></div><a href="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TqciYhuSI/AAAAAAAAALU/s6_HGThEx-0/s1600/reportAddparts.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="131" src="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TqciYhuSI/AAAAAAAAALU/s6_HGThEx-0/s200/reportAddparts.jpg" width="200" /></a>Right click on 'PerformancePoint Content' select 'New -> Report' or from 'Create' ribbon select 'Other Reports'. From the dialog box select 'Analytical Chart'. Press OK.<br />
<br />
Name your report as desired I name it 'Factorize Me'.<br />
<br />
<br />
For the sake of simplicity I would add one time dimension to the bottom axis and numeric value to series. This numeric values is upon which we are going to apply the factor. In my case it is 'Sales Amount'.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh6.ggpht.com/_gR7S4_fJPaI/S_TqcOVRe8I/AAAAAAAAALQ/OXzx49vMVwQ/MDXquery.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="252" src="http://lh6.ggpht.com/_gR7S4_fJPaI/S_TqcOVRe8I/AAAAAAAAALQ/OXzx49vMVwQ/MDXquery.jpg" width="320" /></a></div><a href="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TqcBrbUoI/AAAAAAAAALM/Ehny4g6meQo/s1600/designView.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="255" src="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TqcBrbUoI/AAAAAAAAALM/Ehny4g6meQo/s320/designView.jpg" width="320" /></a>I am using a Banking analysis services cube provided by Microsoft but this method can be used for any measure.<br />
One more thing I would like to mention is regarding the use of sales amount as this tutorial would allow user to apply factor on sales for what ifs. Like what if I increase sales by 10% we can enter 1.1 and we have a projected sale of 10%. One can see a good utilization of this.<br />
<br />
Back to the designing. Once we added the our dimensions we move to the 'Query' part of the report since we need to add a parameter.<br />
Here is the query created by PerformancePoint Services:<br />
SELECT<br />
HIERARCHIZE( { [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 1].&[March], [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 2].&[April], [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 2].&[May] } )<br />
ON COLUMNS,<br />
<br />
{ [Measures].[Sales Amount]}<br />
ON ROWS<br />
<br />
FROM [Bank DW]<br />
<br />
We would be editing it and adding a parameter. First add the required measure something like 'Target'.<br />
WITH MEMBER [Measures].[Target] as ([Measures].[Sales Amount] * <<factor>> )<br />
here <<factor>> is our parameter and would be automatically added to parameters section below. One thing to note is that I am using measure sales amount to make the change visible but we can use any other measure here.<factor><factor> <br />
Finally we use this new Target measure on ROWS along with sales amount measure. Here is the final query:<br />
WITH MEMBER [Measures].[Target] as ([Measures].[Sales Amount] * <<factor>> )<br />
<br />
SELECT<br />
HIERARCHIZE( { [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 1].&[March], [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 2].&[April], [Tr Date].[YearToMonth].[Calendar Year].&[2009].&[Q 2].&[May] } )<br />
ON COLUMNS,<br />
<br />
{ [Measures].[Sales Amount], [Measures].[Target] }<br />
ON ROWS<br />
<br />
FROM [Bank DW]<br />
<br />
Assign a default value to our parameter <<factor>> and switch to design pane where you would be seeing 2 measures now (Sales Amount and Target).</factor></factor></factor></factor></factor></factor><br />
<div class="separator" style="clear: both; text-align: center;"></div><a href="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TtTOEvU4I/AAAAAAAAALg/BE21lV54t6A/editsp.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="244" src="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TtTOEvU4I/AAAAAAAAALg/BE21lV54t6A/editsp.jpg" width="320" /></a><br />
<factor><factor><factor><factor><factor><factor><br />
Next create dashboard add the report. Right click the dashboard and select 'Deploy to SharePoint...'. </factor></factor></factor></factor></factor></factor><br />
<factor><factor><factor><factor><factor><factor>Wait for the SharePoint page to load and you can see the report.</factor></factor></factor></factor></factor></factor><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh3.ggpht.com/_gR7S4_fJPaI/S_TtS4pHddI/AAAAAAAAALY/NcT_1SlsXAw/s1600/sp.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="215" src="http://lh3.ggpht.com/_gR7S4_fJPaI/S_TtS4pHddI/AAAAAAAAALY/NcT_1SlsXAw/s320/sp.jpg" width="320" /></a></div><factor><factor><factor><factor><factor><factor> </factor></factor></factor></factor><br />
<factor><factor><factor><factor>Time to add an input field where use can enter a value to be used by our report. So select 'Edit Page' from top. Click on 'Add Web Part' on which ever zone you would like the input box to appear. This would open the web part gallery. From 'Categories' select 'Filters' and from there select 'Text Filter'. Press 'Add' button.</factor></factor></factor></factor></factor></factor><br />
<factor><factor><factor><factor><factor><factor> </factor></factor></factor></factor><br />
<factor><factor><factor><factor>Rename web part to your liking.</factor></factor></factor></factor><br />
</factor></factor><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh5.ggpht.com/_gR7S4_fJPaI/S_TtTaiT39I/AAAAAAAAALo/MhdlvejBv_c/connectfactor.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="81" src="http://lh5.ggpht.com/_gR7S4_fJPaI/S_TtTaiT39I/AAAAAAAAALo/MhdlvejBv_c/connectfactor.jpg" width="400" /></a></div><factor><factor><factor><factor>From web part setting select 'Connections' >> 'Send Filter Values To' >> 'Factorize Me' (this is the name of our analytical report). On the configure connection dialog box just check if the correct parameter is selected and press 'Finish'.</factor></factor></factor></factor><br />
<factor><factor><factor><factor><br />
</factor></factor></factor></factor><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TtTMabJLI/AAAAAAAAALk/AdY9RfwkuV0/configure.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="164" src="http://lh4.ggpht.com/_gR7S4_fJPaI/S_TtTMabJLI/AAAAAAAAALk/AdY9RfwkuV0/configure.jpg" width="320" /></a></div><factor><factor><factor><factor>That is it, we are done. Enter a value and see the Target value change. (if the values does not change try edit/save SharePoint page again)</factor></factor></factor></factor><br />
<factor><factor><factor><factor>Cool feature to SharePoint 2010 to be able to connect web parts to PerformancePoint content.</factor></factor></factor></factor><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://lh3.ggpht.com/_gR7S4_fJPaI/S_TtTHVGRWI/AAAAAAAAALc/68JwVlJkpA8/final.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="http://lh3.ggpht.com/_gR7S4_fJPaI/S_TtTHVGRWI/AAAAAAAAALc/68JwVlJkpA8/final.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="313" src="http://lh3.ggpht.com/_gR7S4_fJPaI/S_TtTHVGRWI/AAAAAAAAALc/68JwVlJkpA8/final.jpg" width="400" /></a></div><factor><factor><factor><factor>One downside to this is that when we use custom MDX in reports the inherent analytic features are disabled like drill downs, pivot, (the new very useful) adding measure on the go etc.</factor></factor></factor></factor><br />
<factor><factor><factor><factor>External Links:</factor></factor></factor></factor><br />
<a href="http://blogs.msdn.com/performancepoint/archive/2007/06/15/mdx-in-dashboards-scorecards-and-views.aspx">MDX in Dashboards, Scorecards, and Views?</a><factor><factor><factor><factor><br />
</factor></factor></factor></factor></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1tag:blogger.com,1999:blog-7867858430165148488.post-86859176592330856182010-05-12T23:34:00.001-07:002010-05-12T23:39:40.241-07:00SharePoint 2010 location of log filesIf anyone is wondering what is the location of SharePoint 2010 log files. Copy the line below and paste it in run:<br />
%programfiles%\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS<br />
<br />
SharePoint 2007 had '12' folder and SharePoint 2010 has '14'... hmm where did '13' go??? Superstitious I presume since most building don't have 13th floors :D<br>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-61293977578374075152010-05-03T01:52:00.000-07:002010-05-03T01:56:03.310-07:00PerformancePoint and Default Fitler Values<a href="http://4.bp.blogspot.com/_gR7S4_fJPaI/S96PYZW6bsI/AAAAAAAAAKU/uYQHOPiDjK8/s1600/PPS.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="400" src="http://4.bp.blogspot.com/_gR7S4_fJPaI/S96PYZW6bsI/AAAAAAAAAKU/uYQHOPiDjK8/s400/PPS.jpg" width="342" /></a>Default behavior of PerformancePoint filters is to persist the values selected before, so that when dashboard reopens the filter values are set to those used previously. But what if we need to change that THEN the following article is what is needed:<br />
<br />
<a href="http://blogs.msdn.com/performancepoint/archive/2008/02/12/always-display-default-filter-selection-in-dashboards.aspx">http://blogs.msdn.com/performancepoint/archive/2008/02/12/always-display-default-filter-selection-in-dashboards.aspx</a><br />
<br />
(Image taken for the above article)Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-57143833318954268682010-04-26T04:56:00.000-07:002010-05-03T02:03:17.401-07:00XSL Conditional Format based on QueryString using SharePoint DesignerToday I had to add a success message on <b>ContactUs </b>page built on <b>MOSS </b>website using SharePoint Designer.<br />
I had already built a ContactUs form, built by using <b>DataFormWebPart</b> A.K.A <b>DataViewWebPart</b>, where a user can enter some information and submit using only out of the box components using <b>SharePoint Designer</b> (which I will cover in this blog sometimes later).<br />
Problem was, information was submitted successfully but no message was displayed to end user. So following is how I accomplished it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_gR7S4_fJPaI/S9VtHk0jk3I/AAAAAAAAAJs/yQJ01xyjp4I/s1600/xsl.JPG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://1.bp.blogspot.com/_gR7S4_fJPaI/S9VtHk0jk3I/AAAAAAAAAJs/yQJ01xyjp4I/s320/xsl.JPG" /></a></div>First add a parameter in '<b>Common Data View Tasks</b>' as shown in the screen shot.<br />
Press '<b>New Parameter</b>' and select '<b>Parameter Source</b>' as Query String, assign a name like in this case 's' and a default value.<br />
<br />
This would declare a xsl parameter in our DataView web part like:<br />
<xsl:param name="Success">0</xsl:param><br />
<br />
Next is to redirect the same page with a querystring value which would indicate success.For that '<b>Submit</b>' button is changed to send querystring "<i>?s=1</i>" on submit.<br />
To do that:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/S9VvwHQWGdI/AAAAAAAAAJ0/kdpqkXUVFDk/s1600/formaction.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/S9VvwHQWGdI/AAAAAAAAAJ0/kdpqkXUVFDk/s320/formaction.jpg" /></a></div>Right click 'Submit' button, choose '<b>Form Actions...</b>'. In '<b>Navigate to page</b>' append the querystring.<br />
Press Ok.<br />
<br />
Finally in the XSL we use this value of query string Success as:<br />
If page submitted using the Submit button<br />
<xsl:if test="$Success = 1" ><br />
<table><br />
<tr><br />
<td>Thank you for your request</td><br />
</tr><br />
</table><br />
</xsl:if><br />
<a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/S96QV17eevI/AAAAAAAAAKc/GIs2uSPKjjY/s1600/contact.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/S96QV17eevI/AAAAAAAAAKc/GIs2uSPKjjY/s320/contact.jpg" /></a><br />
Else any other value we show the table containing the contact us form.<br />
<xsl:if test="$Success != 1" ><br />
<table border="0" width="80%"><br />
<xsl:call-template name="dvt_1.body"><br />
<xsl:with-param name="Rows" select="$Rows"/><br />
</xsl:call-template><br />
</table><br />
</xsl:if><br />
<br />
<br />
<a href="http://1.bp.blogspot.com/_gR7S4_fJPaI/S96RKd1bJfI/AAAAAAAAAKk/pIJxcEPTGcE/s1600/suc.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://1.bp.blogspot.com/_gR7S4_fJPaI/S96RKd1bJfI/AAAAAAAAAKk/pIJxcEPTGcE/s320/suc.jpg" /></a>Other possibilities can be to show an Alert which can be done by using the following code in side the conditional xsl:<br />
<xsl:text disable-output-escaping="yes"><br />
<!--[CDATA[<br />
<script><br />
alert("Thank you")<br />
</script><br />
]]--></xsl:text><br />
<br />
Good Day to all. <br />
External Links:<span style="font-size: x-small;"> </span><br />
<a href="http://www.evolt.org/node/27855"><span style="font-size: small;">Embed HTML in XML & Retrieve it with XSL</span></a>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-42824819430192123122010-03-31T08:53:00.003-07:002010-03-31T08:55:03.589-07:00KPI Threshold Values From TableToday i had a requirement to make Thresholds configurable in PerformancePoint's KPIs. In PerformancePoint we can make Target value to be stored in a separate table and use it. But in my case I was mostly working with percentages where target values is always 100% but the performance indicators are variable. As some >80% is green sometimes >70% is green.<br />
Client needed to store these thresholds values in an independent table perhaps accomplishing 2 things:<br />
<ol><li>Access cube via excel and use these values</li>
<li>Change in thresholds did not effect PPS KPIs.</li>
</ol>As of yet I have not found a way to configure thresholds to be pulled from other source. There is an option in "Band by Stated score (advanced)" but it still uses a single value.<br />
<br />
So following is the methodology to overcome this limitation:<br />
<ol><li>First I created 2 simple tables in my data mart. One table for source systems. Other for target values for Green and Yellow along with reference to source systems table.</li>
<li>In my cube I used these 2 tables in an unrelated measure group. Source systems table being used as a dimension. Target value table used a fact table with a measure group.</li>
</ol>These were all the backend changes required.<br />
Now we move to PerformancePoint server's Dashboard Designer.<br />
<br />
For each KPI in the Actual row use the following CASE expression:<br />
<blockquote><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">CASE</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">when</span> </div><div style="background-color: #cccccc; color: black;"><span style="font-size: small;">[Measures].[My Value] >= ([Measure].[Green - Fact Target Values], [Dim Source].&[System XYZ])</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Then 1</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">when</span> </div><div style="background-color: #cccccc; color: black;"><span style="font-size: small;">[Measures].[My Value] >= ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">then 0</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">when</span> </div><div style="background-color: #cccccc; color: black;"><span style="font-size: small;">[Measures].[My Value] < ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])</span> </div><div style="background-color: #cccccc; color: black; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">then -1</span><span style="font-size: small;"> END</span></div></blockquote>WHERE<br />
[Measure].[My Value] = Factual value upon which all measure are based.<br />
[Measure].[Green - Fact Target Value] = Threshold value for Green indicator to be shown like 90 etc.<br />
[Measure].[Yellow - Fact Target Value] = Threshold value for Green indicator to be shown like 80 etc.<br />
([Measure].[Yellow - Fact Target Value], [Dim Source].&[System XYZ]) = Returns values for a particular system since I had multiple source systems.<br />
<br />
In the Target value use [Measure].[My Value] in 'Data Mappings' and 'Source Data' in 'Calculations' to show values on dashboard.<br />
<br />
For Thresholds use 'Increasing is Better' scoring pattern and 'Band by numeric value of Actual' banding method. Lastly for indicator values set 1 for Green, 0 for Yellow and -1 for Red.<br />
<br />
That is all you need and no more adjusting Thresholds in Dashboard designer. Just modify values in the database table and you would be fine. Oh and yes you would be required to process your cube every time threshold values change.<br />
<br />
Environment: SQL Server 2005, Performance Point Server 2007 SP2<br />
<br />
<u>Alternate Approach</u><br />
Use SQL Server Analysis Services KPI do accomplish the same since it provides much simpler and easier way to use external goal values.<br />
<br />
External Links:<br />
<ol><li><span style="color: #1f497d;"><a href="http://blogs.msdn.com/performancepoint/archive/2008/01/09/band-by-stated-score.aspx">http://blogs.msdn.com/performancepoint/archive/2008/01/09/band-by-stated-score.aspx</a></span></li>
<li><span style="color: #1f497d;">MS Press Microsoft SQL Server 2005 Analysis Services Step By Step <i>Chapter 8 page 213</i> <o:p></o:p></span></li>
</ol>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-47261656785108168562010-02-11T00:18:00.000-08:002010-02-11T00:19:53.151-08:00Password Protected Access DB and SSISToday I shall show you how to import data using SSIS from MS Access 2007 which is password protected. Creating a connection using Access provider and now specifying password would give error:<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_gR7S4_fJPaI/S3O8E32gVuI/AAAAAAAAAI8/U2YsM4rW7YY/s1600-h/Error.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="270" src="http://1.bp.blogspot.com/_gR7S4_fJPaI/S3O8E32gVuI/AAAAAAAAAI8/U2YsM4rW7YY/s320/Error.jpg" width="320" /></a></div><br />
To remove this error double click your new connection in the Connection Managers.<br />
<br />
Move to the 'All' tab.<br />
<br />
<br />
Enter the password against the propery "<b>Jet OLEDB:Database Password</b>".<br />
<br />
And Test Connection. Success.<br />
<br />
<br />
<a href="http://2.bp.blogspot.com/_gR7S4_fJPaI/S3O8q4RxEgI/AAAAAAAAAJE/NpsnUVm-eWE/s1600-h/Success.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="278" src="http://2.bp.blogspot.com/_gR7S4_fJPaI/S3O8q4RxEgI/AAAAAAAAAJE/NpsnUVm-eWE/s320/Success.jpg" width="320" /></a>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-45909269867330798942009-12-04T06:45:00.000-08:002009-12-04T06:46:26.462-08:00Where Clause and SSAS Calculations<div style="font-family: Georgia,"Times New Roman",serif;">Today I had to change business rule in our SQL Server Analysis Services 2005 solution. It would be hard to explain the scenario so I use an example.<br />
Scenario: A class contains boys and girls. We need to calculate the passing percentage of students that are boys.<br />
<br />
Previously the rule was to get passing percentage of boys.<br />
Total number of boys that pass / Total number of students in class<br />
Correct rule should have been:<br />
Total number of boys that pass / Total number of boys<br />
Yeah, I know this can be done in a giffy in SQL Server but Analysis services is a totally different ball game.<br />
<br />
Constraint was that we had no dimension for gender like boys, girls. Instead two columns in fact table (yup, not good implementation) boys and girls with value 0,1.<br />
To work around our constraint we created Named Query in cube which returned all the dimensions along with every record where boys = 1.<br />
Finally we had to filter measure based upon result dimension (P meaning Pass). So in Calculations tab of cube created a calculation as:<br />
<br />
<span style="background-color: #eeeeee;">Round(([Measures].[Vw Get All Boys Count], [Result Dim].[Result].&[P]) / [Measures].[Vw Get All Boys Count] * 100,0) </span><br />
<br />
And got the result we were expecting.<br />
</div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-9518160632861798332009-12-03T06:59:00.000-08:002009-12-03T07:00:32.233-08:00Alerts on SharePoint ListToday I had to troubleshoot a SharePoint environment where alerts were not working.<br />
Firstly this environment was not configured to send mail. So I started by installing IIS's SMTP client component. Configured the relay server.<br />
Then configured the 'Outgoing E-Mail Settings' in Central Administrator.<br />
Added an alert on a custom list.<br />
And upon adding a new item nothing happens... hmm... googled around... eliminated the fail points since this environment was working with anonymous access. And then got it working by:<br />
Disable alerts on the site <br />
<div style="background-color: #eeeeee;"><span style="font-family: 'Verdana','sans-serif'; font-size: 10pt;">stsadm.exe -o setproperty -pn alerts-enabled -pv "false" -url </span><a href="http://problemsite/" mce_href="http://problemsite/"><span style="font-family: 'Verdana','sans-serif'; font-size: 10pt;"><span style="color: blue;">http://problemsite</span></span></a><br />
</div><div style="background-color: white;">Enable alerts on the site <br />
</div><div style="background-color: #eeeeee;"><span style="font-family: 'Verdana','sans-serif'; font-size: 10pt;"> stsadm.exe -o setproperty -pn alerts-enabled -pv "true" -url </span><a href="http://problemsite/" mce_href="http://problemsite/"><span style="font-family: 'Verdana','sans-serif'; font-size: 10pt;"><span style="color: blue;">http://problemsite</span></span></a><br />
</div>Added a new item in the custom list and it working like a charm. Also works when an anonymous user adds a list item.<br />
<br />
External Links:<br />
1. <a href="http://blogs.technet.com/harikumh/archive/2008/05/25/troubleshooting-alerts.aspx">Troubleshooting Alerts</a><br />
2. <a href="http://technet.microsoft.com/en-us/library/cc263462.aspx">Configure outgoing e-mail settings</a>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1tag:blogger.com,1999:blog-7867858430165148488.post-20425212750877424472009-11-05T23:26:00.000-08:002009-11-05T23:27:50.255-08:00OLE DB Parameterized SQL Command in Execute SQL Task<div style="font-family: Georgia,"Times New Roman",serif;">There are very good articles on the internet explaining how to use different parameters in 'Execute SQL Task' control of SSIS.<br />
</div><div style="font-family: Georgia,"Times New Roman",serif;">I needed to use the same parameter twice in my query so this is what i did:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_gR7S4_fJPaI/SvPOJzqTzOI/AAAAAAAAAHg/ajyPYuUZMm0/s1600-h/sqlexetask.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_gR7S4_fJPaI/SvPOJzqTzOI/AAAAAAAAAHg/ajyPYuUZMm0/s320/sqlexetask.JPG" /></a><br />
</div>Since SSIS treats every other '?' used in SQL statement as a new parameter index I used the same variable twice. And the 'where' clause used is like: <br />
</div><div style="font-family: Georgia,"Times New Roman",serif;"><pre style="background-color: #cccccc; color: black;">WHERE (? = GetDate() OR CreatedDate > ? ) </pre><pre></pre></div><div style="font-family: Georgia,"Times New Roman",serif;">Reads where fromdate is equal to today or created date is greater than fromdate.<br />
<br />
External Links<br />
</div><div style="font-family: Georgia,"Times New Roman",serif;">1. <span style="font-size: small;"><a href="http://decipherinfosys.wordpress.com/2008/03/26/running-parameterized-sql-commands-using-the-execute-sql-task-i/" rel="bookmark" title="Permanent Link to Running Parameterized SQL Commands Using the Execute SQL Task – I">Running Parameterized SQL Commands Using the Execute SQL Task – I</a></span><br />
<span style="font-size: small;">2. <a href="http://go2.wordpress.com/?id=725X1342&site=decipherinfosys.wordpress.com&url=http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms141003.aspx">Execute SQL Task on MSDN</a> <br />
</span><br />
</div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com0tag:blogger.com,1999:blog-7867858430165148488.post-29916622316445575832009-10-23T07:08:00.000-07:002009-10-23T08:07:17.614-07:00Multi-Value Parameters, Stored Procedures and IN operator<p><div style="font-family: Georgia,"Times New Roman",serif;">Today, I was adding parameters to my Reporting Services report and made a very common mistake when using multi-value parameters. My assumption:<br />
</div><div style="background-color: #eeeeee; font-family: Georgia,"Times New Roman",serif;"><pre><span style="color: black;">SELECT * FROM [SomeTable] WHERE ID IN ( @multiValueParameter )
</span></pre></div><div style="font-family: Georgia,"Times New Roman",serif;">No wonder it didn't work since this is interpreted as:<br />
</div><div style="background-color: #eeeeee; font-family: Georgia,"Times New Roman",serif;"><pre><span style="color: black;">SELECT </span><span style="background-color: #eeeeee; color: black;">* </span><span style="color: black;">FROM [SomeTable] WHERE </span><span style="background-color: #eeeeee; color: black;"> ID IN ( '1,2,3,4' )</span></pre></div><i>instead</i> of<br />
<div style="background-color: #eeeeee; font-family: Georgia,"Times New Roman",serif;"><pre><span style="color: black;">SELECT </span><span style="background-color: #eeeeee; color: black;">* </span><span style="color: black;">FROM [SomeTable] WHERE </span><span style="background-color: #eeeeee; color: black;"> ID IN ( '1','2','3','4' )</span></pre></div><div style="font-family: Georgia,"Times New Roman",serif;">I recalled I had done this before in some other project and after a visit to the archive I found it. <br />
</div><div style="font-family: Georgia,"Times New Roman",serif;">Following is the solution (You will find same logic elsewhere but with use of a Split function to return table whereas I create a table in my procedure in other words skip creating a function):<br />
</div><div style="background-color: #eeeeee; font-family: Georgia,"Times New Roman",serif;"><pre><span style="background-color: #eeeeee; color: black;">DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned
DECLARE @TableOfArrayItems TABLE (Item NVARCHAR(100) collate database_default ) DECLARE @separator char(1) --Used in WHERE clause
BEGIN
SET @separator =',' --Separator A.K.A. Delimiter
SET @MultiValueFromRS = @MultiValueFromRS + @separator --append ',' at the end
WHILE PATINDEX('%' + @separator + '%', @OpportunityLevel) <> 0
BEGIN -- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@MultiValueFromRS)
SELECT @array_value = LEFT(@MultiValueFromRS, @separator_position - 1)
INSERT INTO @TableOfArrayItems(Item) VALUES(@array_value)
--Moving to end of array
SELECT @MultiValueFromRS = STUFF( @MultiValueFromRS, 1, @separator_position, '')
END
END
</span></pre></div><div style="font-family: Georgia,"Times New Roman",serif;">And now you can use @TableOfArrayItems anywhere in you query/stored procedure like:<br />
</div><div style="background-color: #eeeeee; font-family: Georgia,"Times New Roman",serif;"><pre><span style="color: black;">SELECT * FROM [SomeTable] WHERE</span><span style="background-color: #eeeeee; color: black;"> ID IN ( SELECT ID FROM @</span><span style="background-color: #eeeeee; color: black;">TableOfArrayItems</span><span style="background-color: #eeeeee; color: black;">)
</span></pre></div><div style="font-family: Georgia,"Times New Roman",serif;">And success!<p></div><div style="font-family: Georgia,"Times New Roman",serif;">External References: <br />
</div><ol style="font-family: Georgia,"Times New Roman",serif;"><li><a href="http://www.sommarskog.se/dynamic_sql.html#List">The curse and blessing of dynamic SQL</a><span style="font-size: small;"> </span></li>
<li><span style="font-size: small;"><a href="http://bisqlserver.blogspot.com/2007/02/using-multi-value-parameters-in-stored.html">Using Multi-Value Parameters in Stored Procedures</a></span><span style="font-size: small;"> </span></li>
<li><a href="http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/"><span style="font-size: small;">Passing multi-value parameter in stored procedure (SSRS report)</span></a></li>
</ol><h2 style="font-family: Georgia,"Times New Roman",serif; font-weight: normal;"></h2><div style="font-family: Georgia,"Times New Roman",serif;"></div><div style="font-family: Georgia,"Times New Roman",serif;"></div>Umair Khanhttp://www.blogger.com/profile/05206643132277024296noreply@blogger.com1