MSBI (SSIS/SSRS/SSAS) Online Training

Friday, August 1, 2014

Complete BI Migration from SQL server 2008 to 2012

The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.There are two approaches when u

The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.
An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.




There are two approaches when upgrading:

1.       In-Place: The SQL Server is upgraded where it is currently installed
2.       Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.
The approach I am going to take is the second, since I have a new hardware and I am going to do a fresh SQL Server 2012 installation.

The migration from SQL server 2008 to 2012 must be well prepared and tested before going to production.

I have to migrate several databases, the integration services (SSIS) packages, analisys services (SSAS) packages and reporting services (SSRS) reports.
Requirements
This article assumes you already know the SQL Server database engine, integration services, analisys services, reporting services and it's tools.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.
Databases
The database engine isn't a complete rewrite. This means that we can expect a deep compability level.

There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read ithere to ensure that you ate not using a feature that breaks the SQL 2012 compatibility.
After reading the article and fixing any issues, the upgrade of the databases can be implemented in the following steps: 
1.       Create the databases and configure them on the SQL 2012 Server. If you don't have any special requirements skip this step, since when you perform the restore the databases are created automatically.
Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
2.       Backup the SQL Server 2008 databases and restored them in SQL 2012.
3.       Change each database compatibility level from 2008 to 2012. This action is important since it allows the usage of the new SQL Server 2012 features.
The following script can be useful you have several databases to migrate:

USE [master]
GO
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110

where [mydatabase] is the database to change the compatibility level

or goto the database properties and on the options select the Compatibility Level 110.

4.       Check the logical and physical integrity of all the objects in the upgraded databases:

DBCC CHECKDB([myDatabase]) WITH NO_INFOMSGS
where [mydatabase] is the database to  run the integrity checks
NO_INFOMSGS option  suppresses all informational messages.

If If DBCC printed any error messages you must fix them so that your database will work correctly.
Don't forget to create the databases maintenance plans.
Integration Services (SSIS)
In SQL Server 2012 the SSIS Package format changed and the specifications are now Open Source.
The Business Intelligence Development Studio (BIDs) is replaced by the SQL Server Data Tools (SSDT).
SQL Server 2012 SSIS offers a wizard for upgrading most of the solution components, but a few settings may be needed to be changed manually.
The wizard appears when you open a SQL Server 2008 package on the SQL Server data tools.
Microsoft has a white Paper that gives you 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012. You can read ithere.

SSIS 2012 supports two deployment models:
1.       Package deployment model: In this model the the unit of deployment is the package. This is the model used in previous versions of SSIS and is the default deployment model for upgraded packages.
2.       Project deployment model: . The unit of deployment is the project for this model. This model is new in SQL Server 2012 and provides additional package deployment and management features such as parameters and the Integration Services catalog
I have decided to use the Package deployment model for now, since it is the one the gives more compatibility with the SSIS 2008 model. When I have more time for testing and development I am going to convert to the Project deployment model. There is an wizard the performs this task and that is explained in the white paper I mentioned previously.

The migration of the integration services (SSIS) packages
1.       Open the solution (sln) file with the packages to migrate
2.       The Visual Studio Conversion wizard appears. It is very simple and after a few next's pressed, the Package Management options appear.
3.       In the Package Management options select validate upgraded packages, so that the packages are validated and only the ones that pass validation are saved.
4.       Disable Ignore configurations, so that the configurations are validated during the upgrade process.
5.       The wizard ends the conversion and you can close it.
6.       Test each package and verify that it is working as expected.

If there is a conversion error by the wizard, when you open the package in Visual Studio  it is immediately converted. This methodology allows that you can easly control the errors and correct them.
Analisys Services (SSAS)
The SSAS 2012 has a great deal of changes. The main new features are:

·         Business Intelligence Semantic Model
·         Tabular model
·         PowerPivot for Excel and Sharepoint
·         SQL Server Data Tools (SSDT)
·         Programmability with support for the new features

The options for migrating are:
1.       Use only the multidimensional model 
2.       Convert to the tabular model
3.       Use the multidimensional model for existing cubes and use the tabular model for new developments
4.       Use the tabular model or the multidimensional model depending on the project requirements

