How to show all category labels on the Column Chart SSRS 2012

How to show all category labels on the Column Chart X-Axis  SSRS 2012:

1. Right-click the category axis and click Axis Properties. The Axis Properties dialog box opens.
2. In Axis Options, set Interval to 1. Every category group label is displayed. If you want to show every other category group label on the x-axis, type 2.
3. Click OK.

How to have a parameter value automatically selected when another parameter value Selected in MS SSRS.

Step 1: Create cascade parameter in SSRS.
Step 2: One Parameter value is depend on another parameter value.
Step 3: The Depending parameter data set always return One Value.
Step 4:In Parameter Select-->Default Value Tab-->Get value from a Query-->Select Dependent data set--Select Value.

Example:
Dataset1:

select distinct

snapshot_WK

,cast(snapshot_Wkstart as date) as Snapshot_WKstart

,cast(snapshot_Wkend as date) as Snapshot_WKend

from sandbox..[Rawdata_Master](nolock)

order by snapshot_Wk

Dataset2:

select top 1

cast(snapshot_Wkstart as date) as Snapshot_WKstart

,cast(snapshot_Wkend as date) as Snapshot_WKend

from sandbox..[Rawdata_Master](nolock)

where snapshot_WK = @Reporting_Wk

Dataset2 is depend on Dataset1.Both data set are using for Parameter in Report. 

Result:When we select Snapshot_Wk in one parameter,other two parameter automatically filled with corresponding Wk_Start data and Wk_End Date.

Hexadecimal value, is an invalid character: SSRS 2012

Hi,Today i have encounter below issue while deploying my report on SSRS 2012 Report manager.
Report is working fine in MS Visual studio during development,but when i had deployed the same report on report manager i was getting below Error message:

Error :The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
There is an error in XML document (1, 12827).
' ', hexadecimal value 0x1A, is an invalid character. Line 1, position 12827.

Issue Root cause:
      "This problem occurs because a non printable character is populated into a report parameter that you define in the report".

Solution:
Step1: Check the ASCII number for non printable character  using below statement.

select ASCII(' ')

Note:Might be it will not display on  SSMS,but when you execute select statement you will get  number. in my case it is 26.
Step2: Just use REPLACE() function in your query to eliminate non printable character.

select distinct REPLACE(title, CHAR(26),',') as title from analytics..<<Table_Name>>

order by title desc

 Tag:(SSRS, xml, non printable character, Ascii, Char, Replace)
  

MSSQL Table BackUp

MSSQL Server:

1-Table back up With table schema and Data:

Method 1:

select  * into [dbo].[USER_backup] 

from [dbo].[USER]

Method 2: 

2-Table back up only Schema:

SELECT TOP 0 *

INTO [dbo].[USER_Schema_backup]

FROM [dbo].[USER]

3-Data base Back Up:


4-Data base Differential Back Up: 

Export Package from "Integration Services Catalog" MS SQL Server 2012

Steps to Export Package From Integration Services Catalog(SQL Server 2012):
1.Connect to server
2.Drill down "Integration Services Catalog" folder.
3.Drill down till projects folder.
4.Right Click on Package-->Export
5.Save file on your local folder with extension ".ispac"
6.Now,change your saved file extension to ".zip"
7.Extract the Zip file you will get ".dtsx" package file.

Errors in ODI(Oracle Data Integrator)

Error Description:

ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_EXCEL connection Incident_data.
Caused By: java.sql.SQLException: Invalid Fetch Size

Solution:You will get above error  after executing "Interface" in "Operation" Tab.

step to Fixed this issue:--Go to Topology-->Physical Architecture-->Technology-->Microsoft Excel-->Select Your data server-->Check Array Fetch Size Column   .By default the value in this column is 30.

Just remove the value from this column  

How To Create Master repository in ODI 11 G(Oracle Data Integrator)

How To Create Master and Work Repository:

Step 1: Prerequisites for working with ODI:

·         ODI should be installed, for configuring ODI we need Master repository and Work repositories. These repositories can be created in two ways:

1)      First way is to create the repositories in the DB(Data Base) and then use it in the configuration, the steps for this is given in Step 2 and the

2)      Second method is to do it through RCU (Repository Creation Utility), steps for this is given in Step 3.

For Complete step 2(Create repository in the DB) we have to create user in the DB. The user can be created in Oracle  (this user can be created in any RDBMS).

Find Scripts to create users in the DB.

--------------------------------------------------------

 SCRIPT FOR CREATING ODI User

--------------------------------------------------------

CREATE USER SINGH_DEV_ODI_REPO IDENTIFIED BY DEV_ODI_REPO;

GRANT CONNECT, RESOURCE TO SINGH_DEV_ODI_REPO ;

Note: Here SINGH_DEV_ODI_REPO is a Username and

Password: DEV_ODI_REPO. We will create this username in the data base and then use this user for Creating Master Repository.

