Error processing OLAP project

If you get this error - "Key not valid for use in specified state"
One probable solution can be to change the a/c under which analysis service is running. I changed from "NT AUTHORITY\LOCAL SERVICE" to "Local Service" a/c and worked for me.

Figure out whether SSIS package was executed from JOB or not

1. Write one SP, like this -
   ALTER PROCEDURE [dbo].[RunDTSPackages]
   @JobID VARCHAR(255)
   AS
   BEGIN
   SET ROWCOUNT 0
   DECLARE @DtexecQuery VARCHAR(8000)
   DECLARE @PackageName VARCHAR(200)
   DECLARE @DatabaseServer VARCHAR(100)
   DECLARE @ID INT
   DECLARE @JobSYSID VARCHAR(255)
   DECLARE @ReturnCode INT
   ----my package name to BLANK initially
   SET @PackageName = ''
   ----my server name
   SET @DatabaseServer = 'F1LT230'
   --Passing the SysJobID
   SET @JobSYSID ='/set \package.variables[SysJobID].Value;"\"'+@JobId+'\""'
   DECLARE @tblSSISMaster TABLE(
   ID INT PRIMARY KEY,
   Name VARCHAR(255),
   PackageName VARCHAR(255)
   )
   INSERT @tblSSISMaster SELECT
   ID,
   Name,
   PackageName
   FROM SSISPackageToRun
   WHERE IsMaster = 1 ORDER BY ID DESC
   SET ROWCOUNT 1
   SELECT @ID = ID FROM @tblSSISMaster
   WHILE @@ROWCOUNT <> 0
   BEGIN
   SELECT @PackageName = LEFT (PackageName, LEN(PackageName)-5) FROM @tblSSISMaster WHERE ID = @ID
   SET @DtexecQuery = 'dtexec /sq ' + @PackageName + ' /ser ' + @DatabaseServer + ' '
   SET @DtexecQuery = @DtexecQuery + @JobSYSID
   --Print @DtexecQuery
   EXEC @ReturnCode = xp_cmdshell @DtexecQuery
   DELETE FROM @tblSSISMaster WHERE ID = @ID
   SELECT @ID = ID FROM @tblSSISMaster
   END
   SET ROWCOUNT 0
   END
2. Create a job and call above SP as under. Also ensure that you are logging the GUID generated from the job. Also, if same package is being executed from other way - using WEB App or directly, pass a hard coded GUID.
   DECLARE @SQL NVARCHAR(72), @jobID UNIQUEIDENTIFIER, @jobName SYSNAME
   SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
   EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
   EXEC RunDTSPackages @jobID
3. Query the log table for the above two unique GUIDs and get the reports generated as in to know whether the package was executed from the Job or Web.
4. Your log table should have following columns -
   ExecutionLogId]
   ,[ParentExecutionLogId]
   ,[Description]
   ,[PackageName]
   ,[PackageGuid]
   ,[MachineName]
   ,[ExecutionGuid]
   ,[LogicalDate]
   ,[Operator]
   ,[StartTime]
   ,[EndTime]
   ,[Status]
   ,[FailureTask]
   ,[tblsysJobID]

Reporting Service 2008: Call .NET function in MDX

Steps -

1. Create an .NET library. Ensure there is a public function that returns a value - String, Int etc.
2. Connect to Analysis Server and right click on Assembly folder.
3. In the Register Server Assembly window, browse and select the assembly (DLL), created in the step-1. Remove if there is any space in in the Assembly name textbox, you find.
4. Under the Permission section, select External access and click on OK button.
5. Notice that the one assemly is added under the Assembly folder.
6. In MDX write code as under -
   MEMBER [Measures].[TECHNOLOGY NAME] AS
   (
   ITGExecDashboard.GetTechnologyNames ([Measures].[APPLICATION ID])
   )
7. Select above measure in the column. Note that GetTechnologyNames .NET function is being executed and application identifier is being passed which is coming from other measure.

SQL Server 2008-Get comma separated column value

