Daily IT Solutions

App Timeline Server not starting Or Downgrade Ambari

2015-03-13T13:36:00.001-07:00

This can also be used as a downgrade guide from Ambari 1.7.0 to 1.6.1.

If using HDP 2.1.2 with Ambari 1.7.0 your App Timeline Server does not start, you come to the right place.

Symptoms: running the ATS from Ambari throws:

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.

All services work fine. I have set the recommended configuration for HDP 2.1.2

yarn.timeline-service.store-class = org.apache.hadoop.yarn.server.applicationhistoryservice.timeline.LeveldbTimelineStore

The History Server is running fine.

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:
1. Stopped and removed ambari server and all agents
2. Deleted repo and any directories for ambari
3. Downloaded and installed ambari 1.6.1
4. Re-configured/installed cluster, as HDP version remained the same
5. Formatted namenode and hbase
6. Change the config:

yarn.timeline-service.store-class = org.apache.hadoop.yarn.server.applicationhistoryservice.timeline.LeveldbTimelineStore

7. Start ATS, failed, checked logs for historyserver, error:

Permission denied on /hadoop/yarn/timeline/leveldb.timeline-store.ldb/LOCK

8. Deleted the leveldb-timeline-store.ldb
9. Restarted ATS, worked fine!

Usually I never got this issue for other cluster installs using HDP 2.2 and Ambari 1.7.0.

Changing Storage in Hadoop

2015-03-13T13:27:00.001-07:00

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.

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.

First format and optimized the partition or drive.

mkfs -t ext4 -m 1 -O dir_index,extent,sparse_super /dev/sdb

Create a mount directory

mkdir -p /disk/sdb1

Mount with optimized settings

mount -noatime -nodiratime /dev/sdb /disk/sdb1

Append to fstab file so that the partition is mounted on boot (very critical)

echo "/dev/sdb /disk/sdb1 ext4 defaults,noatime,nodiratime 1 2" >> /etc/fstab

Add folder for hdfs data

mkdir -p /disk/sdb1/data

Location to store Namenode data

mkdir -p /disk/sdb1/hdfs/namenode

Location to store Secondary Namenode

mkdir -p /disk/sdb1/hdfs/namesecondary

Set these in hdfs-site.xml or through Ambari

dfs.namenode.name.dir = /disk/sdb1/hdfs/namenode
dfs.namenode.name.dir = /disk/sdb1/hdfs/namesecondary
dfs.datanode.data.dir = /disk/sdb1/data

Set permissions

sudo chown -R hdfs:hadoop /disk/sdb1/data

Format namenode

hadoop namenode -format

Start namenode through ambari or CLI

hadoop namenode start

Start all nodes and services. The new drive should be listed.

References:
http://www.slideshare.net/leonsp/best-practices-for-deploying-hadoop-biginsights-in-the-cloud

RStudio setup on Hortonworks Hadoop 2.1 Cluster

2014-12-11T09:21:00.002-08:00

Here is a complete set of steps I performed to set up R and RStudio on a small cluster.

Installing R and Rstudio
-- R should be installed on the node which have Hive server
-- RStudio can be installed anywhere. (I installed on edge node)

sudo rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
sudo yum -y install git wget R
ls /etc/default
sudo ln -s /etc/default/hadoop /etc/profile.d/hadoop.sh
cat /etc/profile.d/hadoop.sh | sed 's/export //g' > ~/.Renviron

Check latest version of RStudio @
http://www.rstudio.com/products/rstudio/download-server/
(It should have installation steps, follow those)
Listing them here for completion with current release version)
$ sudo yum install openssl098e # Required only for RedHat/CentOS 6 and 7
$ wget http://download2.rstudio.org/rstudio-server-0.98.1091-x86_64.rpm
$ sudo yum install --nogpgcheck rstudio-server-0.98.1091-x86_64.rpm

Create a new system user and set password
sudo useradd rstudio
sudo passwd rstudio
>> hadoop

Login to RStudio at http://hostname:8787

Install required packages either from
In RStudio >> Tool >> Install packages
OR
install.packages( c('RJSONIO', 'itertools', 'digest', 'Rcpp', 'functional', 'plyr', 'stringr'), repos='http://cran.revolutionanalytics.com')
install.packages( c('bitops', 'reshape2'), repos='http://cran.revolutionanalytics.com')
install.packages( c('RHive'), repos='http://cran.revolutionanalytics.com')

