How to change the DataSourceView of an existing dimension

If you have two or more data source views on your analysis service project, there is a definite possibility to create dimensions using wrong data source view. But you may have confused later on, since SSAS doesn't provide you a direct way to re-change the data source view.

I found that error recently, when I deleted some columns from the Time Dimension on the database, but it wasn't reflect on my Time Dimension on the cube. When I right click and click on 'Edit Data Source View' on the Data Source View pane of the dimension editor window, it was redirected me to a different data source view. So it was a good way to identify whether the dimension is being created using appropriate data source view.

But the solution was rather simple. You only have to do open the dimension using code view and change the DataSourceViewID tag to correct data source view. (It is recommended to get the correct data source view ID from another dimension if possible)

Above method can be used to change the data source view from an existing cube as well.

Finally I would like invite you to read my new and more interesting article on How to use Forex Trading as Part Time Earning Method. It would be more beneficial and interesting thing for you.

Connecting Informix databases through SSIS

I was quite confused to find a way to connect Informix database through SSIS in Windows x64 environment few days back. I went through lot more articles on the internet, but couldn't be able to find a proper direction. But the experience I have gathered lead me to achieve the connection sucessfully and it's drive me to write an article on how to create connection between Informix through SSIS.

First download the latest version of Informix Client Software Development Kit (32bit) from IBM's website. It's a freeware but you have to register on the IBM's website to download the product. Don't go for the 64bit versions since BIDS is still a 32bit application, your 64bit providers will not be shown on the providers list.

You can download the latest version of client SDK from here.

The next step is to add a path to "C:\Windows\SysWOW64" to the begining of the path environmental variable before installing the client SDK. Otherwise you will promt an error message saying add a prefix to path variable. Then install the product including IBM data server driver package.

After intalling the client SDK, use Setnet32 tool to configure a default Informix server. You can find Setnet32 application under "All programs>IBM Informix Client SDK".

Fill Server information and Host information with correct details to your Informix server. You will find the INFORMIXDIR and INFORMIXSERVER environmental variables are filled with values regarding to default server. You don't need to create those environmental variables manually on windows environmental variables.

Then add a new entry to your servises file. Service file can be found in "C:\Windows\System32\drivers\etc".

The format of the service file is clearly mentioned to the begining of the file. In my case the first entry is my service name then port number and protocol.

After editting the service file, you can create a new Open Data Base Connection (ODBC). Don't use Data Source Administrator tool in "Program Files>Administrative Tools" insted use "C:\Windows\SysWOW64\odbcad.exe" to add and configure new data source.

Select IBM INFORMIX ODBC DRIVER and click finish. Then configurations window will pop up. Give any data source name and fill appropriate connection details. Click on apply and test connection.

Use ConnectTest Demo tool to test connection and view table data.

Now you have allmost configured your environment to connect Informix. Open BIDS and create a new SSIS project. Go to add new data source, you will find "IBM OLE DB Provider for Informix" is listed under OLE DB providers. Create new connection to the Informix server and have some fun with Data.

The server name should be given in the format of database@servername.

Set Run64BitRuntime into false under project properties (Project>Properties>Debugging).

Finally I would like invite you to read my new and more interesting article on How to use Forex Trading as Part Time Earning Method. It would be more beneficial and interesting thing for you.

Use SSIS system variables effectively

Package variables are an important piece of component to create dynamic SSIS packages. There are two types of variables inside SSIS, system variables and user defined variables. System variables are not editable but can use as read only variables. The values of the system variables are set by the package for the purpose of tracking metadata such as Package ID. Effective use of system variables increases the performance of SSIS packages and using system variables is a good development practice as well. A list of system variables included in SSIS is mentioned bellow.