Create Table tblApplication (ID int, ApplicationName Varchar(50)) GO
Insert into tblApplication values (1, 'Microsoft')
Insert into tblApplication values (2, 'Apple')
Insert into tblApplication values (1, 'Aditi')
Insert into tblApplication values (1, 'CTS')
Insert into tblApplication values (2, 'Infosys')
Insert into tblApplication values (1, 'Wipro')
Insert into tblApplication values (3, 'Birlasoft')
GO

CREATE FUNCTION dbo.GetApplicationNames
( @AppID INT )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @ApplicationNames VARCHAR(MAX)
SELECT @ApplicationNames = COALESCE(@ApplicationNames + ', ', '') + ApplicationName
FROM tblApplication
WHERE ID = @AppID
RETURN @ApplicationNames
END
GO
SELECT dbo.GetApplicationNames(1)
GO

RS: Does not show axis tab

Sometime when you select a chart, axis properties buttons are not displayed. To fix this,

1. Go to the view code for the .rdl.
2. Search for and put
   
   =Fields!Organization.UniqueName
   
3. Finally code should look like this -
   
   
   
   
   
   
   =Fields!Organization.UniqueName
   
   

How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008

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

Deploying Report

There are various issues in deploying reports on the machine running Windows Vista. To fix try a few things -

1. Go to this folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServe. Open rsreportserver.config file and change SecureConnectionLevel value from 2 to 0.
   Like
2. Go to Report Server Configuration and check following -
   a. Web Service URL link: Change port from default (80) to say 8080.
   b. Database link: Ensure that the ReportServer database is being pointed and credential set to service account.
   c. Report Manager URL link: Check to ensure that 8080 port is being used.
   d. Execution Account: Ensure Specify an execution account is unchecked.

RS - Get last day from time param

Build expression like as under -
="Last Updated: "+ DateAdd("d",-1,dateadd("m",1,DateAdd("d",1-DatePart("d",Parameters!TimeName.Value),Parameters!TimeName.Value)))

Report - Problem freezing table header in the report

If you need to freeze column header in the report so that column header remain intact when you traverse between the pages. This is done by setting some properties for the table, however many a occasion it does not work. So you might have to go to report code behind and carry out something like this –

Right click on the RDL and select view code

Look for following XML tag and set KeepTogether = True

Commonly asked puzzles

1. A light bulb is hanging in a room. Outside of the room there are three switches, of which only one is connected to the lamp. In the starting situation, all switches are 'off' and the bulb is not lit.
   The Question: If it is allowed to check in the room only once to see if the bulb is lit or not (this is not visible from the outside), how can you determine with which of the three switches the light bulb can be switched on?
   
   To find the correct switch (1, 2, or 3), turn switch 1 to 'on' and leave it like that for a few minutes. After that you turn switch 1 back to 'off', and turn switch 2 to 'on'. Now enter the room. If the light bulb is lit, then you know that switch 2 is connected to it. If the bulb is not lit, then it has to be switch 1 or 3. Now touching for short the light bulb, will give you the answer: if the bulb is still hot, then switch 1 was the correct one; if the bulb is cold, then it has to be switch 3.
2. Tom has three boxes with fruits in his barn: one box with apples, one box with pears, and one box with both apples and pears. The boxes have labels that describe the contents, but none of these labels is on the right box.
   The Question: How can Tom, by taking only one piece of fruit from one box, determine what each of the boxes contains?
   Tom takes a piece of fruit from the box with the labels 'Apples and Pears'. If it is an apple, then the label 'Apples' belong to this box. The box that said 'Apples', then of course shouldn't be labeled 'Apples and Pears', because that would mean that the box with 'Pears' would have been labeled correctly, and this is contradictory to the fact that none of the labels was correct. On the box with the label 'Appels' should be the label 'Pears'. If Tom would have taken a pear, the reasoning would have been in a similar way.
