VMware image with Sharepoint 2010, MSSQL 2012, SSRS, Performance Point and PowerPivot

This is how you can install a VMware Image with Windows 2008 r2, SharePoint 2010 and Microsoft SQL Server 2012 Denali.

I will also show you how you can configure SSRS, Performance Point and PowerPivot.

At first i tried to install this without a domain, and got everything working except PowerPivot.

At present time there is no workarounds for getting PowerPivot to run in a non domain environment.

Bummer, so i had to scrap it and start over with a domain.

Anyway installing a domain is pretty easy, so just live it and use a domain in your SharePoint playground.

It just makes your life so much easyer.

Sorry no screenshots, if there are parts you don't understand please contact me.

I spent a couple of days doing this, so maybe someone will find this useful.

Install windows

1. Install VMware Workstation.
2. Install Windows 2008 R2 With SP1 with username dev.
3. Change computername to DevServer.
4. Turn off the Firewall.
5. Turn off UAC.
6. Turn off IE ESC (Server manager down on the right).
7. Add server roles: Web Server (IIS) and Application Server.
8. Run windows update over and over again, until the green checkmark of delight appears. (Microsoft PLEASE, 2012 and this is still not automated in any way) .
9. Power off the VM, and take a snapshot. VMware can only clone snapshots taken during power off (just a little tip if you need to clone it in a later life).

Install the domain controller

1. Click Start, then run.
2. Type in dcpromo and hit enter.
3. Don't check use advanced mode installation.
4. Select create a new domain in a new forest.
5. FQDN of the forest root domain: xtrm.local or whatever.
6. Forest functional level, just select windows 2008 r2.
7. Select additional options for this domain controller, make sure DNS server is selected.
8. Change to static IP in the IPV4 settings of your network card.
9. For example IP: 10.10.10.1, Subnet mask 255.255.255.240.
10. Continue the wizard: Select no, i will assign static IP addresses to all physical network adapters.
11. A delegation for this DNS server could not be created. Click Yes.
12. Location for Database, Log Files ans SYSVOL, just leave the defaults.
13. Directory services restore mode administrator password: Use a strong password.
14. Summary, click next, click Finish, and reboot.
15. Switch back to dhcp, obtain ip address and dns automatically because of internet access through the VM.
16. Now start Active Directory Users and Computers.
17. Create 3 new users: sqladmin, spadmin, spfarm. Use same password, user cannot change password, password never expires.
18. Add spadmin to the Domain Admins group.
19. Reboot, and we are done with setting up the domain.

Install Microsoft Sql Server 2012

1. When installing MSSQL 2012 Denali, make sure you install these options: Reporting Services - Native, Reporting Services - SharePoint, Reporting Services Add-in for SharePoint Products.
2. In other words, select all features except the replay controller / client.
3. Install a sql instance named SharePoint.
4. Run all services with the xtrm\sqladmin user.
5. Add dev, sqladmin, spadmin and spfarm user to the sql server / analysis services administrator group.
6. Install and Reboot.
7. Microsoft SQL Server 2012 is now up and running.

Install SharePoint 2010

1. Login as spadmin, IMPORTANT !.
2. Install SharePoint 2010 Prerequisites.
3. Start the SharePoint 2010 server install.
4. Wait until done.
5. Run the SharePoint Products Configuration Wizard (Started automatically).
6. Select Create a new server farm.
7. Database server: DevServer\SharePoint
8. Database name: SharePoint_Config
9. Database Access account: xtrm\spfarm
10. Passphrase: Enter a strong password.
11. Force port number for SharePoint Central Administration.
12. Select NTLM.
13. Finish the wizard.
14. Start SharePoint Central Administration,, configure your SharePoint farm (If not automatically started).
15. Service account: Use existing xtrm\spfarm.
16. Create a top level site, Team Site or whatever.
17. SharePoint 2010 is now up and running in a farm on Microsoft SQL Server 2012.

Configure Reporting Services in SharePoint 2010

1. Start SharePoint Central Administration.
2. Click on General Application Settings.
3. Under SQL Server Reporting Services, click on Reporting services integration.
4. DON'T bother, because this is for SQL Server 2008 /2008 R2 only.
5. This is how you integrate against SQL Server 2012:
6. Start SharePoint 2010 Management Shell, right click and do a run as administrator. IMPORTANT !
7. Type in: Install-SPRSService
8. Type in: Install-SPRSServiceProxy
9. Type in: get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
10. Now go back to SharePoint Central Administration.
11. Click on System Settings, Manage services on server.
12. Then you will see a service named: SQL Server Reporting Services Service with status Started.
13. Goto Application management, Service Applications, Manage Service Applications.
14. Select New then SQL Server Reporting Services Service Application.
15. Name: SSRSApp, New app pool: SSRSAppPool, App pool account: xtrm\spfarm.
16. Select windows authentication, select web application association: SharePoint 80.
17. And we are done with SSRS integration against SharePoint 2010.

