SurendraThotaMSBIProfessional

when we are developing certain measures where there is no data at present but we need to create that measure now . For example  i need to create a Measure which calculate succeesRate of a particular application called Wx . CREATE MEMBER CURRENTCUBE .[Measures].[Wx SR] AS IIf ([Measures].[SuccessRate] > 0,([Measures].[SuccessRate],([Application].[Application].&[Wx])), null ), FORMAT_STRING = "Standard" , VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'DRA Rate Count' ; As there is no data for that application we will get  when we Query that measure we will get the result as   #Error To handle this error in mdx we need use the Function IsError() Syntax: MEMBER [KPI Value] As iif (IsError( KPIValue ("Wx SR")),0, KPIValue ("Wx SR"))

when we run the below mdx query WITH SET LastSample AS TAIL(NONEMPTY([Date].[Hierarchy].members *[Time].[Hierarchy].members,[Measures].[Overall LU SR])) MEMBER [KPI Name] AS "Overall LU" MEMBER [KPI Value] As KPIValue("Overall LU SR") MEMBER [KPI Status] AS KPIStatus("Overall LU SR") MEMBER ObjectName as "Overall LU SR" MEMBER [Critical Threshold] as [KPI Thresholds].[Threshold1].&[Overall LU SR].member_value MEMBER [Major Threshold] AS [KPI Thresholds].[Threshold2].&[Overall LU SR].member_value MEMBER [Minor Threshold] AS 0 MEMBER latestDate as [Date].[Hierarchy].membervalue, format_string = 'mm/dd/YYYY' MEMBER latestTime as [Time].[Hierarchy].member_caption SELECT {latestDate,latestTime,ObjectName,[KPI Name], [KPI Value],[KPI Status],[Measures].[Critical Threshold],[Measures].[Major Threshold],[Measures].[Minor Threshold]} ON COLUMNS ,nonempty(LastSample) ON ROWS FROM [DRA] we got the below error Executing the query ..

we got one requirement where the user wants to the see the report in a format  showm below . The relation is in a hireachy   Oppourtunity ==> Cases==> Order items They want a format shown below so that   under Oppourtunity 1    it contains its corresponding cases   and under each Case it should conatin its Order items . Each level will have  more details with them and they want diffrent format in differnt level on a hole the format repeats for each Oppourtunity . Oppourtunity 1  CaseID 1 Details …… Order item 1 Details … Order Item n Details … CaseID n Details …… Order item 1 Details … Order Item n Details … Oppourtunity n  CaseID 1 Details …… Order item 1 Details … Order Item n Details … CaseID n Details …… Order item 1 Details … Order Item n Details … The Solution : In Reporting Service, we can use a group to organizes a view of a report dataset. All groups in a data region specify different views of the same report dataset. Gro

SSIS Project deployment Model is the new feature in SSIS 2012 . with this feature we got new concept called parameters which is not there in previous versions . i just want want to give you simple difference between parameters and variables . The simple and easy defination between the difference between Parameters and variables are Parameters are read only and Vaibales are Read write , In technical terms we can even say Parameters are like constants and Variable are variables . Parameters are just the values used in process and u cannot write back anything on those . but we can do with variables.  Parameters of 2 types one Project parameter and 2. Package parameter .The differnce between these 2 are Scope . The Project parameter have higher scope that means it can used in any of the packages under that project  where as the Package parameter is very specific to that package . For Example when to Use project parameter is when we  have common destination Sql server in all th

we can Unzip the folder in SSIS using Execute Process task . Here is the Step by Step process 1.Open the SSDT or BIDS and create a new SSIS Project. 2.Drag and drop the Execute Process Task to the design pane. This task executes any process file. In this tip we will execute the 7z.exe file. 3.Double click on it and click the process Tab. Fill the Executable field, the WorkingDirectory and the Arguments The Executable file is the name of the .exe file. In this case the 7z.exe. The WorkingDirectory is the path where the Destination folder. Finally the Arguments are the parameters used. For Unziping here is the the parameters we need pass Excuteable  :  C:\Program Files\7-Zip\7z.exe        Arguments : e "Unzip Folder Name" -o"Destination Folder Name" -y WorkingDirectory :  Destination folder name. the final image looks like  

Here i want to explain how to download files from SFTP location  using SSIS. There are different ways but i want to tell you how we can do it using PSCP . use a free tool named pscp. You can download the tool here: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html First all place pscp.exe in folder C:\Program Files\PuTTY\pscp.exe ( generally if you have Putty installed on your machine that pscp.exe is placed in that folder only ) First check the command using cmd promt navigate to the folder where Pscp.exe exists Then type the below command pscp -sftp -pw <<password>> <<username>>@IP:Sourcepath DestinationPath pscp -sftp -pw Password Username@11.111.11.1:/opt/DSC_6_0_R5/logs/metrics.csv C:\Users\Public\Documents\SurendraData\DRA\PSCPTest\server1A_metrics_SSIS.csv NOTE : if you use the above command you will need to give respond yes or no in the next step . Now Coming to SSIS 1.Open the SSDT or BIDS and create a new SSIS Projec

This error will occur in Execute Process task in SSIS . The main problem with SSIS is it wont show the Exact reason for the Error . It is generic error message for all type of Mistakes. The 3 settings we need to take care in Execute Process task are 1.Executable 2.Arguments 3.Working Directory Generally the errors will occur when you are passing wrong arugements or when there is no permission to the given path of Executable or working Directory to the account with with package is running. i will show you one case where i got this error Case : when i am using the Process execute task in ssis it is failing with the fallowing error [Execute Process Task] Error: In Executing "C:\Program Files\PuTTY\pscp.exe" "echo y | pscp -sftp -pw Password Username@11.111.11.1:/opt/DSC_6_0_R5/logs/metrics.csv C:\Users\Public\Documents\SurendraData\DRA\PSCPTest\server1A_metrics_SSIS.csv" at "C:\Program Files\PuTTY", The process exit code was "1" whil