3. You are standing next to a well, and you have two jugs. One jug has a content of 3 liters and the other one has a content of 5 liters.
   The Question: How can you get just 4 liters of water using only these two jugs?
   Solution 1:
   Fill the 5 liter jug. Then fill the 3 liter jug to the top with water from the 5 liter jug. Now you have 2 liters of water in the 5 liter jug. Dump out the 3 liter jug and pour what's in the 5 liter jug into the 3 liter jug. Then refill the 5 liter jug, and fill up the 3 liter jug to the top. Since there were already 2 liters of water in the 3 liter jug, 1 liter is removed from the 5 liter jug, leaving 4 liters of water in the 5 liter jug.
   Solution 2:
   Fill the 3 liter jug and pour it into the 5 liter jug. Then refill the 3 liter jug and fill up the 5 liter jug to the top. Since there were already 3 liters of water in the 5 liter jug, 2 liters of water are removed from the 3 liter jug, leaving 1 liter of water in the 3 liter jug. Then dump out the 5 liter jug and pour what's in the 3 liter jug into the 5 liter jug. Refill the 3 liter jug and pour it into the 5 liter jug. Now you have 4 liters of water in the 5 liter jug.
4. Charles walks over a railway-bridge. At the moment that he is just ten meters away from the middle of the bridge, he hears a train coming from behind. At that moment, the train, which travels at a speed of 90 km/h, is exactly as far away from the bridge as the bridge measures in length. Without hesitation, Charles rushes straight towards the train to get off the bridge. In this way, he misses the train by just four meters! If Charles would, however, have rushed exactly as fast in the other direction, the train would have hit him eight meters before the end of the bridge.
   The Question: What is the length of the railway-bridge?
   Let the length of the bridge be x meters.
   Running towards the train, Charles covers 0.5x-10 meters in the time that the train travels x-4 meters. Running away from the train, Charles covers 0.5x+2 meters in the time that the train travels 2x-8 meters.
   Because their speeds are constant, the following holds:
   (0.5x-10) / (x-4) = (0.5x+2) / (2x-8)
   which can be rewritten to
   0.5x2 - 24x + 88 = 0 Using the abc formula we find that x=44, so the railway-bridge has a length of 44 meters.
5. There is a water-cask with three different water-taps. With the smallest tap the water-cask can be filled in 20 minutes. With middle the tap the water-cask can be filled in 12 minutes. With the largest tap the water-cask can be filled in 5 minutes. The Question: How long does it take to fill the water-cask with the three taps together?
   The smallest tap fills 1/20 water-cask in 1 minute. The middle tap fills 1/12 water-cask in 1 minute. The largest tap fills 1/5 water-cask in 1 minute. Together they fill 1/20 + 1/12 + 1/5 = 1/3 water-cask in 1 minute. Therefore, the whole water-cask is filled in 3 minutes.

Business Intelligence Interview Questions

1. You have two tables Table1 and Table2 and value saved in the corresponding column of the tables are as under -
   Table1
   Column1
   1
   2
   3
   None
   Table2
   2
   3
   None
   None
   > How many none you will get with left outer join?
   > How many none you will get with full outer join?


2. Difference between delete and Truncate? Which one is faster and why?


3. You have a table with column name Gender that holds only two value - Male or Female. What will you do to defect if some wrong value is entered?


4. RS: How to create cascading parameters?


5. RS: What all are different caching available in Reporting services?


6. RS: What will happen to bar graph, if we export report to csv format? How to fix it?


7. RS: What are drill through actions?


8. AS: How to implement partitions?


9. What are KPI? Why a KPI in SSAS?


10. What is zero error?


11. AS: What is diff between ROLAP and MOLAP?


12. IS: Moslty used containers in SSIS?


13. IS: Look up and its caching features?


14. RS: How to implement security features in Reporting service?


15. RS: What is PeriodToState function?


16. Commonly used MDX functions?


17. RS: Difference between Table and Matrix in SSRS?


18. RS: What all types of reports you have created?


19. AS: In Dimension Usage TAB, What all are relationships available?


20. AS: What is diff between many to many and reference?