Download latest rmr2 package from:
https://github.com/RevolutionAnalytics/RHadoop/wiki/Downloads
Winscp tar.gz file and install through Rstudio

### Need to run every time RStudio is initialized or restarted
Set environment variables in RStudio
Sys.setenv(HADOOP_HOME="your hadoop installation directory here e.g. /usr/lib/hadoop")
Sys.setenv(HIVE_HOME="your hive installation directory here e.g. /usr/lib/hive")
XX Sys.setenv(HADOOP_CONF_DIR="/etc/hadoop/conf/") do not execute!

Sys.setenv("RHIVE_FS_HOME"="your RHive installation directory here e.g. /home/rhive")
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.
If not this is the error:
Error: java.io.IOException: Mkdirs failed to create file:/home/rhive/lib/2.0-0.2

library(RHive)
rhive.init()
rhive.connect(host="IP ADDRESS/Hostname", port=10000, hiveServer2=TRUE)

If error
Error: java.sql.SQLException: Error while processing statement: file:///rhive/lib/2.0-0.2/rhive_udf.jar does not exist.
check if the jar file is in the said directory and rstudio user has permission on it.

Hope it helps.

Cheers!

References and Thanks:
http://jsolderitsch.wordpress.com/hortonworks-sandbox-r-and-rstudio-install/

Excel + SQL Server Linked Server Troubleshooting

2013-12-09T08:35:00.003-08:00

Following is a great article to troubleshoot errors thrown by OLEDB when connecting an Excel file from OPENROWSET query:

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Gives step by step resolution to multiple errors like:

- Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

- Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

- SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part

of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

On client deployments my recurring issue was setting my access to temp folders for service accounts.

Build Hierarchy From Delimited String

2013-11-26T10:50:00.001-08:00

In this post we create a parent child hierarchy (n level) from a delimited string like [Parent_Child_LeafChild].

What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:

Stage1

--Stage2

----Stage3

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 @separator char(1) --Used in WHERE clause

declare @StageLevel varchar(255) = 'Stage1_Stage2_Stage3'

declare @holder varchar(255) = @stagelevel

SET @separator = '_' --Separator A.K.A. Delimiter

SET @StageLevel = @StageLevel + @separator --append ',' at the end

-- select PATINDEX('%[' + @separator + ']%', @StageLevel)

Declare @Level int = 0

Declare @ParentId int

WHILE PATINDEX('%[' + @separator + ']%', @StageLevel) <> 0

BEGIN -- patindex matches the a pattern against a string

SELECT @separator_position = PATINDEX('%[' + @separator + ']%',@StageLevel)

SELECT @array_value = LEFT(@StageLevel, @separator_position - 1)

set @level = @level +1

--select @array_value, @level

IF @level =1 --This is parent node

Begin

insert into Hierarchy (StageLevelName, StageLevel)

values (@array_value, @Level)

END

ELSE

BEGIN

insert into Hierarchy (StageLevelName, StageLevel, ParentID)

values (@array_value, @Level, @ParentId)

END

set @ParentId = @@IDENTITY

select @ParentId

--Moving to end of array

SELECT @StageLevel = STUFF( @StageLevel, 1, @separator_position, '')

END

The end result will be a table with each row as a recursive hierarchy. Plus it has level information for depth.

Moving Closer To MCSA: SQL Server 2012

2013-10-16T08:06:00.000-07:00

Last week I passed:
Exam 457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 1

Which will help me to qualify for Microsoft Certified Solutions Associate for SQL Server 2012. Now am preparing for 458 (Part 2).

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.

Cheers!

Content Organizer Rule Manager for SharePoint 2010

2012-06-20T09:42:00.001-07:00

I am proud to release the beta version of Content Organizer Rule Manager (codename CORMa) for SharePoint 2010 today.

Background:

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.

CORMa to the rescue!

List and create content organizer rules in bulk.
Delete rules.
Works for all field types inlcuing Taxonomy fields.
Export all site content types with associated lists to text file.
Check inherent SharePoint rules for rule creation.

Download, test and give feedback...

http://corma.codeplex.com/

http://gallery.technet.microsoft.com/Content-Organizer-Rule-aa98ffab

XSL Link for SharePoint 2010 Web Parts