·         Execute the script in any user with SYSDBA privilege.

Important: We can create any number of Master repositories and Work Repositories

Step2:  How to Create Master Repository and Work Repository :

Open ODI Studio: StartàAll ProgramàOracleàOracle data integratoràODI studio

ODI(Oracle Data Integrator)Start Page will open

Click on File->New and below window will be opened.

Select Master Repository Creation Wizard ---then Click :Window will openàMaster Repository Creation Wizard step1 of 3:

Technology:Oracle (Select any RDBMS).

JDBC Driver:Select respective Driver that supports selected technology.

JDBC URL:enter the specific information of the system on which oracle is available.                                                      

User:<Master Repository User name> ex: SINGH_DEV_ODI_REPO

Password:<Master Repository User’s Password>Ex: DEV_ODI_REPO

DBA User:<DBA User name> Ex: User name which you user during oracle installation

DBA Password:<DBA User’s Password> Ex: password which you user during oracle installation

Test the connection, Enter the ID for master repository and click Next, this creates Master repository related tables in the Oracle user that we have used for Master repository for the first time

Oracle 11g XE Installation Steps:

Oracle 11G XE(Express Edition) Installation Steps:

Step 1:

It’s recommended that you right click on the setup.msi program and run it as the Administrator. If you’ve not disabled Microsoft User Access Controls (UAC), I’d do that first.

Step 2:

 After launching the setup.msi file, you see the Install Shield wizard screen while the file loads.

Step 3:

After launching the setup.msi file, and loading the MSI file, you see the first dialog box of the installation. Click the Next button to start the installation.

Step 4:License Agreement

The second dialog box is the license for the installation. Click the I accept the terms in the license agreement. radio button and then the Next button.

Step 5:Choose Destination Location

The third dialog box lets you accept the default location or provide an override location. If you accept the default location, click the Next button to continue.

Step 6: Specify Database Ports

  The fourth dialog box lets you specify the TNS, MTS, and HTTP Ports. The default values are shown in the following screen shot but I’ve change my TNS port to 1621, Click the Next button to continue.

You can add below default values in the below text box:

TNS Port: 1521

MTS Port :2030

HTTP Port:8080

Step 7:Specify Database Passwords 

 The fifth dialog box lets you enter the password for the SYS and SYSTEM users. You must enter it twice and naturally they must match each other. Click the Next button to continue.

Step 8: Summary 

 The sixth dialog box lets you see the configuration options you’ve chosen. Note that this installation is using a TNS port of 1621 rather than the default of 1521. Click the Next button to continue.

Step 9: Set up Status 

The seventh dialog box lets you see the progress bar, take a break it can run for two or three minutes. Click the Next button to continue.

Step 10: Install Shield Wizard Complete

 The last dialog box lets you see that you’ve installed the product. Click the Finish button to complete the installation.

Oracle SQL Developer Installation

Installation Steps for SQL Developer:


We need not install this, just copy and paste it to specific drive.                                                          
1.Open the SQL Developer folder and click on file sqldeveloper.exe.
2.When SQL developer for the first time it asks the path of JAVA.exe file.
3.If Java is already installed then browse it, else install java.
After installation, start SQL developer.

ODI Installation Issue :

You Can Install any number of ODI version in your System:
Example you can install below two version in your machine.
ODI 11G Version(11.1.1.5)   and
ODI 11G Version(11.1.1.6)

The other ODI version will come under   Start-->All Program-->Oracle-->Oracle Data Integrator(2)-->ODI

Issue 1:
I'm running Windows 7 64-bit and have a 64-bit installation of JDK 1.6 installed. I installed the generic version of ODI Studio 11g (11.1.1.5) (the 32-bit version will not install on Windows 64-bit).

After the installation and launching ODI Studio, I received the following error:

Unable to launch the Java Virtual Machine Located at path:
c:\Progra~1\Java\jdk1.6.0_26\jre\bin\server\jvm.dll

Solution -1:

1. Download and install the 32-bit version of JDK 1.6 (not the 64-bit version!):

http://download.oracle.com/otn-pub/java/jdk/6u31-b05/jdk-6u31-windows-i586.exe

2. Edit the odi.conf file which is located here:

C:\Oracle\[your_install_directory]\oracledi\client\odi\bin\odi.conf

3. Locate and make the following change:

OLD (which is pointing to the 64-bit JDK):
SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_26

NEW (change it to point to the 32-bit JDK):
SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_31

4. Launce ODI Studio 11g again 

Solution 2:

1.Install jdk1.7.0 in C:\Java\jdk1.7.0   drive then change the below path                                    C:\Oracle\[your_install_directory]\oracledi\client\odi\bin\odi.conf           Change in SetJavaHome c:/Java/jdk1.7.0

ODI(Oracle Data Integrator)11G Installation Steps

ODI(Oracle Data integrator) 11G Installation for 64 bit:

Step1:
Install the Java in C: \Java  in your local machine.You can Download jdk-7u17-windows-x64 files from internet or from Oracle Site.
Please verify ,after installation java, a folder will create in C:\Program File\Java\jdk1.7.0_17 path. 

Step3:
Go to -->Disk1\install\win64 and click on setup, it will ask for a JDK location.After click on win64  a cmd(command prompt) window will open.

Step3: 
Provide  the path up to jdk1.7.0_17 folder and press enter Example:C:\Program Files\Java\jdk1.7.0_17)

Step4: After Completing Step 4 it will open up the "Welcome" Screen of Oracle Data Integrator. CLICK --Next

Step 5:Screen Name: Install Software Update                                                                                Select Option "Skip software Updates" button

Step6: Screen Name: Select Installation Type                                                                            Select Option "Developer installation" and "Standalone Installation" CLICK--Next

Step7: Screen Name:Prerequisite Checks     CLICK--Next

Step:8: Screen Name:Specify Installation Location                                                                            Provide Installation Location    CLICK--Next

Step9: Screen Name :Repository Configuration                                                                                  select option  "Skip Repository Configuration"     CLICK -->Next

Step10: Screen Name :Specify Agent Details                                                                                           Agent Name: "PowerAgent"                                                                                                                Agent Port:"7002"                         CLICK --Next

Step11: Screen Name:"Specify Security Updates"                                                                                 Un-check "I wish to receive security updates via my oracle support"   CLICK--Next

Step12: Screen Name:"Installation Summary"     CLICK-->Install

Step13: Screen Name:"Installation Progress"  CLICK--Next

Step14: Screen Name:"Configuration Progress" CLICK--Next

Step15: Screen Name:"Configuration Progress"  Uncheck "ODI Configuration" CLICK--Next

Step 14: Screen Name:"Installation Completed" CLICK--Finish

Now we can see the installation was successful.

Final Step:GO to Start--->All Programs----->Oracle----->Oracle Data Integrator ----->ODI Studio.

Above step will opens the starting page Oracle Data Integrator.

Oracle Warehouse Installation Basics

Oracle Warehouse Installation Basics Information:

If  you want to work on all Oracle BI Component then you must have install below software in your Local Machine.
1.ODI(Oracle Data Integrator) 11G.
2.SQL Developer.

3.Oracle 11G(if required). Oracle Data base is very heavy and not advise  to install in your machine(It Required 6 to 8 GB RAM)Always install  oracle Database on separate server,and then from your machine you can connect to oracle server.

4.Oracle 11G XE(Express Edition): ( If you feel Oracle 11G Data base is slow your system then you  can install Oracle 11g XE (express edition with limited functionality that will be fine for practice/Training Purpose). This occupies less space and it will not use more system resources.

5.OBI:Not recommended to install OBI on local system  as it occupies more space.Install OBI in Separate server

ODI(Oracle Data Integrator)

Introduction to Oracle Data Integrator:
 

1.ODI(Oracle Data Integrator) is a Widely used data integration software product.
2.ODI Provides a new design approach to Defining data transformation and integration process and provide results with faster and simpler development and maintenance.
3.ODI is based on unique E-LT architecture (Extract - Load Transform),
4.It Provides high performance for execution of data transformation and validation process and it is very cost effective.

Why to choose ODI:
•Faster and simpler development and maintenance: The declarative rules driven approach to data integration greatly reduces the learning curve of the product and increases developer productivity while facilitating ongoing maintenance.
 
•Data quality firewall: Oracle Data Integrator ensures that faulty data is automatically detected and  recycled before insertion in the target application. This is performed without the need for programming, following the data integrity rules and constraints defined both on the target application  and in Oracle Data Integrator.

•Better execution performance: traditional data integration software (ETL) is based on proprietary  engines that perform data transformations row by row, thus limiting    performance. By implementing  an E-LT architecture, based on your existing RDBMS engines and SQL, you are capable of executing data  transformations on the target server at a set-based level, giving you much higher performance.

Simpler and more efficient architecture: The E-LT architecture removes the need for an ETL Server sitting between the sources and the target server. It utilizes the source and target servers to perform complex transformations, most of which happen in batch mode when the server is not busy processing end-user queries.
•Platform Independence: Oracle Data Integrator supports all platforms, hardware and OSs with the same software.

Data Connectivity: Oracle Data Integrator supports all RDBMSs including all leading Data warehousing platforms such as Oracle, Exadata, Teradata, IBM DB2, Netezza, Sybase IQ and numerous other technologies such as flat files, ERPs, LDAP, XML.

• Cost-savings: the elimination of the ETL Server and ETL engine reduces both the initial hardware and software acquisition and maintenance costs. The reduced learning curve and increased developer productivity significantly reduce the overall labor costs of the project, as well as the cost of ongoing enhancements.