• System::ContainerStartTime
• System::CreationName
• System::LocaleID
• System::ParentContainerGUID
• System::TaskID
• System::TaskName
• System::TaskTransactionOption
• System::CancelEvent
• System::CreationDate
• System::CreatorComputerName
• System::CreatorName
• System::ExecutionInstanceGUID : Unique ID for each package execution.
• System::FailedConfigurations
• System::InteractiveMode
• System::LastModifiedProductVersion
• System::MachineName : Name of the computer
• System::OfflineMode
• System::PackageID : Unique Id generated for the package. 
• System::PackageName : Name of the package.
• System::ProductVersion
• System::StartTime : Start time of the package.
• System::UserName : Package execution user
• System::VersionBuild
• System::VersionComments
• System::VersionGUID : Unique GUID for the package version
• System::VersionMajor
• System::VersionMinor

Two package execution instances of same package by diferent users on different machines is dipicted on the following figures. Notice that PackageID is an unique Id for the package while ExecutionInstanceGUID is unique for the package execution.

Sharepoint Content Deployment Using Performancepoint Import Items Wizad

Sharepoint content deployment can be done in different ways. Its very useful feature in sharepoint since devolopers would choose the most suitable deployment technique rather creating same thing again and again. This is a another major factor to commercialize sharepoint products among most of the companies and organizations. Some popular deployment techniques  are listed below.

• If two serverfarms are physically connected by intranet, Content deployment and path jobs is the preferable solution for content deployment.
• If two server farms are physically diconnected, you can use sharepoint backup and restore to deploy your content.
• If top level site to be deployed to a subsite or vise vasa, sharepoint import and export make your wish.

But today Iam going to introduce a new method to deploy performancepoint contet. This method can only be used to deploy performancepoint content rather. As BI engineers most of you have to deploy sharepoint content as performancepoint content. Therefore deployng performancepoint content between serverfarms is to be done quite frequently.

So should we have to recreate our performancepoint content from the scratch, no absolutely not. Or else should we get a backup and restore it to the new site in other server farm. No its not a wise thing to do, since when you wanted to get restore previously created performancepoint content, your new site has almost created. Then you cannot lose your data. Actually you don't want to restore a whole site to get few performancepoint content.

Therefore the preferable technique would be to use Performancepoint Import Items wizad.

To import items there should be a performancepoint workspace file (*.ddwx file). When you working with performancepoint designer you can save workspace to a .ddwx file. Please make sure that only edited items (Items in the left hand pane) can save to a file. If you are newly opened the performance point designer, you should double click on each item to get those items to the left hand pane.