2012-06-19T09:23:00.000-07:00

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:

Get the XSL style sheet.
Modify (if required)
Upload to Style Library > XSL Style Sheets
Edit web part and under Miscellaneous > XSL Link paste the path.
Set Appearance > Chrome Type to 'Border Only'. Apply and save.

Style classes are embedded in the XSL file which you may move on to your CSS files.

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.

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.

<xsl:stylesheet version="1.0"
xmlns:xsl=http://www.w3.org/1999/XSL/Transform
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
exclude-result-prefixes="msxsl"
xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method='html' indent='yes'/>

<xsl:template match='dsQueryResponse' xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">

<style>
.QLinksHeader{
            PADDING-BOTTOM: 2px; PADDING-LEFT: 2px; PADDING-RIGHT: 2px;
            DISPLAY: block; MARGIN-BOTTOM: 2px; BACKGROUND: #0d5995; COLOR: white;
            FONT-SIZE: 14px; PADDING-TOP: 2px }
#QLinks {
            PADDING-BOTTOM: 0px; LIST-STYLE-TYPE: none; MARGIN: 0px;
            PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 0px; }
#QLinks LI {
            BACKGROUND-IMAGE: url(/_layouts/images/icongo.gif); PADDING-LEFT: 2em;
            BACKGROUND-REPEAT: no-repeat; MARGIN-LEFT: 8px; FONT-SIZE: 12px;
            PADDING-BOTTOM: 5px }
#QLinks LI a:hover{ text-decoration:underline; }
</style>

<div cellpadding="10" cellspacing="0" border="1" >
    <SPAN class="QLinksHeader">
      <xsl:value-of select=" substring-before(substring-after(/dsQueryResponse/Rows/Row/@FileRef,'Lists/'),'/')" >
      </xsl:value-of>
    </SPAN>
    <ul id="QLinks">
      <xsl:apply-templates select='Rows/Row'/>
    </ul>
</div>
</xsl:template> <xsl:template match='Row'>
<li>
    <a href="{@URL}">
      <xsl:value-of select="@URL.desc" ></xsl:value-of>
    </a>
  </li>
</xsl:template>
</xsl:stylesheet>

Installing SQL Server 2012 RC0 on Windows 7

2011-12-02T09:02:00.001-08:00

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.

Here are the steps:


After extracting the download file, double click the setup.exe. Select the first option under installation to install a stand alone version.	Some checks, no issues.

Choose what type of edition you want to get.	Some more checks.

Standard feature installation.	Select the components.

Wondering where is BIDS; it’s been named to SQL Server Data Tools.	Setup the instance id.

Configure roles to be used.	Analysis services configurations.
	Reporting Services settings. Hit ‘Next’, rest of the steps are linear and at the end you have a successful SQL Server 2012.

AdventureWorks and SQL Server 2012 RC0 and Access Denied

2011-12-01T14:03:00.000-08:00

While attaching AdventureWorksDWDenali_Data.mdf to SQL Server 2012 RC0 if you get this error:

Unable to open the physical file "C:\Data\AdventureWorksDWDenali_Data.mdf". Operating system error 5: "5(Access is denied.)".

To fix Right Click --> Properties --> Security

Give Full Control access to the account you would be using.

Attach to database again.

Find out SQL Server Edition

2011-11-21T07:42:00.000-08:00

Here is a query to find out what version and edition of SQL Sever you are using:

SELECT SERVERPROPERTY('edition') as Edition,
SERVERPROPERTY('productversion') ProdVersion,
SERVERPROPERTY('productlevel') as Lvl

Clear Analysis Services Cache

2011-08-29T11:37:00.000-07:00

To test execution time of calculated measures from cold cache instead of warm cache run this query on Analysis Services MDX query editor:

<DatabaseID>NameOfCube</DatabaseID>

</Object>

</ClearCache>

</Batch>

Analysis Services Default Member Through Stored Procedure

2011-08-29T11:32:00.000-07:00

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.

For this we would need to create a mapping table in Relational database with date, user and default value.

Create a basic User Stored Procedure (uspGetUserDefaultValue) which takes username (AD Login) as a parameter, selects on the mapping table based on username passed and returns a single value.

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.

We would need to add:

Microsoft.AnalysisServices

Microsoft.AnalysisServices.AdomdClient

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.