Configure Performance Point in SharePoint 2012

1. Just make sure PerformancePoint Service is started in System Settings, Manage Services on Server.
2. And that the Performance Point Service application has been created under Application Management, Manage Service Applications.

Configure PowerPivot in SharePoint 2010

1. Login as spadmin.
2. PowerPivot is a separate install, so first we need to install it.
3. Run Microsoft Sql Server Setup.
4. Add features to an existing installation.
5. Next screen, you need to select Perform a New installation of Sql Server 2012.
6. Then select Sql Server PowerPivot for SharePoint, do not select add database instance.
7. It will default to named instance PowerPivot.
8. Run the Sql Server Analysis Services as the xtrm\sqladmin account.
9. Add dev, spadmin, spfarm and sqladmin as administrators.
10. Finish the wizard and it will be installed.
11. Start the PowerPivot Configuration Tool.
12. Select Configure or Repair PowerPivot For SharePoint.
13. Default Account: xtrm\spfarm
14. Database Server: devserver\SharePoint
15. Enter SharePoint passphrase.
16. Click on Register Sql Server Analysis Services (PowerPivot) on local server and enter password for sqladmin user.
17. Click Validate, then Run.
18. Click yes on the confirm messagebox.
19. And wait until it succeeds.
20. Start SharePoint Central Administration
21. If you look at System, Services On Server, you now have a new service named: SQL Server PowerPivot System Service.
22. Goto Application Management, Manage Service Applications.
23. You will have a new Service Application named: Default PowerPivot Service Application.
24. Now there are 2 farm solutions that has been deployed in the farm.
25. To view them goto System Settings, Manage farm solutions.
26. PowerPivotfarm.wsp is globally deployed.
27. PowerPivotWebApp.wsp is deployed to each web app.
28. So if you create a new webapp, then you need to deploy this solution to the new webapp.

Great, finally everything is installed and configured.
Later i will write a post about how you actually use what you have installed:

• SSRS both Native and SharePoint.
• Performance Point.
• PowerPivot.

A final tip: ALWAYS run IE and SharePoint Management Console as administrator.

(Right click and run as administrator). If you don't do this there will be a lot of menu options in

SharePoint Central Administration that will be missing.

And the scripts will fail with some cryptic errors.

If you find any errors above, unnecessary steps or other ways to improve the sequence,

please let me know.

How to calculate median in MSSQL

Definition of median:
Median is described as the numerical value separating the higher half from the lower half of a
sample, a population or a probability distribution.

The median value of a list of numbers can be found by arranging all the observations from lowest value to the highest value and picking the middle one.
If there is an uneven number of observations, then median will be the middle value.
If there is an even number of observations, the median is then usually defined to be the mean of the two middle values.

So lets say we have this sample data:

Lot	Wafer	ResultName	Value	Comment
8485474	1	someresult	50
8485474	1	someresult	40
8485474	1	someresult	30	<- Median 1 / Median 2     (Median1 + Median 2) / 2 = Median = 30
8485474	1	someresult	20
8485474	1	someresult	10
8485474	2	someresult	60
8485474	2	someresult	50
8485474	2	someresult	40	<- Median 1
8485474	2	someresult	30	<- Median 2     (Median1 + Median 2) / 2 = Median = 35
8485474	2	someresult	20
8485474	2	someresult	10

First we need to sort and number the resultvalues ascending.
Then we need to get the result count for each wafer.
To do this, we can use the row_number() function / count(*) function together with over / partition by.

(select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join 
  resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult') as medianvalues

The above query will give us this resultset:

Lot	Wafer	ResultName	Value	RowNum	ResultCount
8485474	1	someresult	10	1	5
8485474	1	someresult	20	2	5
8485474	1	someresult	30	3	5
8485474	1	someresult	40	4	5
8485474	1	someresult	50	5	5
8485474	2	someresult	10	1	6
8485474	2	someresult	20	2	6
8485474	2	someresult	30	3	6
8485474	2	someresult	40	4	6
8485474	2	someresult	50	5	6
8485474	2	someresult	60	6	6

Now we can filter out the middle rows with the follwing formula:

(resultcount + 1) / 2 and (resultcount + 2) / 2)

So for wafer 1:

(5 + 1) / 2 = 3
(5 + 2) / 2 = 3  (3.5 will be 3 when converted to int)

And for wafer 2:

(6 + 1) / 2 = 3 (3.5 will be 3 when converted to int)
(6 + 2) / 2 = 4 

So now that we have the two middle rows, we can just do an average of the two rows:
So for wafer 1:

30 + 30 / 2 = 30  (Row 3 + Row 3) / 2

And for wafer 2:

30 + 40 / 2 = 35  (Row 3 + Row 4) / 2