Then go to the BI site where you want to restore the content. (If it is a totally isolated from the parent server farm, it doesn't matters) Open the performancepoint dashboard designer. Click on the Import items. A dialog box will pop up and ask you for a source file, then browse and locate a workspace file where you want to import.

Then you have to map source lists (Data connections and Perfomancepoint content) with destination lists. The important point to note here that is, you can directly import items to lists in the subsites as well.

Then click next to start the content deployment.

Performacepoint import items wizad is a useful piece of component inside the performancepoint dashboard designer. It reaveals you a new ere to content deployment and save your development time by easily reusing performancepoint content.

How to connect two or more Performancepoint Filters to Scorecards and Reports

In this post I am not going to introduce a new thing but I would like to demonstrate a few important steps, that might mess around you and could delay your developments. That is how to make connections among performancepoint filters, scorecards and reports.

Usually connecting one filter to a report and scorecard might not a difficult task to you. Therefore I am forcusing on how to connect two or more filters to both reports and scorecards.


The scenario I have taken here like this,

• I have two performance point filters for Month and Year.
• I have a Scorecard which displays few KPIs.
• I have a Report with some tables and charts.

You can do this in two different ways.

1. The most common method is create a dashboard in performancepoint designer and deploy it to a sharepoint site..
2. The next method is create dashboard layout in sharepoint itself, and insert performance point content as webparts.

Eventhough above two methods are seems to be two different approaches, the connection settings are almost same. But I hope to demonstrate above two methodologies. I'd like to guide you through images, since this not very much technical related thing.

To make connections between reports and filters, goto the connections in the report and create a connection to each of the report parameter.

To create connections inbetween scorecards and filters, do the same thing to the scorecards as shown below.

I am suggesting here to connect filters to the EndPoint_Page of the scorecards. If you are using sharepoint 2010, EndPoint_page cause for disabled decomposition tree. (To get decomposition tree right click on the scorecard and select decomposition tree.) To enable decomposition tree install the following hot fix to the sharepoint server.
http://support.microsoft.com/kb/2496951

If you have done correctly, your all connections would work well. Even this is not a technical article, I hope this might helpful to you when you are get stuck with selecting proper configurations in performancepoint.

SSIS package variables cannot update and use inside a dataflow task

In this article I am going to introduce an alternative solution for a common problem in SSIS. That is updated values of a SSIS package variables cannot use inside of a dataflow task. I found this problem when I was going to Increase the ErrorCount, if any missmatches found during the lookup. A sample scenario is demonstrated below.

In the above scenario, I have used Row Count component to get the row count of the lookup no match output. I used variable called Count to store the row count. In  the script component I increment the ProductErrorCount variable if row count (value of the Count variable) greater than zero. At the end I found that no count had been updated, during the execution of dataflow task. Therefore above method cannot be used to get the row count of a specific path.

The reason for the above problem is SSIS package variables does not update during the execution of data flow task. Those variables get updated once the Data flow task finishes. Therefore we cannot use the updated value of the variable during the execution of a dataflow task.

Due to the above error we cannot change the value of a variable within the Process method of a Script component. If you try to change the value of a variable, you'll get the following error.

Eventhough you could change the variable values within pre-execution or post-execution methods, it only take effect to the value of the variable after the execution of the data flow task.

Then I use the following alternative method to get the row count without using package variables.

I create a grobal variable called Count inside the script component. Then I increment its value within the process method. If script component read hundred records, then Count global variable incremented up to hundred at the end of the process method. Then In the post execution method I incerement the package variable ProductErrorCount, so I can use its value at the Control flow.  

I hope the above method might helpful in your scenarios. But this is only a alternative solution, Its not a correct way to solve the problem. If anybody found any good method please let me know. Your comments are highly appritiated.

Development Environment for Sharepoint & SQL Server

I would like to share some of my experience in preparing a proper devlopment environment, in this article. I had been a great desire to make my development environment to be same as the production environment, for few months. As developers most of you would agree with me, since you also like to work with the production environment itself. It is advantageous to have a your own development environment in your own pc's since it give you the ability to work in a production environment at home and it's more convinient to your testing and deployments.

There are two ways to achieve this on microsoft environment.

1. Intall Windows server as your main operating system and use virtualisation to prepare other server machines and windows7 development environments.
2. Intall Windows7 as your major operating system and prepare a dual booting system for windows server and use virtualization on windows server system to prepare other production servers.

There are several pros and corns in above two methods.

In the first method we choose windows server as our prefered operating system. It would be a really hard decission to you as you'll have to depend on a server for all small tasks such as preparing a word document or listen to music or small excel thing. In the next method we create our machine for dual booting, then we could choose the operating system at booting instance. Then it would be more user friendly as our requirement not always be a Sever OS. Terefore I really like for the second option and I hope to guide you to a nice dual booting system and essintial configurations for a production environment such as how to configure active directory domain services.

Creating a dual booting system

First of all your system have to be patitioned properly. Before creating patitions you should aware that which patitions require more disk space. I personally like to install all the operating systems to C:/ drive hense C drive should requre more space than other patitions. For example if you have 500GB hard disk you should alocate at least 300GB for C drive.

• Install Windows7 or WindowsXp to your C drive without any hesitation. No any aditional thing to undergo here.
• Next you are going to create a vhd file, which is used to install Windows Server 2008 R2. Therefore this is not a virtual PC, its directly install on to your hardware, but the difference is its installed to a file.

Here is a great article which describes how to create a win2k8r2.vhd file.
http://www.microsoftnow.com/2009/11/dual-booting-windows-server-2008-r2.html


I shall note down here some important points that you should follow up and difficulties I have recovered.

• When you creating the .vhd file allocate more disk space to Server system as we need to create other servers on this server. According to the above example hence you allocated 300GB to C drive, give Maximum disk size as 200GB.  
• Once you are created the .vhd file you'll have to reboot the machine for windows installation (I assume that you are intalling windows server using bootable DVD) then mounts are deleted and you have to reattached the .vhd file. Otherwise precreated partition will not be shown in the partition table.
•  Press SHIFT+F10 to get command promt when you get the windows installation screen after the language selection window.
• Sometimes your machines F10 key is allocated for some aditional tasks, In that instant you will not get the command promt even if you press SHIFT+F10. Please make sure that your F10 key is not assigned for any other tasks and use SHIFT+Fn+F10 or use another keyboard.
• Then probably you would see the new patition that we have created in the partition selection table and intall windows server to the newly created partition.

After installing Windows7 and Windows Server to your system you would see boot selection menu at the booting instance.

Creating a Production environment on Windows Server

The main components of a production environment are listed below.

1. Domain Controller
2. Database Server
3. Web Server or Sharepoint Server.

You need at least three servers to be installed on your pc to create real production environment. But I was bit greedy to spend on three servers, so I install Databse server and Sharepoint server on one server. Therefore in my care I use only two servers, but you can use three servers. But keep in mind that you really use this pc for development purposes, then you have to power on above three servers before any development task.

You can use allready installed server as Domain Controller. Which can be done by adding server rolls in windows server 2008 r2. Open the server manager snap in and add the new roll called Active Directory Domain Services. The following technet article shows you how to configure Active directory domain services on Windows server 2008 r2.

http://technet.microsoft.com/en-us/library/ee256063(WS.10).aspx (Use only 5th proceedure)

You can add DNS server roll as a part of adding Active Directory Domain Services server roll. So I have configured "dsp.com" as my Fully Qualified Domain Name (FQDN).

Now you have a Active Directory and a DNS server.

Our next step is adding Hyper-V server roll in to our server. Open server configuration manager and add Hyper-V server roll. Before proceed through the steps, you should aware that you are going to create a Virtual machine. That machine should needed at least 3GB of memory. In my case Iam going to install database server and sharepoint server on this virtual machine. If you use this VM as a databse server, then 2GB is enough, but if you plan to install sharepoint farm more than 3GB is preferable. The following technet article shows how to add hyper-v server role, set up a virtual machine and configure virtual networks.

http://technet.microsoft.com/en-us/library/cc732470(WS.10).aspx

Then add your virtual machine to your domain, in my seinario its dsp.com. (Write click on my computer go properties, Change settings on Computer domain then system properties dialog box will apear. Under computer name tab click on change and change the computer domain to your domain)

Once your VM is added to the domain you can logon to the system as a domain user. Then go to the domain controller again and add new active directory user and you can use that user as a default user when you log on to the VM. Before log on to the system using domain account its better to add the domain user to the Administrators group of the VM.

Generally Iam using my PC as a sharepoint server. But Iam developing SQL server based products as well, so I need to install database server which compatible with sharepoint installation. There are three minimum service accounts should require for a least previlage sharepoint server installation. Those accounts are listed below.

1. sp_sql
2. sp_admin
3. sp_farm

For more details about user accouts and previlages use following technet article.

http://technet.microsoft.com/en-us/library/ee662513.aspx

Then create above user accounts and service accounts in your domain controller. Then Intall SQL server using sp_sql service account.  Then you can Install sharepoint using least previlage service accounts. The following article shows how to install sharepoint using least previlage service accounts.

http://sharepointgeorge.com/2010/installing-sharepoint-2010-privilege-service-accounts/

For prerequisits follow the follwing msdn article.

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

Now you have done your prodoction environment and hope it would work well.