Now we create/rename our namespace and class to like

Project/Assembly: AnalysisServicesCustomLibrary

Namespace: CustomMethodCollection

Class: CustomMethods

or whatever you like.

Create a public static function like:

public static string GetUserDefaultValue(string userName)

{

OleDbConnection connection;

try

{

connectionString ="provider=sqloledb;server=;database=;trusted_connection=yes";

connection= new OleDbConnection(connectionString);

connection.Open();

OleDbCommand command= new OleDbCommand("uspGetUserDefaultValue", connection);

command.CommandTimeout = 0;

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new OleDbParameter("@UserLogin", OleDbType.VarChar, 50));

command.Parameters[0].Value = userName;

DataTable dataTable = new DataTable("DefaultValue");

OleDbDataAdapter dataAdapter= new OleDbDataAdapter(command);

dataAdapter.Fill(dataTable);

connection.Close();

if (dataTable.Rows.Count == 0)

return "empty";

string defaultValue = dataTable.Rows[0][0].ToString();

return "[Dimension].[Dimension Attribute].&[" + defaultValue + "]";

}

catch (Exception ex)

{

errorMessage = "Some error: " + ex.Source + " Message: " + ex.Message;

throw new Exception(errorMessage, ex);

}

The return 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.

Do a quick test to see if you are getting the desired output from Stored Procedure and Class Function.

Now we move to Analysis Services solution. First step is to add reference to AnalysisServicesCustomLibrary project under Assemblies folder.

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 DefaultMember enter:

STRTOMEMBER( AnalysisServicesCustomLibrary.CustomMethodCollection.CustomMethods.GetUserDefaultValue(UserName))

Here UserName is an Analysis Services keyword to get current logged in user’s account.

STRTOMEMBER converts the string passed by custom library’s function in a member.

Build/Deploy/Process cube. Run a query or browse the cube and you should have the default value against logged in user.

DEBUG

To debug your code, move to your custom library project. Go to Debug-->Attach to Process and select Msmdsrv.exe process. Add a breakpoint and try querying the dimension using the function.

References:

Debugging Stored Procedures

From Date To Date in PerformancePoint Analytical Chart

2011-01-28T04:33:00.000-08:00

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.

Start with creating a Report and select Analytical Chart. Add your required Dimensions and Measures including Time dimension.

Once done, move to the Query tab of the report it should be something like below depending on what artifacts you drop :

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.

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.

With every parameter you add you would find a value added to your query canvas like <> and <>. For now it does not matter where they are just add parameters.

Move to your query and where ever your time dimension is used replace that with:

HIERARCHIZE ( {<>:<>} )

Your final query would be like:

Save/Publish your report.

In your dashboard section add filters from Filters tab. I am using Time Intelligence Calendar filters but any type of filter can be used.

If you opt to use any other type of filter remember to set your values in Report query respectively.

Now we have our filters and report. We add them to the dashboard page like so:

Now we start linking filters and report. First link FromDate filter like so:

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.

Similarly we link ToDate filter like:

All done. Save/Publish and Preview.

Here dates selected in 14^th September, 2009 and the report displays data for that day. Now changing the ToDate to 18^th September, 2009 would show:

And we have From Date and To Date filters working on an Analytical Report.

This is very intuitive way of showing data to end users. A lot of stakeholders ask for this feature especially for Trend Charts/Reports.

Downside to this implementation is that we lose the right click contextual features of analytics like drill downs, decomposition tree etc.

MDX Date dimension in Descending Order

2010-10-06T08:53:00.000-07:00

Scenario:

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.

select
[Measures].[Some Units] on 0,
ORDER (
nonempty(
[time].[year-quarter-month-week-date].members,[Measures].[Some Units])
,[time].[year-quarter-month-week-date].CurrentMember.MemberValue , desc) on 1
from [MyCube]

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).

No need to change the web.config!

The date dimension is SSAS business intelligence time dimension (auto generated).

Reference:

Changing the limit on the number of items returned in a filter

Pass Parameter to Analytical Report PPS 2010

2010-05-20T02:02:00.000-07:00

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.

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.

Right click 'Data Connections' or from 'Create' ribbon select 'Data Source'. Configure to your already deployed cube.

Right click on 'PerformancePoint Content' select 'New -> Report' or from 'Create' ribbon select 'Other Reports'. From the dialog box select 'Analytical Chart'. Press OK.