The conversion from the multidimensional model to the other models isn't supported by Microsoft at  the date this article was written.
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:
1.       The existing cubes can be migrated to the SSAS 2012 multidimensional model, without any modifications.
2.       The existing reports and client tools will work without any problems
3.       The model is more mature and supports much higher data volumes 
4.       The team has knowledge of this model and can continue the development without any significant changes

In the future I pretend to explore the new models, but for now the mature multidimensional model is the best option.

The analysis services migration, with the selected approach, can be performed in the following simple steps : 

1.       Open the solution (sln) file with the SSAS databases to migrate
2.       The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish.
3.       Terminated the wizard and let it execute
4.       Deploy and process the SSAS database 
5.       Test the SSAS database and confirm that everything is working as expected
The only issued I faced was that after processing I got the error:

- Errors in the back-end database access module. The provider 'SQLNCLI10.1' is not registered.
- The following system error occurred:  Class not registered

This error hints that there is a problem with the Data Sources connection string.
When I tried to open a data source the in project and pressed the edit button to edit the connection string, I got the error:

"The specified provider is not supported. Please choose different provider in connection manager."

The SQL Server 2008 Native Client is not installed in the Sql Server 2012 server, so I changed the connection string provider to the native client 11.0 and the issue was fixed.

Another option, if strictly necessary, is to Download and install the SQL Server 2008 SQL Native Client or the SQL Server 2005 SQL Native Client, depending on the connection string provider you want to use.

After his issue was fixed the processing occurred correctly and smoothly.
Reporting services (SSRS)

The SQL Server 2012 Reporting Services (SSRS) has two processing modes:
1) SSRS 2012 report processor. A report that is successfully converted to SSRS 2012 format is executed in this mode and can use the new SSRS features.
2)  Backward-compatibility mode processor. A report that cannot be converted to SSRS 2012 is processed in backward-compatibility mode and the new features are not available, but the report is still rendered.
You can find more information here.

This approach by Microsoft gives a high degree of compatibility and I don't expect to have any issues in the migration.
The reporting services migration steps are:
1.       Open the solution (sln) file with the reports to migrate
2.       The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish. 
3.       An information message may appear asking if you want to upgrade the report server project to the latest version. Press Yes
4.       Let the wizard execute.
5.       Open each data source and test the connection string. If there is an error fix it.
6.       Deploy the reports
7.       Test the reports and confirm that everything is working as expected


Wednesday, February 19, 2014

*** MSBI Online Training Program DEMO Updates (Start from 19-FEB-2014) ***

Hi All,

I would like to start new batch from 19-FEB-14 on wards, please join the demo session on 19-FEB-14 at 10:00 PM IST. 
Please send me request on my skype id:- nv.ravindranathreddy 
 and be ready online at 10:00 PM IST



Training Details:
About myself- I  have trained 200+ candidates and 90% successfully got the jobs on MSBI platform. I do have 10+ yrs MSBI experience at Architect level and right now I am in Switzerland and use to take online session.
I will teach from Basics to advanced level with real-time examples.

Training Updates:
ü  Session start time is: 10:00 PM -11:00 PM IST
ü  Duration: 40 days
ü  Fee: 15,000 INR
ü  I will share all code (regular sessions), step by step documents and interview questions +  end-to-end project implementation guidance.
ü  If you are interested send me request on my skype id:- nv.ravindranathreddy 
ü  Please install teamviewer from google. If you do not have SQL Server 2008R2 software, let me know then  I can share the software thru online link then you can download it.


Training highlights:
ü   More than 2000 interview questions with the explanation
ü   2GB of MSBI data free (with IMP documents, videos etc...)
ü   Trained more than 200 + aspirants on MSBI.
ü   We are providing the best training on MSBI and consulting for MSBI jobs as well in all major cities.
ü   For sure before you finish training itself you will get a job on MSBI.
ü   We will provide real-time projects with module by module explanation ( free of cost). 

Thanks!
Best Regards,
Ravindra
PRINCE2®, ITIL®, MCITP

Friday, January 10, 2014

MSBI (SSIS,SSRS,SSAS) Online Training with 100% Placement Guarantee WITH REALTIME PROJECTS

Hi All,
I am happy to announce we have trained 200+ students ( only 5 aspirants per batch) with 90% successful jobs throughout global.
I wish you a successful career for all my students; good luck !!



Well as we committed for day one onwards we work with aspirants very closely to make them understand MSBI concepts in detail with real-time projects as well as real-time scenarios.
Our aim is not only to help to get job but also able to stand in BI projects with successful end-to-end implementation.

Please visit below page for more details about MSBI training. Don’t miss this opportunity.

!! Right place for Right career!!


Best Regards,

Ravindra

MSBI & SQL SERVER – 2012 Certification Details

Hi MSBI Guys,

I am providing online training for below certifications and will take care of registration at  Prometric center,, certification will help you a lot in your career.
It is actually very good to have certification in terms of to get good package and shortlist the resume early from the race.
Please let me know if you are interested.


All the best!
Regards,

Ravindra





SSIS AND SSRS Latest Interview Questions &Answers

Q1 Explain architecture of SSIS?
SSIS architecture consists of four key parts:
a) Integration Services service: monitors running Integration Services packages and manages the storage of packages.
b) Integration Services object model: includes managed API for accessing Integration Services tools, command-line utilities, and custom applications.
c) Integration Services runtime and run-time executables: it saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions. The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes, and custom tasks.
d) Data flow engine: provides the in-memory buffers that move data from source to destination.


Q2 How would you do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.

Q3 How would you do Error Handling?
A SSIS package could mainly have two types of errors
a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

Q4 How to pass property value at Run time? How do you implement Package Configuration?
A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

Q5 How would you deploy a SSIS Package on production?
 A) Through Manifest
1. Create deployment utility by setting its propery as true .
2. It will be created in the bin folder of the solution as soon as package is build.
3. Copy all the files in the utility and use manifest file to deply it on the Prod.
B) Using DtsExec.exe utility
C)Import Package directly in MSDB from SSMS by logging in Integration Services.

Q6 Difference between DTS and SSIS?
Every thing except both are product of Microsoft :-).

Q7 What are new features in SSIS 2008?
explained in other post
http://sqlserversolutions.blogspot.com/2009/01/new-improvementfeatures-in-ssis-2008.html

Q8 How would you pass a variable value to Child Package?
too big to fit here so had a write other post
http://sqlserversolutions.blogspot.com/2009/02/passing-variable-to-child-package-from.html


Q9 What is Execution Tree?
Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.

Q10 What are the points to keep in mind for performance improvement of the package?
http://technet.microsoft.com/en-us/library/cc966529.aspx

Q11 You may get a question stating a scenario and then asking you how would you create a package for that e.g. How would you configure a data flow task so that it can transfer data to different table based on the city name in a source table column?


Q13 Difference between Unionall and Merge Join?
a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs

b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.

Q14 May get question regarding what X transformation do?Lookup, fuzzy lookup, fuzzy grouping transformation are my favorites.
For you.

Q15 How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.

Q16 Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

Q17 How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent

Q18 Difference between asynchronous and synchronous transformations? 
Asynchronous transformation have different Input and Output buffers and it is up to the component designer in an Async component to provide a column structure to the output buffer and hook up the data from the input.

Q19 How to achieve parallelism in SSIS?
Parallelism is achieved using MaxConcurrentExecutable property of the package. Its default is -1 and is calculated as number of processors + 2.

-More questions added-Sept 2011
Q20 How do you do incremental load?
Fastest way to do incremental load is by using Timestamp column in source table and then storing last ETL timestamp, In ETL process pick all the rows having Timestamp greater than the stored Timestamp so as to pick only new and updated records

Q21 How to handle Late Arriving Dimension or Early Arriving Facts.
 
Late arriving dimensions sometime get unavoidable 'coz delay or error in Dimension ETL or may be due to logic of ETL. To handle Late Arriving facts, we can create dummy Dimension with natural/business key and keep rest of the attributes as null or default.  And as soon as Actual dimension arrives, the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions.

Q1. WHAT is SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection

Q2.
Architecture of SSRS:

 -Admin 

Q3. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery

Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

Q5. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.


Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.

Q7. Important terms used in the reporting services? 

1. Report definition:  The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.

2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.

3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

4. Parameterized report: A published report that accepts input values through parameters.

5. Shared data source: A predefined, standalone item that contains data source connection information.

6. Shared schedule: A predefined, standalone item that contains schedule information.

7. Report-specific data source: Data source information that is defined within a report definition.

8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

9. Linked report: A report that derives its definition through a link to another report.

10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.

11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.


12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.

13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.

14. Report Builder: Report authoring tool used to create ad hoc reports.

15. Report Designer: Report creation tool included with Reporting Services.

16. Model Designer: Report model creation tool used to build models for ad hoc reporting.

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

Q8. what are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.
  
Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months
 SELECT * FROM ReportServer.dbo.ExecutionLog

-Development
Q. What is difference between Tablular and Matrix report?
OR What are the different styles of reports?

Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
    a. One group of data is displayed across the page.
    b. One group of data is displayed down the page.
    c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
    d. One group of data is displayed as the "filler" of the cells.
Martix reports can be considered more of a  Pivot table.

Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.

Q. How to create Drill-Down reports?
To cut the story short: 
- By grouping data on required fields
-Then toggle visibility based on the grouped filed




SSIS - Uses with For Loop Container

How to configure a simple For Loop Container? The For Loop Container is a repeating container that does something untill a condition evaluates true.

Four examples:
- Do something ten times
- Wait untill a certain time
- Wait for a couple of seconds / minutes


A) Do something ten times
This example does ten times what's within the loop.

1) Variable
Create a integer variable named Counter. This variable is used the count till 10.
 

Variable Counter (right click in Control Flow)








2) Add For Loop Container
Drag a For Loop Container to your Control Flow and give it a suitable name.

 

















3) Edit For Loop Container
·                     Edit the For Loop Container and set the InetExpression to: "@[User::Counter] = 0". This is the initial value of the counter. This example starts at zero.
·                     Set the required EvalExpression to: "@[User::Counter] < 10". This is the evaluation expression that contains the expression used to test whether the loop should stop or continue. The example stops after 10 times (0, 1, 2, 3, 4, 5, 6, 7, 8, 9).
·                     Set the requires AssignExpression to: "@[User::Counter] = @[User::Counter] + 1". This is an optional iteration expression that increments the loop counter.

Set the expressions



4) Testing
For testing purposes I added a Script Task with a MessageBox that shows de value of the counter.
 

Testing the For Loop





B) Wait untill a certain time
This example uses the For Loop as a Wait statement. Let's wait untill 12:20.

1) For Loop Container
Add a For Loop Container to your Control Flow and give it a suitable name. I added a Annotation to clarify that it does nothing within the loop.

Waiting For Loop














2) Edit For Loop Container
Only enter this EvalExpression: GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

EvalExpression



















Clarification of the expression
It removes the time from the current datetime. So 01-02-2011 12:01:45.002 becomes 01-02-2011 0:00:00.000:
(DT_DBDATE)GETDATE()
After that it adds 20 minutes and 12 hours. So it becomes 01-02-2011 12:20:00.000:
DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))
That datetime is compared to the current datetime:
GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

3) Testing
For testing purposes I added a Script Task after the loop that shows the current time in a messagebox.

Testing














C)  Wait for a couple of seconds / minutes
With a slightly different approach then example B, you can wait for a couple of seconds, minutes, etc.
Use this expression 
in the same way as example B to wait 30 seconds:
DATEADD("ss", 30, @[System::ContainerStartTime]) > GETDATE()
or 5 minutes:
DATEADD("mi", 5, @[System::ContainerStartTime]) > GETDATE()

Click 
here for more information about DATEADD.

Note: Delay techniques with a For Loop causes unnecessary CPU processing. A Script Task with a 
Thread.Sleepdoesn't have that drawback.