Now let's put it all together:

select lot, 
       waferno, 
       resultcount, 
       avg(resultvalue) as median, 
       (resultcount + 1) / 2 as middlerow1, 
       (resultcount + 2) / 2 as middlerow2
from
  (select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult') as medianvalues
where rownum in((resultcount + 1) / 2, (resultcount + 2) / 2)
group by lot,waferno, resultcount
order by lot,waferno

Or if you prefer the new with statement:

with medianvalues as
(
select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult'
)
select lot, 
       waferno, 
       resultcount, 
       avg(resultvalue) as median, 
       (resultcount + 1) / 2 as middlerow1, 
       (resultcount + 2) / 2 as middlerow2
from medianvalues
where rownum in((resultcount + 1) / 2, (resultcount + 2) / 2)
group by lot,waferno, resultcount
order by lot,waferno

The above query will give us this resultset:

Lot	Wafer	ResultCount	Median	MiddleRow1	MiddleRow2
8485474	1	5	30	3	3
8485474	2	6	35	3	4

Formatted TimeDiff UDF for MSSQL

I just created a UDF function for displaying a formatted time diff value in MSSQL.
Why ? I googled this and found a lot of solutions that did not work :(
So i just had to look into it.

I wanted the time difference to be displayed in the following format:
HHH:MM:SS (@type = 0)
Where HHH = Total number of hours,  so if the difference is one week,
then this value will be 168. (168:00:00).

I also created another variant:
D MM:HH:SS (@type = 1)
Where D = Total number of days, so if the difference is one week,
then this value will be 7. (7 00:00:00).

create function [dbo].[TimeDiff](@date1 datetime, @date2 datetime, @type smallint)
returns varchar(50)
as
begin
declare @days int
declare @totalhours int
declare @hours int
declare @minutes int
declare @seconds int
declare @ret varchar(50)
set @seconds = datediff(ss, @date1,@date2) 
set @totalhours = @seconds / 3600 
set @days = @totalhours / 24 
set @hours = @totalhours - (@days * 24)
set @minutes = ((@seconds - (3600 * @totalhours)) / 60)
set @seconds = ((@seconds - (3600 * @totalhours) - (60 * @minutes)))
if(@type = 0)
begin
    --HHH:MM:SS 
    set @ret = case when @totalhours < 10 then '0' + convert(varchar(20), @totalhours) else convert(varchar(20), @totalhours) end + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2)
end
else
begin
    --D HH:MM:SS
    set @ret = convert(varchar(20), @days) + ' ' + right('0' + convert(varchar(20), @hours), 2) + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2)
end
return @ret
end

Custom multiply aggregation function in AS2008

In a recent project of mine i needed a custom aggregation function that needed to multiply som yield numbers of a specific measure in a given dimension.

My yield numbers are actually stored as a number between 0 and 100. And not between 0 and 1 which i guess are more common when expressed as percentage.
The reason for this is some limitations in our 3rd party reporting charts.
Any way this is how i made the custom aggregation function that will work with both variants:

Create a c# class library.
Add a reference to this dll (ADOMD.Net interfaces):

C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msmgdsrv.dll

Add a class named ASUtils with the following content:

namespace ASUtils
{
    using System;
    using Microsoft.AnalysisServices.AdomdServer;

    public class Multiplication
    {
        [SafeToPrepare(true)]
        public static Nullable<double> Multiply(Set inputSet, Expression inputExpression, double divider, bool excludeZeroValues)
        {            
            Nullable<double> num = null;
            bool first = true;
            foreach (Tuple tuple in inputSet.Tuples)
            {
                MDXValue mdxValue = inputExpression.Calculate(tuple);
                bool isEmpty = (mdxValue.ToChar() == 0x00);
                if (!isEmpty)
                {
                    double value = mdxValue.ToDouble();
                    if (value != 0 || (value == 0 && excludeZeroValues == false))
                    {
                        if (first == true)
                        {
                            num = value / divider;
                            first = false;
                        }
                        else
                        {
                            num *= value / divider;
                        }
                    }
                }
            }

            return num * divider;
        }
    }
}

I did not find any built in functions that could check if a value was empty or not, but this seems to work:

bool isEmpty = (mdxValue.ToChar() == 0x00);

Any other solutions out there ?

Build the project, then in your AS2008 cube project, add a reference to the new class library.
In your cube, under calculations. Add a new calculated member named YieldM with this code:

ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 100.0, false)

[Process] = Dimension
[ProcessNoGroup] = Hierarchy
[Measures].[Yield] = Measure containg numbers between 0 and 100.

If the measure contains numbers beteen 0 and 1, then you can change the multiplyer to 1 like:

ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 1.0, false)

Empty values are always eliminated from the multiply.
You can also exclude 0 (zero) values by changing the last parameter to true.

So if we now look at the yield measure over time and process, we have this test data:

Time Dimension	Process Dimension	Yield
W01	Process1	94.88
W01	Process2	96.42
W01	Process3	95.03
W02	Process1	98.86
W02	Process2	96.42
W02	Process3	94.03

When we aggregate this by week and look at our calculated YieldM measure:

Time Dimension	YieldM	Formula
W01	86.93	(94.88*96.42*95.03)
W01	89.63	(98.86*96.42*94.03)

And voila, our custom aggregation is working as it should.

Using ILMerge and Eazfuscator.NET

I ran into some new requirements on my last project.
I've made an autoupdate system which will be used for an upcoming project, and i wanted to merge all the dll's into the autoupdate executable.
Just to minimize trouble and communication when downloading the autoupdater.
I also wanted to obfuscate my executable.

Merge Assemblies:

Now first i stumbled upon ILMerge, which is used to merge assemblies.
This is a tool release by Microsoft so it should be safe to use, you can read more about it here:
http://research.microsoft.com/en-us/people/mbarnett/ilmerge.aspx

It's pretty simple to use:

ILMerge.exe /out:MyMergedExe.exe MyOriginalExe.exe Myfirst.dll MySecond.dll

First, all my projects use framework version 4, so we need to add some parameters so that ILMerge will be able
to merge a framework version 4.0 project.

ILMerge.exe /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MyMergedExe.exe MyOriginalExe.exe Myfirst.dll MySecond.dll

In the ILMerge folder (C:\Program Files (x86)\Microsoft\ILMerge\), create a file named ILMerge.exe.config:

<?xml version ="1.0"?>
<configuration>
 <startup useLegacyV2RuntimeActivationPolicy="true">
  <requiredRuntime safemode="true" imageVersion="v4.0.30319" version="v4.0.30319"/>
 </startup>
</configuration>

Great, now everything runs and works with .NET Framework v4.0.

Then i wanted a more dynamic way to include the DLL's without editing the ILMerge command line every time.
Luckily there is a parameter named /wildcards

ILMerge.exe /wildcards /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MyMergedExe.exe MyOriginalExe.exe *.dll

And to get some more information about the merge process, add the /log parameter.

That's basically everything you need to know about merging assemblies into a single executable.

Obfuscate Executable:

So let's see how we can obfuscate the executable.
There is a lot of obfuscators out there.
I tried 3-4 variants, and found a lot of buggy obfuscators out there. Some crashed, some did not work, some costs a lot of $.
Anyway i ended up with the free EazFuscator.NET. It works, and has a nice commandline interface.
You can read more about it here:
http://www.eazfuscator.net

Eazfuscator.NET.exe -s -o MyObfuscatedExe.exe MyMergedExe.exe

I also added -s to get some statistics about the merge process.
You dont need to use the GUI application that comes with Eazfuscator.NET.
We will use the commandline interface only.


Automate, using PostBuild event:

Now, let's automate all this information into a complete build:

In your project, add this to the postbuild event:

if $(ConfigurationName) == Release $(ProjectDir)\Build\PostBuild.cmd "$(TargetDir)", "$(TargetName)"

Create a folder in your project and name it Build.
Inside the build folder, create a file named Postbuild.cmd with this content:

REM Add this to the project Postbuild event: 
REM if $(ConfigurationName) == Release $(ProjectDir)\Build\PostBuild.cmd "$(TargetDir)", "$(TargetName)"
ECHO --------------------------------------------------------------------------------------------------------------------------
ECHO Executing PostBuild.cmd with parameters: TargetDir=%1, TargetName=%2
CD %1
ECHO Merging Assemblies Into Executable: %2
IF NOT EXIST %1MergedRelease mkdir %1MergedRelease
"C:\Program Files (x86)\Microsoft\ILMerge\ILMerge.exe" /wildcards /log /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MergedRelease/%2.exe %2.exe *.dll
ECHO Obfuscating Executable: %2
IF NOT EXIST %1MergedRelease\ObfuscatedRelease mkdir %1MergedRelease\ObfuscatedRelease
"C:\Program Files (x86)\Eazfuscator.NET\Eazfuscator.NET.exe" -s -o MergedRelease/ObfuscatedRelease/%2.exe MergedRelease/%2.exe
ECHO --------------------------------------------------------------------------------------------------------------------------

Now when building a release build:
The normal exe with separate dll's will be created under

$(ProjectDir)\Bin\Release\

Under this folder there will be a subfolder named MergedRelease:

$(ProjectDir)\Bin\Release\MergedRelease\

In this folder our merged exe with the pdb file will be created.

Under this folder there will be a subfolder named ObfuscatedRelease:

$(ProjectDir)\Bin\Release\MergedRelease\ObfuscatedRelease\

In this folder our merged and obfuscated exe will be created.

Now thats all, a merged and obfuscated exe everytime you do a release build.