Name your report as desired I name it 'Factorize Me'.

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'.

I am using a Banking analysis services cube provided by Microsoft but this method can be used for any measure.
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.

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.
Here is the query created by PerformancePoint Services:
SELECT
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] } )
ON COLUMNS,

{ [Measures].[Sales Amount]}
ON ROWS

FROM [Bank DW]

We would be editing it and adding a parameter. First add the required measure something like 'Target'.
WITH MEMBER [Measures].[Target] as ([Measures].[Sales Amount] * <> )
here <> 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.
Finally we use this new Target measure on ROWS along with sales amount measure. Here is the final query:
WITH MEMBER [Measures].[Target] as ([Measures].[Sales Amount] * <> )

SELECT
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] } )
ON COLUMNS,

{ [Measures].[Sales Amount], [Measures].[Target] }
ON ROWS

FROM [Bank DW]

Assign a default value to our parameter <> and switch to design pane where you would be seeing 2 measures now (Sales Amount and Target).

Next create dashboard add the report. Right click the dashboard and select 'Deploy to SharePoint...'.
Wait for the SharePoint page to load and you can see the report.

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.

Rename web part to your liking.

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'.

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)
Cool feature to SharePoint 2010 to be able to connect web parts to PerformancePoint content.

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.
External Links:
MDX in Dashboards, Scorecards, and Views?

SharePoint 2010 location of log files

2010-05-12T23:34:00.001-07:00

If anyone is wondering what is the location of SharePoint 2010 log files. Copy the line below and paste it in run:
%programfiles%\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

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

PerformancePoint and Default Fitler Values

2010-05-03T01:52:00.000-07:00

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:

http://blogs.msdn.com/performancepoint/archive/2008/02/12/always-display-default-filter-selection-in-dashboards.aspx

(Image taken for the above article)

XSL Conditional Format based on QueryString using SharePoint Designer

2010-04-26T04:56:00.000-07:00

Today I had to add a success message on ContactUs page built on MOSS website using SharePoint Designer.
I had already built a ContactUs form, built by using DataFormWebPart A.K.A DataViewWebPart, where a user can enter some information and submit using only out of the box components using SharePoint Designer (which I will cover in this blog sometimes later).
Problem was, information was submitted successfully but no message was displayed to end user. So following is how I accomplished it.

First add a parameter in 'Common Data View Tasks' as shown in the screen shot.
Press 'New Parameter' and select 'Parameter Source' as Query String, assign a name like in this case 's' and a default value.

This would declare a xsl parameter in our DataView web part like:
<xsl:param name="Success">0</xsl:param>

Next is to redirect the same page with a querystring value which would indicate success.For that 'Submit' button is changed to send querystring "?s=1" on submit.
To do that:

Right click 'Submit' button, choose 'Form Actions...'. In 'Navigate to page' append the querystring.
Press Ok.

Finally in the XSL we use this value of query string Success as:
If page submitted using the Submit button
<xsl:if test="$Success = 1" >
<table>
    <tr>
      <td>Thank you for your request</td>
    </tr>
</table>
</xsl:if>

Else any other value we show the table containing the contact us form.
<xsl:if test="$Success != 1" >
<table border="0" width="80%">
    <xsl:call-template name="dvt_1.body">
      <xsl:with-param name="Rows" select="$Rows"/>
    </xsl:call-template>
</table>
</xsl:if>

Other possibilities can be to show an Alert which can be done by using the following code in side the conditional xsl:
<xsl:text disable-output-escaping="yes">
</xsl:text>

Good Day to all.
External Links:
Embed HTML in XML & Retrieve it with XSL

KPI Threshold Values From Table

2010-03-31T08:53:00.003-07:00

Today 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.
Client needed to store these thresholds values in an independent table perhaps accomplishing 2 things:

Access cube via excel and use these values
Change in thresholds did not effect PPS KPIs.

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.

So following is the methodology to overcome this limitation:

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.
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.

These were all the backend changes required.
Now we move to PerformancePoint server's Dashboard Designer.

For each KPI in the Actual row use the following CASE expression:

CASE
when
[Measures].[My Value] >= ([Measure].[Green - Fact Target Values], [Dim Source].&[System XYZ])
Then 1
when
[Measures].[My Value] >= ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])
then 0
when
[Measures].[My Value] < ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])
then -1 END

WHERE
[Measure].[My Value] = Factual value upon which all measure are based.
[Measure].[Green - Fact Target Value] = Threshold value for Green indicator to be shown like 90 etc.
[Measure].[Yellow - Fact Target Value] = Threshold value for Green indicator to be shown like 80 etc.
([Measure].[Yellow - Fact Target Value], [Dim Source].&[System XYZ]) = Returns values for a particular system since I had multiple source systems.

In the Target value use [Measure].[My Value] in 'Data Mappings' and 'Source Data' in 'Calculations' to show values on dashboard.

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.

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.

Environment: SQL Server 2005, Performance Point Server 2007 SP2

Alternate Approach
Use SQL Server Analysis Services KPI do accomplish the same since it provides much simpler and easier way to use external goal values.

External Links:

http://blogs.msdn.com/performancepoint/archive/2008/01/09/band-by-stated-score.aspx
MS Press Microsoft SQL Server 2005 Analysis Services Step By Step Chapter 8 page 213

Password Protected Access DB and SSIS

2010-02-11T00:18:00.000-08:00

Today 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:

To remove this error double click your new connection in the Connection Managers.

Move to the 'All' tab.

Enter the password against the propery "Jet OLEDB:Database Password".

And Test Connection. Success.

Where Clause and SSAS Calculations

2009-12-04T06:45:00.000-08:00

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.
Scenario: A class contains boys and girls. We need to calculate the passing percentage of students that are boys.

Previously the rule was to get passing percentage of boys.
Total number of boys that pass / Total number of students in class
Correct rule should have been:
Total number of boys that pass / Total number of boys
Yeah, I know this can be done in a giffy in SQL Server but Analysis services is a totally different ball game.

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.
To work around our constraint we created Named Query in cube which returned all the dimensions along with every record where boys = 1.
Finally we had to filter measure based upon result dimension (P meaning Pass). So in Calculations tab of cube created a calculation as:

Round(([Measures].[Vw Get All Boys Count], [Result Dim].[Result].&[P]) / [Measures].[Vw Get All Boys Count] * 100,0)

And got the result we were expecting.

Alerts on SharePoint List

2009-12-03T06:59:00.000-08:00

Today I had to troubleshoot a SharePoint environment where alerts were not working.
Firstly this environment was not configured to send mail. So I started by installing IIS's SMTP client component. Configured the relay server.
Then configured the 'Outgoing E-Mail Settings' in Central Administrator.
Added an alert on a custom list.
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:
Disable alerts on the site

stsadm.exe -o setproperty -pn alerts-enabled -pv "false" -url http://problemsite

Enable alerts on the site

stsadm.exe -o setproperty -pn alerts-enabled -pv "true" -url http://problemsite

Added a new item in the custom list and it working like a charm. Also works when an anonymous user adds a list item.

External Links:
1. Troubleshooting Alerts
2. Configure outgoing e-mail settings

OLE DB Parameterized SQL Command in Execute SQL Task

2009-11-05T23:26:00.000-08:00

There are very good articles on the internet explaining how to use different parameters in 'Execute SQL Task' control of SSIS.

I needed to use the same parameter twice in my query so this is what i did:

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:

WHERE (? = GetDate() OR CreatedDate > ? )

Reads where fromdate is equal to today or created date is greater than fromdate.

External Links

1. Running Parameterized SQL Commands Using the Execute SQL Task – I
2. Execute SQL Task on MSDN

Multi-Value Parameters, Stored Procedures and IN operator

2009-10-23T07:08:00.000-07:00

Today, I was adding parameters to my Reporting Services report and made a very common mistake when using multi-value parameters. My assumption:

SELECT * FROM [SomeTable] WHERE ID IN ( @multiValueParameter )

No wonder it didn't work since this is interpreted as:

SELECT * FROM [SomeTable] WHERE  ID IN ( '1,2,3,4' )

instead of

SELECT * FROM [SomeTable] WHERE  ID IN ( '1','2','3','4' )

I recalled I had done this before in some other project and after a visit to the archive I found it.

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):

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

And now you can use @TableOfArrayItems anywhere in you query/stored procedure like:

SELECT * FROM [SomeTable] WHERE ID IN ( SELECT ID FROM @TableOfArrayItems)

And success!

External References: