RAVINDRANATHREDDY - MSBI - BLOG

How to generate SSRS Report and How to deploy in MOSS

2011-05-11T16:58:00.000-07:00

Hi,
Here you can learn how to deploy/integrate a SSRS report with MOSS application.

Assumptions:
Report Server has been configured for SharePoint Integration Mode.
SharePoint Report Server Integration Feature is activated.
Appropriate Content Types (Report Builder, Report Builder Report, Report Data Source) have been added to a Document Library within a site.

For further information on these configuration topics, see Deploying SSRS with SharePoint Integration.

Assume the following server/site/library/folder names:
• Server Name = MyServer
• SP Site = SiteABCReports
• Doc Library = SSRS Reports
• Data Sources Folder (optional) = Data Sources
• Report Models Folder (optional) = Models
• Reports Folder (optional) = Reports

Deploy Report Designer Reports

From within a SQL Server Business Intelligence Studio (BIDS) Report Server Project, go to the project’s Property Page (right-click project in Solution Explorer and select Properties).
Apply the following deployment settings:
• Overwrite ExistingDataSources = True
• TargetDataSourceFolder = http://MyServer/SSRS%20Reports/[Data%20Sources]
• TargetReportFolder = http://MyServer/SSRS%20Reports/[Reports]
• TargetServerURL =http://MyServer

Where [‘xxx’] denotes optional.

Things to note:

• A TargetReportFolder must be specified. The folder can be the document library or a folder within the document library.
• The TargetDataSourceFolder is optional. If one is not specified, the Data Source will be deployed to the TargetReportFolder.
• For both target properties, if the folder within the document library does not already exist, it will be created upon deployment.
• Relative paths are not valid.
• The replacement of the space character with %20

See the below article for step-by-step approach to deploy the report on MOSS.
http://mosshowto.blogspot.com/2009/02/reporting-services-2008-report.html

Here you can download some of the sample SSRS reports,it’s really nice work.
http://blogs.msdn.com/b/chrisfie/archive/2008/04/10/new-sql-reporting-services-sample-reports-for-project-server.aspx

Linked server in sql server 2008

2011-05-11T16:38:00.002-07:00

Hi,
When you are setting up a linked server, register the connection information and data source information with SQL Server. After registered, that data source can be referred to with a single logical name.
You can use stored procedures and catalog views to manage linked server definitions:
• Create a linked server definition by running sp_addlinkedserver.
• View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views.
• Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server.
You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.
When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. For more information, see Distributed Queries.

Step-by-step procedure available in below article.
!! Thanks madhu !!
http://madhuottapalam.blogspot.com/2008/08/configuring-linked-server-from-sql.html

SSIS Migration from 2000 (dts) to 2008 (dtsx)

2011-05-11T16:20:00.000-07:00

Integration Services provides the DTS Package Migration Wizard for migrating SQL Server 2000 Data Transformation Services (DTS) packages that were created by using the DTS tools and object model.
Not all packages can be migrated completely. For example, some DTS tasks do not map to Integration Services tasks, custom tasks cannot be migrated, and some scripts in ActiveX Script tasks or in data transformations cannot be migrated. For more information, see Migrating Tasks and Known DTS Package Migration Issues.
Packages left in the DTS package format can be used in Integration Services solutions and can also continue to run as stand-alone DTS packages.
Many DTS packages are password-protected. If you migrate these packages, the Package Migration Wizard prompts you for their passwords. You cannot migrate a password-protected package unless you provide the correct password.
SQL Server does not install support for Meta Data Services. The Package Migration Wizard supports Meta Data Services (Repository) packages only if SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files are installed on the local computer. When the Repository files are present, the Package Migration Wizard can migrate DTS packages that were saved to Meta Data Services. When the Repository files are not present, the Package Migration Wizard can only migrate DTS packages that were saved to SQL Server or to structured storage files.
You can find some known issues in migration here

http://www.simple-talk.com/sql/ssis/dts-to-ssis-migration/
http://technet.microsoft.com/en-us/library/ms143462.aspx

I have some nice links which describes step by step how to migrate DTS to SSIS 2008.
I hope that will help u goto following urls:

http://www.infosys.com/microsoft/resource-center/DTS-SSIS-2008-migration.pdf

http://weblogs.sqlteam.com/derekc/archive/2008/02/19/60518.aspx

http://wiki.sqlis.com/default.aspx/SQLISWiki/MigrationWizardSteps.html

Downloads:
http://pragmaticworks.com/DTSxChange-vs-MSWizard.pdf

Video link for "how to migrate SQL Server 2000 DTS packages to SQL Server 2008"

http://www.msdev.com/Directory/Description.aspx?eventId=493

INDIA won the world cup 2011

2011-04-03T13:40:00.000-07:00

!! Hearty Congrats Team !!

Geospatial Data with Sql Server 2008 R2

2011-04-02T08:23:00.000-07:00

Overview
Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications which ultimately helps end users make better decisions.
Top New Features
• Use the new geography data type to store geodetic spatial data and perform operations on it
• Use the new geometry data type to store planar spatial data and perform operations on it
• Take advantage of new spatial indexes for high performance queries
• Use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio
• Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications

Visual analysis of location data
Geographical Factors in Decision-Making and Analysis
The spatial support in Microsoft SQL Server 2008 can help you make better decisions through visual analysis of location data.
Make better decisions
Visual analysis of location data in scenarios such as:
• Consumer-focused location-based information
• Customer-base management and development
• Environmental-related data impact, analysis, and planning
• Financial and economic analysis in communities
• Government-based planning and development analysis
• Market segmentation and analysis
• Scientific research study design and analysis
• Real-estate development and analysis

Geodetic Model

Planar Model
Comprehensive Spatial Support
Build spatial capabilities into your applications by using the support for spatial data in SQL Server 2008.
Work with geodetic data
• Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth’s surface
• Associate geographical data with industry standard ellipsoids, such as WGS84, which is used in GPS-enabled solutions worldwide
Work with planar data
• Implement Flat Earth solutions with the geometry data type.
• Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces
Build on industry standards
• Import and export spatial data in industry-standard formats, such as Well Known Text, Well Known Binary, and Geographic Markup Language (GML)
• Take advantage of geometry data type compatibility with Open Geospatial Consortium (OGC) standards for geometric data types
Perform spatial operations
• Use the methods provided by SQL Server 2008 spatial data types to write Transact-SQL code that performs operations on spatial data, such as finding intersections between geospatial objects and distances between locations

Spatial Results Tab
High Performance Spatial Data Capabilities
Achieve high performance spatial capabilities with SQL Server 2008
Store large and complex spatial objects
• Use the spatial types in SQL Server 2008 to accommodate spatial objects, regardless of whether the objects are simple or very complex
Build high-performance solutions with spatial data indexing
• Enhance query performance by using indexes for spatial data that are integrated into the SQL Server database engine
• Take advantage of accurate query optimizer cost assessment for spatial queries that can determine the optimal query plan and identify appropriate index selection
View results directly from within Management Studio
• Use the new spatial results tab to easily view spatial query results directly from within SQL Server Management Studio
• Simple projection and zoom/pan capabilities for quick investigation
Consolidate relational and spatial data in business applications
• Use the native support for spatial data types in SQL Server 2008 to seamlessly incorporate spatial data into line-of-business applications
• Avoid the performance and manageability issues associated with a dedicated spatial data store

Virtual Earth SDK
Geospatial Application Extensibility
Extend spatial support by integrating spatial data in SQL Server 2008 with location-enabled applications and services.
Build spatial solutions of any scale
• Take advantage of spatial support in multiple editions of SQL Server 2008, from SQL Server Express to SQL Server Enterprise Edition
Use spatial standards support to integrate applications
• Leverage a .NET-based geometry library that supports OGC standards. Build applications that consume and manipulate spatial data
• Integrate with geospatial services, such as Microsoft Virtual Earth, to build comprehensive location-enabled solutions that render your spatial data for display
Benefit from spatial community support
• Take advantage of spatial products and services offered by Microsoft partners that integrate with SQL Server 2008
• Microsoft partners with the leading GIS application providers and a range of spatial community organizations, integrators, and ISVs that support SQL Server 2008

I copied from the below site.
http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

For Installing SQL Server 2008 R2 on Windows Server 2008 R2, See the below link:
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_R2_P1.html

Visualizing the SPatial Data:
http://msdn.microsoft.com/en-us/magazine/ee335706.aspx
Download 'SPatialData' detail document and sample script from here.
http://download.microsoft.com/download/a/c/d/acd8e043-d69b-4f09-bc9e-4168b65aaa71/SpatialData.doc
Watch the video:
http://blogs.msdn.com/b/nikosan/archive/2010/11/06/data-connector-web-cast-dec-2nd-2010.aspx

Regards,
Ravindra

URGENT REQUIREMENT IN A MNC FOR MSBI RESOURCE(SSIS/SSAS/SSRS)-only in USA

2011-03-28T14:20:00.001-07:00

Hi Guys,
If you are in MSBI plaform(SSIS or SSAS or SSRS), send me your profile on my emailID.
It is really good opportunity who looking to change.
!! All The Best !!
============================================
eMailID:-->nv.ravindranathreddy@gmail.com
============================================
Regards,
Ravindra

SSAS-Dynamic Cube Partition Refresh

2011-03-15T19:57:00.000-07:00

Hi,

Here you can see Refresh the Cube Partition dynamically using SSIS Package.

The steps are involved,
 Drop the existing Partitions on the cube
 Re-Create the Partitions.
Get more details about Cube-Partitions.
http://msdn.microsoft.com/en-us/library/ms175604.aspx
Here is the link to implement Partition at cube level.
http://sql-bi-dev.blogspot.com/2010/12/dynamic-cube-partitioning-in-ssas-2008.html
Download the sampl SSIS-Package here.
http://www.ziddu.com/download/14205603/Cube_Partition.zip.html

Regards,
Ravindra

Table Partition and Archive the OLD data from a Table - Sql Server

2011-03-15T19:00:00.000-07:00

Hi Friendz,
Here i would like to introduce a concept called 'Partition On Table' in Sql server 2005/2008.

I am using SQL Server 2008 and I have table called, ‘Monthly Product’ (it contains data from 2010-January to till) every month data load approximately 30 million records, I only need to read last 12 months data for my report. Because of huge data my table response is too bad.

Then my option is -
Partition the table based on monthly wise,
Clustered Indexes then
Archive the OLD data (<12 months) into a Text file.
You can see better performance on your query using partition table.

Here i will run the package on monthly basis.
 The first task removes the Partitions on the table and re-create for last 12 months.
 The second task will Archive the Old data (< 12 months)
 The third task deletes the old data from the Report table.

Read More about Partitions:
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
You can Download The sample package from the below link.
http://www.ziddu.com/download/14205160/IntegrationServicesProject2.zip.html

Thanks, Rav's

Happy Maha-Shivaratri to All...!

2011-03-01T18:09:00.001-08:00

Wishing you all a very happy Mahahivrati. God bless you all with lots and lots of happiness, your wishes will be accomplished. Om Namah Shivaya..

Data Architect, Data Warehouse Architect and BI Solution Architect

2011-03-01T11:34:00.000-08:00

What is the difference between a data warehouse architect and a data architect? How about BI solution architect?
Both data architect and data warehouse architect do data modeling, as in ERWin stuff. Or Embarcadero ER Studio. But a data warehouse architect is more specialized on designing the data model for a data warehouse, whereas a data architect is more specialized on designing the data model for databases used by transactional systems.
A data warehouse architect does a lot more than just data modeling. They also do the ETL and the infrastructure. These are areas that a data architect doesn’t do normally.
For a data architect to be able to call themselves a data warehouse architect, they don’t only need to learn how to create a data model for a data warehouse (as in dimensional modeling). But they need to also understand the ETL architecture. And they need to understand the servers. For example, they need to be able to specify the specification for the production data warehouse servers, i.e. CPU, memory, disks. And other server stuff like clustering, mirroring and DR. And they need to understand physical database stuff too, like table partitioning, file groups and materialized views.
In my book I specify there are 2 sides of data warehouse architecture. The first one is the logical architecture and the second one is physical architecture. A warehouse architect needs to understand both.
A data warehouse architect in my opinion demands 4 separate skills: data architecture, ETL, database platform and physical infrastructure. By “database platform” I mean SQL Server knowledge, Oracle knowledge, Teradata knowledge, Netezza knowledge, etc. For example, “SQL Server 2008 Data Warehousing Features” is a “must know” for a DW architect in SQL Server. Whereas UPI & USI is a “must know” for DW architect in Teradata. If we design a DW on Oracle 11g R2, we need to know Oracle specific DW features, such as Initialization Parameter Settings, Partitionwise Joins, Compression, Parallel Execution, etc.
A BI architect is more on the application side, as in SharePoint architecture, Hyperion architecture, Reporting Services architecture, and Analysis Services architecture. For example: a BI system where we have reporting services running on SharePoint, plus excel services and PPS services on SharePoint, and SSAS cubes too reading from a warehouse or mart. And on top of that some custom .NET coding for authentication or customized security. Plus they allow some self-service BI using Qlikview or PowerPivot.
Back to the data warehouse architect, the ETL aspect of the job is sometimes quite demanding. There is something called ETL architecture in warehousing, which is basically
a) the architecture of the overnight batch, i.e. the structure of the tasks and workflows, the execution order of the workflows, the backup, the reconciliation, the checking and alert, and the data quality. The overnight batch is not only about data loading / ETL. It also has: data serving elements, i.e. processing reports (stored as PDFs, ready to be served to achieve split second response time), refreshing OLAP cubes.
b) the architecture of the continuous feed throughout the day for real time warehousing
c) the physical infrastructure of the ETL, i.e. the servers, the databases, the data connections
d) the methods to extract and load the data i.e. sliding window, swap partition, flipping twin tables, identifying incremental extraction, changed data capture, change tracking mechanism, (filtered) replication between warehouse and mart (or mirroring), how to extract data from cubes.
If a data warehouse architect only understands dimensional modeling, the company will have problems in the ETL and infrastructure. The servers might not be ‘suit for purpose’, for example disk configuration is not optimized for warehousing.
How about “BI solution architect”? A data warehouse is the back end. Business Intelligence is the front end. Data warehousing is about the data model, the ETL and the databases. BI is about the reports, the OLAP cubes, the analytical applications, the data mining, the KPIs, the dashboards, the score cards, and the performance management. It is a common perception that a BI solution architect is a “front end” person. An application person. This perception is incorrect.
A “solution architect” is responsible for the whole solution. Not only the front end, but also the back end. It is impossible for him to be responsible for the whole solution without looking after the back end. In the case of a BI solution architect, he or she needs to look after both the BI front end (reports, cubes, performance management, etc.), and the back end (data warehouse, ETL). It is impossible for a BI solution architect to be responsible for the BI without looking after the data warehouse. In some companies, instead of calling the role “BI solution architect”, it is called “data warehouse solution architect”. It doesn’t mean that the role is only responsible for the back end data warehouse, but he is also responsible for the BI front end. In some companies, they have not only one but several BI solution architects, each responsible for a certain area.
There are several different types of architects in IT, for example: system architect, data architect, solution architect, information architect and enterprise architect. This segregation of duties only happens in very large group of companies (enterprises). For example, a banking group with 3000 IT staff. If the IT staff is only 100 usually the architect does multiple functions. A system architect is responsible for infrastructure, including networks and servers. I’ve mentioned about data architect and solution architect. An information architect is responsible for the flow of information throughout the enterprise, including databases and data quality. An enterprise architect is responsible for all the applications in the enterprise, making sure they run in sync and adhere to the standards.

You can find more details from below blog:
http://dwbi1.wordpress.com/2010/06/16/data-architect-data-warehouse-architect-and-bi-solution-architect/

SSAS-Implementing Analysis Services synchronization

2011-02-24T10:05:00.000-08:00

Synchronization is a fairly simple concept resembling the snapshot replication feature available with a SQL Server relational database engine. Synchronization copies the data files from the source server to the destination server. For example, you could synchronize an Adventure Works sample database from server A to server B. If server B (destination) does not have this database yet, it will be created during synchronization. If server B already has the Adventure Works database, it will be overwritten.
If the destination database exists, it remains online while you synchronize it, so your users can continue querying the database. Analysis Services creates a separate folder in the data directory of the destination server. This folder will have a globally unique identifier (GUID) as its name -- for example 761A1D04B1C476A9886 -- and will contain only files that were modified since databases were last synchronized. In other words, synchronization is always incremental.
Note, however, that before incremental changes can be transferred you must first synchronize the entire database. Once you synchronize a primary database from a secondary database, you will only have to copy a subset of all files for subsequent synchronizations. The first synchronization must copy all files, even if the existing copies of data on both servers are identical. Keep in mind that since all files must be copied for the initial synchronization, you will need plenty of disk space to store the existing database files and those files that are being copied from the standby server.
After all necessary files are copied to the synchronization folder, Analysis Services deletes the existing folder, storing the current database files, and replaces it with the folder containing synchronized files. The change occurs very fast because it's a matter of renaming the folder from GUID to the database name (and version number). Users can continue querying the primary database, although queries will fail for a brief period while the folder is being renamed.
For example, the following command synchronizes a test database between two instances of Analysis Services:

Exploiting Analysis Services synchronization
You can use the SYNCHRONIZE command to:
Have a standby Analysis Server dedicated to processing. The primary production server will be dedicated to user queries. After you process cubes on the standby server, you can transfer modified data files to the production server.
Deploy changes from the development environment to production servers. You can include the security settings that exist on the development database or keep those settings that are defined in production. If the development database only has a subset of production data, then you will need to reprocess the production database once synchronization is complete.
Keep multiple copies of the same database on multiple servers for scaling out your analytical solution. If there are thousands of users querying your cubes, you can direct a subset of users to each server.
Keep multiple copies of the same database on multiple servers to assure high availability. Should any of your servers experience hardware issues, you could redirect users' queries to other servers.
Copy the production database to development or quality assurance servers when you need to troubleshoot performance or data accuracy issues.
Here is the screen shots for oyur referrence.

You can get more details from the below page:
http://searchsqlserver.techtarget.com/tip/Synchronizing-Analysis-Services-2005-databases-in-SQL-Server

We Are What We Think...!

2011-02-21T11:12:00.000-08:00

MSBI Interview Questions and Answers

2011-02-19T20:15:00.000-08:00

Guys,
Based on my friend request i have been posted latest questions with answers.
Please let me know if you have any comments and keep me updated with the latest questions, i will try to post you answers ASAP.
Take care
~Rav’s
SSIS
1) Use Of Event Handlers
Based on event (On error, on Progress, On Pre/Post Validate..), if you want to perform some action like send email, insert log info into table etc.. we can go for Event Handler Tab.
http://msdn.microsoft.com/en-us/library/ms140011.aspx
http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
2) Diff b/w script task & script component and its advantages.
Script task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.
Normally we can implement custom code by using these components.
By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well. In single sentence i can conclude the usage of this items we can use to perform any ETL operation if you are good at .net.
http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/
3) What is a Master package.
Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called as master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.
http://sqlblog.com/blogs/jorg_klein/archive/2010/01/04/ssis-package-design-pattern-for-loading-a-data-warehouse.aspx
4) Have you worked with data source view in ssis.
You can simply say ‘i never used so far’.
A DSV allows you to create a metadata layer over an existing Data Source. Within a DSV, you can select a subset (or all) of the tables and views available from a Data Source, rename any of the tables/views and/or their columns, add new Named Calculations (which act like computed columns within a table/view), and add new Named Queries (which are complete select statements, which basically act like views).
http://database.blogs.webucator.com/2010/10/20/how-to-create-data-source-views-in-sql-server-integration-services-2008/
5) If there are 100 Packages, do you create 100 configuration files or u create how many.
The answer is based on the requirement we will create 100 config files for 100 packages.If i go for master package and if i am using same kind of connections for the child packages like source and destinations connections then we will create less config files, because Master Package will pass all the connections using master-child relation using the variables. Creation of config files is dependence of the requirement.
http://www.mssqltips.com/tip.asp?tip=1434
http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx
ssas
1) Difference b/w Cube-Deploy and Process.
Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual Sql server analysis server. This process is called DEPLOYMENT of cube. (like creating one object(table) in the relational database)
So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table)
So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to a SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube.
Best Practice method to deploy a cube:
http://www.mssqltips.com/tip.asp?tip=1883
2) Different kinds of Dimensions. like Degenerate dimension, Conformed dimension, Junk dimension with example.

Different types of Dimensions:
I.SCD (Slowly Changing Dimension- Type 1 Type 2 and Type 3)
Type 1: The new record replaces the original record. No trace of the old record exists.
Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.
Type 3: The original record is modified to reflect the change
II. There are other three types of Dimensions:
1. Confirmed Dimensions.
2. Junk Dimensions.
3. Degenerate Dimensions.
-Confirmed Dimension:
The dimensions which is used more than one fact table is called conformed dimensions. It means we can definitely use CD in the nature
Ex: Time,Geography,customer,employee,product etc..
-Junk Dimension:
Junk dimensions are dimensions that contain miscellaneous data (like flags and indicators) that do not fit in the base dimension table.
-Degenerate Dimension :
A degenerate dimension is data that is dimensional in nature but stored in a fact table. For example, if you have a dimension that only has Order Number and Order Line Number, you would have a 1:1 relationship with the Fact table. Do you want to have two tables with a billion rows or one table with a billion rows. Therefore, this would be a degenerate dimension and Order Number and Order Line Number would be stored in the Fact table
III. Cube Dimensions
a. Fact Dimensions
b. Role-Playing Dimensions
c. Reference Dimensions
d. Many-to-Many Dimensions
http://consultingblogs.emc.com/christianwade/archive/2005/04/07/1255.aspx
http://www.informaticans.com/blog/data-warehousing-faqs/dimensions-different-types/

3) About Cube-Partions.its advantage.
We used Partitions to Improve query performance.
A partition is a file on a hard disk that contains a subset of the data included in an Analysis Services database. Partitions let you spread data over multiple hard disks. This includes combinations of both local (stored locally on hard disk) and remote (distributed across multiple hard disks) partitions. Partitions rely on storage settings to define the format and processing schedule for the database, and they use writeback settings to enable what-if analysis. What-if analysis enables a user to input their own data and evaluate the changes that cascade throughout their cube.
Method to create Partitons:
http://www.mssqltips.com/tip.asp?tip=1549

4) What is the use of data source view.
Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source
5) iIhave 10 dimensions.i will delete one.and i will process the cube .is that deleted dimension will available in cube?
If you want to remove the dimension from the cube, you can hide/delete the dimension then re-deploy the cube and you need to perform the full process then the deleted dimension won’t appear in the cube.
6) diff b/w calculated members and calculated measures
Member is nothing but column on dimension table. Measure is nothing but column on fact table.
The term calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the measures dimension where a simple MDX expression such as addition or subtraction of two or more base measures results in a new measure. Such calculated members on the measure dimension are referred to as calculated measures.
Calculated Member is related to the Dimension where as calculated Measure is related to the measure in measure group.
http://msdn.microsoft.com/en-us/library/ms166568.aspx
SSRS
1) types of parameters
Single value parameters, Multi value parameters,
Cascading parameters (one parameter is dependent on another parameter selection)
http://msdn.microsoft.com/en-us/library/aa337234.aspx
http://sql-bi-dev.blogspot.com/2010/07/report-parameters-in-ssrs-2008.html

2) what are drill through and drill down reports.
Drill down is when a power play model has several levels within a hierarchy. Given a location dimension with country/ state and city. A starting view will show the summary of countries, Drill down on one country will expose their states, drill down on a state will show their cities. So drill down goes to lower levels of a dimension as designed in the model.
Drill thru is the ability to leave PowerPlay and call a SQL Query, (say in Impromptu) passing the dimension line as filters to the query. If the columns in the query match the columns used to build the dimensions the query will automatically apply those values to the where clause. You can write any query or queries you want to support a drill thru from a cube. Context is important to balance the two
Database
1) How u done partitions.
If your database contains very large tables, you may benefit from partitioning those tables onto separate filegroups. This technology, introduced in SQL Server 2005, allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance.
http://www.mssqltips.com/tip.asp?tip=1914

2) Difference b/w view and materliazed view.
Views
A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized Views
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete
3) Have u worked with sequences.
CREATE SEQUENCE will enter a new sequence number generator into the current database. This involves creating and initializing a new single-row table with the name seqname. The generator will be owned by the user issuing the command.
http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

4) About logging in view.
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6003.htm

Frequently asked Sql Queries with answers
http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/
http://www.java2s.com/Code/SQLServer/CatalogSQLServer.htm

Some Other Interview Questions:
http://www.bigresource.com/MS_SQL-ssis-master-package-fail--MaixRNiy.html
Try to get the info from net.I do not have time to explain all these...
DTS vs SSIS: A basic overview
http://decipherinfosys.wordpress.com/2008/03/11/dts-vs-ssis-a-basic-overview/
How to create a basic Cube
http://www.mssqltips.com/tip.asp?tip=1532

Happy Valentine's Day

2011-02-03T16:08:00.001-08:00

SSRS-Types of Reports

2011-01-17T20:02:00.000-08:00

In Reporting Services, you can use reports in a variety of ways. This topic describes the terminology used to describe the various types of reports and the ways reports get created and used. A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.
With Reporting Services, you can create the following types of reports:
Parameterized reports
Linked reports
Snapshot reports
Cached reports
Ad hoc reports
Clickthrough reports
Drilldown reports
Drillthrough reports
Subreports
http://ssrstips.blogspot.com/2009/07/in-reporting-services-you-can-use.html

SSRS report Design Style-Details(Tablix)

2011-01-17T19:39:00.000-08:00

• Report Design Basics
• Specifying Report Data
• Organizing Report Data on a Page
• Improving the Report Design

http://ssrstips.blogspot.com/2009/07/report-design-basics.html
http://ssrstips.blogspot.com/2009/07/tablix-data-region-in-ssrs.html

SSRS Complex Expressions

2011-01-17T19:33:00.000-08:00

Complex expressions can contain multiple built-in references, operators, and function calls, and appear on the design surface as <>. To see or change the expression text, you must open the Expression dialog box or type directly in the Properties pane. The following table lists typical ways you can use a complex expression to display or organize data or change report appearance, including the property to set, the dialog box you typically use to set it, and the value for the property. You can type an expression directly into a dialog box, on the design surface, or in the Properties pane.

Functionality Property, Context, and Dialog Box Property Value

1. Calculate aggregate values for a dataset. Value property for a placeholder inside of a text box. Use Placeholder Properties Dialog Box, General. =First(Fields!Sales.Value,"DataSet1")
2. Concatenate text and expressions in the same text box. Value for a placeholder inside of a text box that is placed in a page header or page footer. Use Placeholder Properties Dialog Box, General. ="This report began processing at " & Globals!ExecutionTime
3. Calculate an aggregate value for a dataset in a different scope. Value for a placeholder inside of a text box that is placed in a tablix group. Use Placeholder Properties Dialog Box, General. =Max(Fields!Total.Value,"DataSet2)
4. Format data in a text box depending on value. Color for a placeholder inside of a text box in the details row for a tablix. Use Text Box Properties Dialog Box, Font. =IIF(Fields!TotalDue.Value < 10000,"Red","Black")
5. Calculate a value once to refer to throughout the report. Value for a report variable. Use Report Properties Dialog Box, Variables. =Variables!MyCalculation.Value
6. Include specific values for more than one field from a dataset. Filter equation for a group in a tablix. Use Tablix Properties Dialog Box, Filters. For data type, select Boolean.
7. =IIF(InStr(Fields!Subcat.Value,"Shorts")=0 AND (Fields!Size.Value="M" OR Fields!Size.Value="S"),TRUE, FALSE)
8. =
9. TRUE
10. Hide a text box on the design surface, that can be toggled by the user using a Boolean parameter named Show. Hiddenproperty on a text box. Use Text Box Properties Dialog Box, Visibility. =Not Parameters! Show .Value
11. Specify dynamic page header or footer content. Value for a placeholder inside of a text box that is placed in the page header or footer. ="Page " & Globals!PageNumber & " of " & Globals!TotalPages
12. Specify a data source dynamically by using a parameter. Connection string on the Data source. Use Data Source Properties Dialog Box, General. ="Data Source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks2008R2"
13. Identify all the values for a multivalue parameter chosen by the user. Value for a placeholder inside of a text box. Use Tablix Properties Dialog Box, Filters. =Join(Parameters!MyMultivalueParameter.Value,", ")
14. Specify page breaks for every 20 rows in a tablix with no other groups. Group expression for a group in a tablix. Use Group Properties Dialog Box, Page Breaks. Select the option Between each instance of a group. =Ceiling(RowNumber(Nothing)/20)
15. Specify conditional visibility based on a parameter. Hidden property for a tablix. Use Tablix Properties Dialog Box, Visibility. =Not Parameters!< boolean parameter >.Value
16. Specify a date formatted for a specific culture. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. =Fields!OrderDate.Value.ToString(System.Globalization.CultureInfo.CreateSpecificCulture("de-DE"))
17. Concatenate a string and a number formatted as a percentage to two decimal places. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. ="Growth Percent: " & Format(Fields!Growth.Value,"p2")

Sql Server/SSAS/SSRS Discussion Forum AND Questions

2011-01-03T17:22:00.000-08:00

Hi Guys,

Here i have listed couple of discussions on Sql Serv 2005/2008.Here you can find SSRS/SSAS questions as well.
Please utilize this opportunity to involve this forum.
Here i just did copy/paste job.

Bunch Of Thanks to--> "StackOverFlow".

http://stackoverflow.com/tags/reportingservices-2005/new

reportingservices-2005
ssrs-2005
reportingservices2005
reporting-services-2005
reporting-services
sql-server-2005
sql-server
ssrs-reports
asp.net
reporting
c#
reportingservices-2008
sql
ssrs-2008
reportviewer
vb.net
.net
bids
excel
iis
parameters
pdf
printing
rdl
subreport
visual-studio
web-services
table
tsql

Regards,
Ravindra

!!~HAPPY NEW YEAR~!!

2010-12-31T16:52:00.000-08:00

Hi All
______________________________________________
Wishing you & your family,
A Very Happy, Prosperous
!! New Year 2011 !!

Cheers,
Ravindra

New Features in PerformancePoint Services (SharePoint Server 2010)

2010-12-11T16:27:00.000-08:00

PerformancePoint Services in Microsoft SharePoint Server 2010 is a performance management service that you can use to monitor and analyze your business. By providing flexible, easy-to-use tools for building dashboards, scorecards, reports, and key performance indicators (KPIs), PerformancePoint Services can help everyone across an organization make informed business decisions that align with companywide objectives and strategy. Scorecards, dashboards, and KPIs help drive accountability. Integrated analytics help employees move quickly from monitoring information to analyzing it and, when appropriate, sharing it throughout the organization.

Prior to the addition of PerformancePoint Services to SharePoint Server 2010, Microsoft Office PerformancePoint Server 2007 functioned as a standalone server. Now PerformancePoint functionality is available as an integrated part of the SharePoint Server Enterprise license, as is the case with Excel Services in Microsoft SharePoint Server 2010. The popular features of earlier versions of PerformancePoint Services are preserved along with numerous enhancements and additional functionality.

What's new

PerformancePoint Services includes many new and updated features and functionality. For convenience, this document separates these updates into the following two categories:

• Platform integration with SharePoint Products and Technologies  

• New PerformancePoint Services features  

Platform integration with SharePoint Products and Technologies

PerformancePoint Services builds on the platform of SharePoint 2010 Products, providing customers with a more robust deployment, scalability, and performance model. The previous version was built directly on Microsoft Internet Information Services (IIS) and used a Microsoft SQL Server database. PerformancePoint Services is a SharePoint Server service application and uses SharePoint document libraries and lists to store content. These architectural changes enable PerformancePoint Services to take advantage of SharePoint Server 2010 enterprise features.

Physical architecture

For information about the physical architecture, see the diagram in Overview of PerformancePoint Services architecture, which shows the PerformancePoint Services architecture for farm deployment that utilizes three servers.

PerformancePoint Services as a service application

In SharePoint Server 2010, services are no longer contained within a Shared Service Provider (SSP). Instead, the infrastructure for hosting services is integrated with Microsoft SharePoint Foundation and the configuration of service offerings is much more flexible. The service application framework is a common service model that provides the following benefits:

• A consistent management experience and shared infrastructure for all services that plug into it. As one of those services, PerformancePoint Services can provide the ability to perform bulk security operations for service applications in a farm deployment from the SharePoint Central Administration Web site. 

• Backup and recovery benefits include restoring a site collection, entire site content, or list content back to a previous version or point in time. 

• Services are installed by default; there is no SSP setup. 

• Sites can be configured to use only the services that are needed rather than the entire list of services. 

• Deploying services across sites and farms is more flexible. Similar to previous versions, a single set of services can be shared by all Web sites in a farm. 

SharePoint Server 2010 as the repository

PerformancePoint Services stores data sources and dashboards in document libraries and all other dashboard content in lists. In addition, PerformancePoint Services data security and management is enhanced by the following features:

• Failover and up-time strategies 

• Backup and restore strategies 

• Disaster-recovery strategies 

• Multi-tenancy support for list content 

• Enterprise-level, single-security model 

• Authentication and authorization schemes 

• Trusted locations 

• Familiar interface for storing and consuming data 

Changes to the security model

PerformancePoint Services uses SharePoint Server 2010 to manage user credentials and to secure access to dashboard content and its underlying data sources. The new and changed features of the PerformancePoint Services security model are described in the following list:

• User authentication is handled by SharePoint Server 2010. Authentication of PerformancePoint Services users is validated by the SharePoint Server 2010 authentication provider. 

• Trusted locations are used to limit access to PerformancePoint Services content types to specific sites.  

• PerformancePoint Services uses the SharePoint Server 2010 security model to set permissions on dashboard content. These permissions provide a level of security that is equivalent to that found in Microsoft Office PerformancePoint Server 2007, but permissions in SharePoint Server 2010 do not map directly to PerformancePoint Monitoring Server 2007 roles and permissions. 

New PerformancePoint Services features

PerformancePoint Services now can utilize SharePoint Server scalability, collaboration, backup and recovery, and disaster recovery capabilities. Dashboards and dashboard items are stored and secured within SharePoint lists and libraries, providing you with a single security and repository framework. The following section provides a summary of feature enhancements.

New features and enhancements

The following is an overview of features and enhancements to PerformancePoint Services in Microsoft SharePoint Server 2010.

• With PerformancePoint Services, functioning as a service in SharePoint Server, dashboards and dashboard items are stored and secured within SharePoint lists and libraries, providing you with a single security and repository framework. The new architecture also takes advantage of SharePoint Server scalability, collaboration, backup and recovery, and disaster recovery capabilities. You also can include and link PerformancePoint Services Web Parts with other SharePoint Server Web Parts on the same page. The new architecture also streamlines security models that simplify access to report data. 

• The Decomposition Tree is a new visualization report type available in PerformancePoint Services. You can use it to quickly and visually break down higher-level data values from a multi-dimensional data set to understand the driving forces behind those values. The Decomposition Tree is available in scorecards and analytic reports and ultimately in dashboards. 

• You can access more detailed business information with improved scorecards. Scorecards have been enhanced to make it easy for you to drill down and quickly access more detailed information. PerformancePoint scorecards also offer more flexible layout options, dynamic hierarchies, and calculated KPI features. Using this enhanced functionality, you can now create custom metrics that use multiple data sources. You can also sort, filter, and view variances between actual and target values to help you identify concerns or risks. 

• Better Time Intelligence filtering capabilities that you can use to create and use dynamic time filters that are always up to date. Other improved filters improve the ability for dashboard users to quickly focus in on information that is most relevant. 

• Ability to include and link PerformancePoint Services Web Parts together with other PerformancePoint Services Web parts on the same page. 

• Easier to author and publish dashboard items by using Dashboard Designer. 

• SQL Server Analysis Services 2008 support. 

• Increased support for accessibility compliance in individual reports and scorecards. 

• The KPI Details report is a new report type that displays contextually relevant information about KPIs, metrics, rows, columns, and cells within a scorecard. The KPI Details report works as a Web part that links to a scorecard or individual KPI to show relevant metadata to the end user in SharePoint Server. This Web part can be added to PerformancePoint dashboards or any SharePoint Server page. 

• Create analytics reports to better understand underlying business forces behind the results. Analytic reports have been enhanced to support value filtering, new chart types, and server-based conditional formatting. 

Retired features

PerformancePoint Services no longer supports Trend Charts, PivotTable reports, PivotChart reports, ODBC data sources, Analysis Services 2000, and 32-bit server architecture.

Conclusion

By becoming more tightly integrated with SharePoint Server 2010, PerformancePoint Services takes advantage of many enterprise-level SharePoint Server 2010 features. This change in architecture results in many changes and enhancements to PerformancePoint Services.

You can see more details on:

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

PerformancePoint Server 2007 Top Blogs

2010-12-11T16:18:00.000-08:00

• The Microsoft PerformancePoint Team Blog—Find in-depth discussions on the monitoring and analytics aspects of Office PerformancePoint Server 2007.

• Kevin White's PerformancePoint Server 2007 Blog—Find in-depth discussions on Office PerformancePoint Server 2007.

• Previous Version Support—Get help with ProClarity and Business Scorecard Manager.

• Help for PerformancePoint Server 2007—Browse PerformancePoint Server 2007 Help

• TechNet Planning, Monitoring, and Analytic—This forum focuses on PerformancePoint Server and Management Reporter

• The BI Blog—Various posts on everything BI

• TechNet: ProClarity—This forum focuses on technical questions related to Microsoft ProClarity products.

• SQL Server TechNet Forums— This forum focuses on technical questions related to Microsoft Analysis Services, Reporting Services, and Data Mining

• Mosha's Blog—This blog has important information on MDX and other things related to Analysis Services

Norm's Blog—Bits of knowledge from the field and beyond

About PerformancePoint Server 2007

2010-12-11T15:45:00.000-08:00

What Is PerformancePoint Server?
Microsoft Office PerformancePoint Server 2007 is an integrated performance management application that enables you to monitor and analyze your business.

By providing flexible, easy-to-use tools for building dashboards, Office PerformancePoint Server 2007 can help everyone across your organization make informed business decisions that align with companywide objectives.
PerformancePoint Server 2007 Top Benefits
The top benefits of Microsoft Office PerformancePoint Server 2007 can help you transform your business process and turn your data into a competitive advantage.

1. A complete and integrated performance management solution

Many performance management applications require connectors, interfaces, and add-ins to link their various applications and to the Microsoft Office environment. Office PerformancePoint Server 2007 is natively integrated across all aspects of the solution—from reporting and analysis, to dashboarding and forecasting.

PerformancePoint Server 2007 is designed to improve operational performance across all departments and all levels of your organization. You can use this single integrated application to monitor and analyze the success your company enjoys or the barriers it faces and to plan for the future of your business.

2. Performance management for all business users

While many solutions on the market focus on business analysts and BI experts, Office PerformancePoint Server 2007 is designed to reach everyone involved in your business at all levels of your organization. PerformancePoint Server 2007 provides a user experience in the familiar and easy-to-use Microsoft Office system, including Microsoft Office Excel, Office Outlook, and Office SharePoint Server.

And through the scalability and high performance of Microsoft SQL Server 2005 and the Microsoft Business Intelligence platform, your organization can afford to deliver the right information that helps the broadest number of people make better, faster, and more relevant decisions without continually burdening the IT department.

3. Monitoring and analyzing across the entire organization

Because of high cost, high complexity, and the need for significant IT support, most performance management environments limit their access to just a few executives or analysts. Because Office PerformancePoint Server 2007 resides within the Microsoft applications that you use today, it’s now economically feasible for all executives, managers, and front-line employees to have the same access to valuable insight from a high-quality performance management application.

Everyone can access the metrics, key performance indicators, dashboards, and reports to see how they contribute to the performance of the organization and to the overall corporate strategy—all leading to improved corporate performance.

4. Advanced analytic and visualization capabilities

Analysis is a core component of the performance management cycle. An organization is at a competitive advantage if every decision maker in it can quickly and easily perform analysis that helps them make better decisions and execute better against corporate goals and objectives.

Using the capabilities incorporated into Office PerformancePoint Server 2007, people can benefit from a single business data model shared across monitoring, and analytics. PerformancePoint Server 2007 guides information workers so they can easily monitor what is happening and make better sense of what the data analysis is telling them. The advanced visualization capabilities can help you spot trends and opportunities easily, allowing for better planning for your business.

5. Accountability from the individual to the enterprise

Office PerformancePoint Server 2007 uses a model-driven approach. Users can create scorecards, dashboards, and analytics that align with their own responsibilities and goals and with the overall goals and objectives of the organization. Providing maximum flexibility, the system accommodates unique departmental business models that synchronize with those above and below them in the organization, as well as across departments and up to the corporate level. So everyone has an up-to-date, consistent view of the data and the most current insight on the entire organization’s performance.

6. Cross-enterprise view

Office PerformancePoint Server 2007 provides a complete perspective of business performance across enterprise functions such as finance, operations, sales, and human resources. PerformancePoint Server 2007 uses SQL Server 2005 to integrate information across disparate enterprise systems and business functions, providing all users with a more strategic and forward-looking view of the business.

7. Enhanced partnership between IT and business analysts

While IT provides the infrastructure and security for crucial business applications, Office PerformancePoint Server 2007 business modeling tools enable business analysts to use their insight to manage roles, workflows, business rules, and information without having to continually rely on IT input. This increases the pace at which business models can be developed, deployed, and modified to meet changing business conditions.
PerformancePoint Server 2007 also features a user-friendly design and application wizard that business people can use to build their own dashboards and scorecards and create interactive analytics charts and graphs without the need for IT support.

8. Built on the Microsoft Business Intelligence platform

Using the Microsoft Business Intelligence platform, Office PerformancePoint Server 2007 can help you build an enterprise-grade, scalable performance management application. PerformancePoint Server 2007 uses Microsoft Office as its primary interface, so business users can interact with familiar tools like Excel to consume information, collaborate with colleagues, and contribute to plans and the performance management cycle.

PerformancePoint Server 2007 also uses the proven data engine of SQL Server 2005 for data integration; an analytical data model that includes a calculation engine and data mining; and a highly flexible and scalable reporting platform to broadcast information to a wide audience. All of this is delivered via the Web in Microsoft Office SharePoint Server 2007, providing everyone with a central place to get their business intelligence information.

MSBI(SSIS,SSAS with MDX,SSRS) No 1 Training Institute && Online Training

2010-12-11T15:11:00.000-08:00

Hi Guys,
Excellent Opportunity. Definitely you will get job in 2 months.
for more details Send me an eMail at→ nv.ravindranathreddy@gmail.com
Right Opportunity at Right Place.
Regards,
Ravindra

Future MSBI-Power Pivot ‘N’ DAX

2010-11-19T10:57:00.001-08:00

Here are Vidas comments on Power Pivot and DAX
- from the session ”SQLCAT: A Preview of PowerPivot Best Practices”. Presenters are Denny Lee and Dave Wickert:
• #PowerPivot spreadsheet is stored in the SQL database when deployed to SharePoint
• #powerPivot uses AS engine – in memory column based store w/VertiPaq. All calculations are local after import.
• Excel has it’s own local in process SSAS engine. New#powerPivot functions – Data Access eXpressions (DAX)
• #powerPivot compression 10:1 (approx) and depends…
• More info about #PowerPivot:VertiPaq does compression about 10:1, then SSAS does on top compression 1.5:1 or 2:1.
• MOLAP stores multiple records in the grain of fact table, so some records could be rolled up
• Vertipaq has a row for each source data, so different from MOLAP.
• There is on disk structure for #PowerPivot that looks similar to SSAS. c:\local user\… Additional info: Denny Lee just blogged about this with more details.
• Sub-folder customdata has file with backup of SSAS database for #PowerPivot.
• Showing demo of silverlight gallery of #PowerPivot reports in SharePoint
• #PowerPivot - when open workbook in Excel services, no interaction with SSAS first
• when click on slicer, excel services /#PowerPivot service talks to AS service engine. So on demand loading of SSAS DB.
• first initialization could take time – depends on traffic, etc. Same published #PowerPivot can serve many people.
• Need to optimize SharePoint for #PowerPivot setup.
• Capacity planning-need memory for any in use database and buffer 10-20% for auto detection(?).
• #PowerPivot keeps cache of detached databases. in the SSAS backup folder
• SSAS servers will be 64-128GB and more for #PowerPivot. Will work with 8GB, but most likely you will want more.
• #PowerPivot capacity planning depends - #users total/concurrent, # of files, max file size, data refresh options
• in SharePoint content db workbooks are stored as blobs . Could use Remote Blob store in FileStream. Overhead 2-3%
• but better handle higher concurrency scenarios (cont..)
• different topology options – one sharepoint server, multiple servers with different services on them, etc…
• Currently SharePoint requires Kerberos security all the way
• in SharePoint 2010 – new “claims tokens”, so no need for Kerberos for #PowerPivot
• Kerberos still might be required, but there is reduction in cases when you need it for #PowerPivot and SharePoint 2010.
• #PowerPivot - before installing YOU MUST READ MANUAL! That is important.
• upgrade SharePOInt 2007 to 2010 is very complex for#PowerPivot. Cannot upgrade CTP2->CTP3. Not clear CTP3->RTM.
• SharePoint is optimized for download, so there are concerns for upload #PowerPivot (large files)
• power pivot max size 2GB (SharePoint limit). But you can create >2GB #PowerPivot files, just not upload to Sharepoint. Additional info: Actual max publish size could be 1.8GB. Although it is limitation, it will affect very few users, as majority of them will not have such large files, so there is no need to worry about this
• in sharepoint LargeChunkFileSize parameter is useless – don’t touch it for #PowerPivot.
• to upload #PowerPivot to SharePoint you need very good network connection! Maybe first copy to SharePoint server – faster.
• troubleshooting – error messages very en-cryptic for#PowerPivot.
• use ULS logs (Bing or google for more info) from SharePoint to troubleshoot#PowerPivot.
• from ULS logs use correlationID to track down log data for one event. Log files very large, filter by time too. #powerpivot
there is no separate #PowerPivot log. Error could be in#PowerPivot, Excel services, etc., so ULS one option.
• You can use SQL Profiler to troubleshoot#PowerPivot SSAS instance! Good news!
• so to troubleshoot #PowerPivot you need to know SharePoint and SSAS and use tools! Consultant will be still busy…
• session almost done, time for lunch. See you latter tonight for #PowerPivot DAX session!
• create a view with fewer records and create#PowerPivot, deploy to Sharepoint, then update view to include all records. Faster.
• #PowerPivot tip: Rename *.xlsx file to *.zip and inside you will see data file that you can rename to *.abf and restore to SSAS server that runs in PowerPivot integrated mode
• this was the best session so far at - Best Practice on#PowerPivot by @dennylee and Dave Wickert
- from the session “DAX in #PowerPivot for Excel 2010″ by Howie Dickerman.
• Data Analysis Expressions = DAX.
• DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis.
• sample DAX: =[Qty]*[Price] – syntax just like Excel.
• DAX uses Excel functions, but no notion of addressing indv cells or ranges – instead columns in the data
• DAX is not replacement for MDX
• DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships
• DAX one to many function sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount])
• DAX has functions to assist with dynamic aggregations of measures:
• DAX dynamic sample: =VALUES(Time[year]) & “.” & VALUES(Product[ProductID])
• DAX sample: =IF(VALUES(Time[Year])=2008,”Baseline”,”normal”)
• DAX calc expression in 2 places – calc columns (full materialize) and measures (eval for each cell dynamically)
• More than 80 Excel functions in DAX
• CTP3 has new FORMAT function to allow to convert any number to string #DAX
• DAX sample: [salesAmt]/[SalesAmt](All(Product)) – use measure as function.
• DAX = Sales[SalesAmt]/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax
• CTP3 DAX has 35 Time Intelligence Functions – require date column in the data. Column type Date
• DAX this version does not have custom time periods and weeks. Works just with Yr, Qtr,Mth,Day
• DAX function samples: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear
• DAX: DateAdd, DatesBetween, DatesInperiod – 26 function that return a table of dates
• DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc <-more functions
• Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))
• AllTime – need to add now in CTP3, not in RTM(will be under cover). So that first year has data.
• DAX sample: QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))
• DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))
• I missed single quotes in same functions prev. If table name contains spaces, need to put single quote around names.
• DAX autocomplete in CTP3 add single quotes, but they are optional.
More Details:
http://www.ssas-info.com/VidasMatelisBlog/195_my-tweets-from-sql-pass-summit-2009-ssas-powerpivot-dax-and-more
http://ms-olap.blogspot.com/2010/05/self-service-bi-powerpivot-and-future.html#comment-form
http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/
~Rav’s

My latest Pics-Smoky mountain,tennesse

2010-11-03T20:57:00.000-07:00

SSRS Report Deployment Procedure in Sharepoint server

2010-11-03T20:37:00.001-07:00

Hi Guys,
Once we develop the SSRS report,we have to deploy this report into sharepoint portal to provide the facility to access these reports by power users /CEO’s.
Here I explained detailed deployment procedure,Please have a look.
Step-1:SSRS Reports Deploy at Report Server
1. Open the SSRS Report Manager Production URL
https://servername.com/reports

2. Go to the Home Folder in Report Manager.

3. Go to Home/DataSources folder

4. Click on “New Data Source”. Make sure the settings in the “Properties/General” tab match with the settings like: Name,Connection Type,Connection String and report server credentials.

5. At the very bottom of the page, click on “Apply”

6. Go to the Home/ Administration folder and for each of the report
o Click “Upload File” button
o Navigate to Reports location folder
o Select each report RDL file from the table below
o à click “OK”

7. Click on “Properties” > “Data Sources” and then “Browse” button

8. Expand the “Data Sources” folder and then select the Data Source “XXXX”

9. Click “OK” and then “Apply”

All the required reports should have been successfully migrated to server(Test/Prod).

Step-2: Changes at Report Viewer Level
In the Report Viewer folder, Edit the XXXMenu.xml file.
Under the Administration Reports, Add the new tag.
“

“

Step-3: Changes at sharepoint portal
Copy the link from the report server for the report and add them to the SharepointPortal Source editor HTML .
1. Goto the portal page where you want to show this report and select the drop down button in that section and choose option ‘Modify Shared WebPart’ option ans selct the Source Editor button from Content Editor window.
2. Copy the report url from Report Server and place into the Portal Content Editor Window.Place the below code inside of that…
“
href="https://servername.com/ReportServer/Default.aspx?%2fReports%2fAdmin+Sales+ Report&rs:Command=Render" target="_blank">Sales Report

“
That’s it.Now User is able to access the report from sharepoint portal.

We have different methods to deploy the reports.
See the below blogs to know some other deployment techniques.

http://blah.winsmarts.com/2007-12-SharePoint_and_SQL_Server_Reporting_Services_-_Authoring_a_simple_report.aspx

http://bisqlserver.rdacorp.com/2008/08/deploy-ssrs-reports-in-sharepoint.html

~Rav’s

SSAS-Data Security

2010-11-01T19:44:00.001-07:00

Hi,
SSAS provides the way to secure analysis services database/cube data from unauthorized access. Analysis services provides secure access by creating object called "roles". After creation of role, user's windows login credential can be used to enroll into particular role because analysis services identifies user from their windows login credentials . You can protect your data in roles at two levels:
1) Dimension level
2) Cell level
If user has been assigned more than one role, analysis services loop through all assigned roles after login. Analysis services finds all permission level for the particular user and union all the permission levels.
If two roles has contradictory access for user then particular access will be allowed. Suppose role1 says Australia data access and role2 denies Australia data access then access to Australia data will be allowed.

http://learnmicrosoftbi.blogspot.com/2010/10/ssas-data-security-dimension-and-cell.html

SSAS-Data Security

2010-11-01T19:44:00.000-07:00

SSAS-Interview Questions

2010-11-01T19:42:00.002-07:00

Nice Post.!!Thanks Amit!!
http://learnmicrosoftbi.blogspot.com/search/label/Interview%20Questions

SSAS-MDX functions

2010-11-01T19:42:00.001-07:00

Hi Guys,
Please look into the below site for more MDX functions.
Regards,
Rav’s
http://www.ssas-info.com/analysis-services-articles/50-mdx/2518-mdx-tutorials-mdx-queries-mdx-samples

SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution

2010-11-01T19:41:00.001-07:00

Hi, MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted.
One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements had to be written to process three different activities (INSERT, UPDATE or DELETE); however, by using MERGE statement, all the update activities can be done in one pass of database table.
See the below blog for more info.Nice article from Dave.
http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/

My lovely Photos

2010-09-25T09:53:00.000-07:00

Here is my Latest Photo

2010-09-25T09:46:00.000-07:00

SSAS - Usage Of OLAP PivotTable Extensions && PT Power

2010-09-22T12:27:00.000-07:00

Hi Guys,
If you want to verify the background MDX query while you are browsing the cube from Excel, i have a very simple solution for you.By using the above 2 Add-Ins, we can analyze the data and MDX funcitionality in easy manner.
This is the best way to resolve the issues on Multi dimensional data.
ThanksRavindra
Useful Links:
http://olappivottableextend.codeplex.com/
http://www.sqlserverpower.com/UtilityDetail/PTPower.aspx

Capacity Planning for the DW/BI Environment

2010-09-13T17:02:00.000-07:00

Hi All-
Capacity planning is a problem for a data warehouse because it sets contrasting functional requirements against each other. On one hand, data warehouse customers consume data warehouse capacity as they query the data in the data warehouse business intelligence (BI) reporting. Meanwhile, applications consume data warehouse capacity as they load data into a data warehouse through the extract, transform and load (ETL) process. These two functions, BI reporting and ETL, grow in volume and frequency as a data warehouse grows. Additionally, database administrator (DBA) tasks, such as backups and table reorganizations can cause additional data warehouse bottlenecks because ETL, BI reporting and DBA tasks contend for the same resources.
On top of that even we can maintain capacity of DW/BI environment by analyzing the below analytical approaches.
how many customers will be in the warehouse?,
at what rate will the customers grow?,
how many transactions will be in the warehouse?,
at what rate will the transactions grow?,
what other data will be in the warehouse?,
at what rate will the other data grow?,
what is the proper level of granularity for data in the warehouse?,
can the level of granularity be changed if needed?,
what amount of history is needed in the warehouse?,
will the user decide to add more history than anticipated?, and so forth.
See the below links for more info.
http://www.inmoncif.com/view/33
http://www.dmforum.org/portal/library/capacityplanningforthedwInmon.pdf
~Ravindra

Sql Server Useful Date() FUNC

2010-08-26T15:50:00.000-07:00

/*
Many useful SQL Server Date functions.
*/
--DATE CONVERSION
SELECT CONVERT (date, CURRENT_TIMESTAMP) ,CONVERT (date, GETDATE()) ,CONVERT (date, GETUTCDATE());
--DATE ADD()
SELECT DATEADD(year,10, getdate());
SELECT DATEADD(month,10, getdate());
SELECT DATEADD(day,10, getdate());
--DATE DIFF()
select DATEDIFF (day,getdate() ,'2010-05-26')
select DATEDIFF (month,getdate() ,'2010-05-26')
--DATE NAME()
SELECT DATENAME(year, '12:10:30.123') ,DATENAME(month, '12:10:30.123') ,DATENAME(day, '12:10:30.123') ,
DATENAME(dayofyear, '12:10:30.123') ,DATENAME(weekday, '12:10:30.123') ,DATENAME(hour, '2007-06-01') ,
DATENAME(minute, '2007-06-01') ,DATENAME(second, '2007-06-01');
--DATE PART()
SELECT DATEPART(year, '12:10:30.123') ,DATEPART(month, '12:10:30.123') ,DATEPART(day, '12:10:30.123')
,DATEPART(dayofyear, '12:10:30.123') ,DATEPART(weekday, '12:10:30.123');
****************************************************************************
select DATEPART(dw, getdate()) AS 'Today';
SELECT CURRENT_TIMESTAMP ,GETDATE() ,GETUTCDATE();
select convert(varchar(12),getdate(),112) AS 'YYYYMMDD'
UNION ALL
select convert(varchar(12),getdate(),112) AS 'YYYYMMDD'
UNION ALL
select convert(varchar(10),getdate(),120) AS 'YYYY-MM-DD'
UNION ALL
SELECT DATENAME (MM,GETDATE())
****************************************************************************
****************************************************************************
----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

Datawherehouse Questions & Answers

2010-08-26T15:46:00.000-07:00

What's A Data warehouse
Answer1:A Data warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated. This makes it much easier and more efficient to run queries over data that originally came from different sources". Another definition for data warehouse is: " A data warehouse is a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision-making tasks, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favour efficient data analysis and reporting (especially OLAP)". Generally, data warehousing is not meant for current "live" data, although 'virtual' or 'point-to-point' data warehouses can access operational data. A 'real' data warehouse is generally preferred to a virtual DW because stored data has been validated and is set up to provide reliable results to common types of queries used in a business. Answer2:Data Warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated....This makes it much easier and more efficient to run queries over data that originally came from different sources. Typical relational databases are designed for on-line transactional processing (OLTP) and do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently than traditional relational databases.

What is ODS?
1. ODS means Operational Data Store. 2. A collection of operation or bases data that is extracted from operation databases and standardized, cleansed, consolidated, transformed, and loaded into an enterprise data architecture. An ODS is used to support data mining of operational data, or as the store for base data that is summarized for a data warehouse. The ODS may also be used to audit the data warehouse to assure summarized and derived data is calculated properly. The ODS may further become the enterprise shared operational database, allowing operational systems that are being reengineered to use the ODS as there operation databases.

What is a dimension table?
A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. it contains only the textual attributes.

What is a lookup table?
A lookUp table is the one which is used when updating a warehouse. When the lookup is placed on the target table (fact table / warehouse) based upon the primary key of the target, it just updates the table by allowing only new records or updated records based on the lookup condition.

Why should you put your data warehouse on a different system than your OLTP system?
Answer1:A OLTP system is basically " data oriented " (ER model) and not " Subject oriented "(Dimensional Model) .That is why we design a separate system that will have a subject oriented OLAP system... Moreover if a complex querry is fired on a OLTP system will cause a heavy overhead on the OLTP server that will affect the daytoday business directly. Answer2:The loading of a warehouse will likely consume a lot of machine resources. Additionally, users may create querries or reports that are very resource intensive because of the potentially large amount of data available. Such loads and resource needs will conflict with the needs of the OLTP systems for resources and will negatively impact those production systems.

What are Aggregate tables?
Aggregate table contains the summary of existing warehouse data which is grouped to certain levels of dimensions.Retrieving the required data from the actual table, which have millions of records will take more time and also affects the server performance.To avoid this we can aggregate the table to certain required level and can use it.This tables reduces the load in the database server and increases the performance of the query and can retrieve the result very fastly.

What is Dimensional Modelling? Why is it important ?
Dimensional Modelling is a design concept used by many data warehouse desginers to build thier datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measuremnets ie, the dimensions on which the facts are calculated.

Why is Data Modeling Important?
Data modeling is probably the most labor intensive and time consuming part of the development process. Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users. The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements.

What is data mining?
Data mining is a process of extracting hidden trends within a datawarehouse. For example an insurance dataware house can be used to mine data for the most high risk people to insure in a certain geographial area.

What is ETL?
ETL stands for extraction, transformation and loading. ETL provide developers with an interface for designing source-to-target mappings, ransformation and job control parameter.· ExtractionTake data from an external source and move it to the warehouse pre-processor database. · TransformationTransform data task allows point-to-point generating, modifying and transforming data. · LoadingLoad data task adds records to a database table in a warehouse.

What does level of Granularity of a fact table signify?
GranularityThe first step in designing a fact table is to determine the granularity of the fact table. By granularity, we mean the lowest level of information that will be stored in the fact table. This constitutes two steps: Determine which dimensions will be included.Determine where along the hierarchy of each dimension the information will be kept.The determining factors usually goes back to the requirements

What is the Difference between OLTP and OLAP?
Main Differences between OLTP and OLAP are:- 1. User and System Orientation OLTP: customer-oriented, used for data analysis and querying by clerks, clients and IT professionals. OLAP: market-oriented, used for data analysis by knowledge workers( managers, executives, analysis). 2. Data Contents OLTP: manages current data, very detail-oriented. OLAP: manages large amounts of historical data, provides facilities for summarization and aggregation, stores information at different levels of granularity to support decision making process. 3. Database Design OLTP: adopts an entity relationship(ER) model and an application-oriented database design. OLAP: adopts star, snowflake or fact constellation model and a subject-oriented database design. 4. View OLTP: focuses on the current data within an enterprise or department. OLAP: spans multiple versions of a database schema due to the evolutionary process of an organization; integrates information from many organizational locations and data stores

What is SCD1 , SCD2 , SCD3?
SCD Stands for Slowly changing dimensions. SCD1: only maintained updated values. Ex: a customer address modified we update existing record with new address. SCD2: maintaining historical information and current information by using A) Effective DateB) VersionsC) Flagsor combination of these SCD3: by adding new columns to target table we maintain historical information and current information.

Why are OLTP database designs not generally a good idea for a Data Warehouse?
Since in OLTP,tables are normalised and hence query response will be slow for end user and OLTP doesnot contain years of data and hence cannot be analysed.

What is BUS Schema?
BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.
What are the various Reporting tools in the Market?
1. MS-Excel2. Business Objects (Crystal Reports)3. Cognos (Impromptu, Power Play)4. Microstrategy5. MS reporting services6. Informatica Power Analyzer7. Actuate8. Hyperion (BRIO)9. Oracle Express OLAP10. Proclarity

What is Normalization, First Normal Form, Second Normal Form , Third Normal Form?
1.Normalization is process for assigning attributes to entities–Reducesdata redundancies–Helps eliminate data anomalies–Produces controlledredundancies to link tables 2.Normalization is the analysis offunctional dependency between attributes / data items of userviews?It reduces a complex user view to a set of small andstable subgroups of fields / relations 1NF:Repeating groups must beeliminated, Dependencies can be identified, All key attributesdefined,No repeating groups in table 2NF: The Table is already in1NF,Includes no partial dependencies–No attribute dependent on a portionof primary key, Still possible to exhibit transitivedependency,Attributes may be functionally dependent on non-keyattributes 3NF: The Table is already in 2NF, Contains no transitivedependencies

What is Fact table?
Fact Table contains the measurements or metrics or facts of business process. If your business process is "Sales" , then a measurement of this business process such as "monthly sales number" is captured in the Fact table. Fact table also contains the foriegn keys for the dimension tables.

What are conformed dimensions?
Answer1:Conformed dimensions mean the exact same thing with every possible fact table to which they are joined Ex:Date Dimensions is connected all facts like Sales facts,Inventory facts..etc Answer2:Conformed dimentions are dimensions which are common to the cubes.(cubes are the schemas contains facts and dimension tables) Consider Cube-1 contains F1,D1,D2,D3 and Cube-2 contains F2,D1,D2,D4 are the Facts and Dimensions here D1,D2 are the Conformed Dimensions

What are the Different methods of loading Dimension tables?
Conventional Load:Before loading the data, all the Table constraints will be checked against the data. Direct load:(Faster Loading)All the Constraints will be disabled. Data will be loaded directly.Later the data will be checked against the table constraints and the bad data won't be indexed.

What is conformed fact?
Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly

What are Data Marts?
Data Marts are designed to help manager make strategic decisions about their business. Data Marts are subset of the corporate-wide data that is of value to a specific group of users. There are two types of Data Marts:1.Independent data marts – sources from data captured form OLTP system, external providers or from data generated locally within a particular department or geographic area. 2.Dependent data mart – sources directly form enterprise data warehouses.

What is a level of Granularity of a fact table?
Level of granularity means level of detail that you put into the fact table in a data warehouse. For example: Based on design you can decide to put the sales data in each transaction. Now, level of granularity would mean what detail are you willing to put for each transactional fact. Product sales with respect to each minute or you want to aggregate it upto minute and put that data.

How are the Dimension tables designed?
Most dimension tables are designed using Normalization principles upto 2NF. In some instances they are further normalized to 3NF. Find where data for this dimension are located. Figure out how to extract this data. Determine how to maintain changes to this dimension (see more on this in the next section).

What are non-additive facts?
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

What type of Indexing mechanism do we need to use for a typical datawarehouse?
On the fact table it is best to use bitmap indexes. Dimension tables can use bitmap and/or the other types of clustered/non-clustered, unique/non-unique indexes. To my knowledge, SQLServer does not support bitmap indexes. Only Oracle supports bitmaps.

What Snow Flake Schema?
Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.
What is real time data-warehousing?
Real-time data warehousing is a combination of two things: 1) real-time activity and 2) data warehousing. Real-time activity is activity that is happening right now. The activity could be anything such as the sale of widgets. Once the activity is complete, there is data about it. Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly. In other words, real-time data warehousing is a framework for deriving information from data as the data becomes available.

What are slowly changing dimensions?
SCD stands for Slowly changing dimensions. Slowly changing dimensions are of three types SCD1: only maintained updated values. Ex: a customer address modified we update existing record with new address. SCD2: maintaining historical information and current information by using A) Effective DateB) VersionsC) Flagsor combination of thesescd3: by adding new columns to target table we maintain historical information and current information

What are Semi-additive and factless facts and in which scenario will you use such kinds of fact tables?
Snapshot facts are semi-additive, while we maintain aggregated facts we go for semi-additive. EX: Average daily balance A fact table without numeric fact columns is called factless fact table. Ex: Promotion Facts While maintain the promotion values of the transaction (ex: product samples) because this table doesn’t contain any measures.

Differences between star and snowflake schemas?
Star schema - all dimensions will be linked directly with a fat table.Snow schema - dimensions maybe interlinked or may have one-to-many relationship with other tables.

What is a Star Schema?
Star schema is a type of organising the tables such that we can retrieve the result from the database easily and fastly in the warehouse environment.Usually a star schema consists of one or more dimension tables around a fact table which looks like a star,so that it got its name.

What is a general purpose scheduling tool?
The basic purpose of the scheduling tool in a DW Application is to stream line the flow of data from Source To Target at specific time or based on some condition.

What is ER Diagram?
The Entity-Relationship (ER) model was originally proposed by Peter in 1976 [Chen76] as a way to unify the network and relational database views. Simply stated the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. Since Chen wrote his paper the model has been extended and today it is commonly used for database design For the database designer, the utility of the ER model is: it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. it is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software.

Which columns go to the fact table and which columns go the dimension table?
The Primary Key columns of the Tables(Entities) go to the Dimension Tables as Foreign Keys. The Primary Key columns of the Dimension Tables go to the Fact Tables as Foreign Keys.

What are modeling tools available in the Market?
here are a number of data modeling tools Tool Name Company NameErwin Computer AssociatesEmbarcadero Embarcadero TechnologiesRational Rose IBM CorporationPower Designer Sybase CorporationOracle Designer Oracle Corporation

Name some of modeling tools available in the Market?
These tools are used for Data/dimension modeling 1. Oracle Designer2. ERWin (Entity Relationship for windows)3. Informatica (Cubes/Dimensions)4. Embarcadero5. Power Designer Sybase

How do you load the time dimension?
Time dimensions are usually loaded by a program that loops through all possible dates that may appear in the data. It is not unusual for 100 years to be represented in a time dimension, with one row per day.
Explain the advanatages of RAID 1, 1/0, and 5. What type of RAID setup would you put your TX logs.
Transaction logs write sequentially and don't need to be read at all. The ideal is to have each on RAID 1/0 because it has much better write performance than RAID 5. RAID 1 is also better for TX logs and costs less than 1/0 to implement. It has a tad less reliability and performance is a little worse generally speaking. RAID 5 is best for data generally because of cost and the fact it provides great read capability.

What are the vaious ETL tools in the Market?
Various ETL tools used in market are: 1. Informatica2. Data Stage3. MS-SQL DTS(Integrated Services 2005)4. Abinitio5. SQL Loader6. Sunopsis7. Oracle Warehouse Bulider8. Data Junction

What is VLDB?
Answer 1:VLDB stands for Very Large DataBase. It is an environment or storage space managed by a relational database management system (RDBMS) consisting of vast quantities of information. Answer 2:VLDB doesn’t refer to size of database or vast amount of information stored. It refers to the window of opportunity to take back up the database. Window of opportunity refers to the time of interval and if the DBA was unable to take back up in the specified time then the database was considered as VLDB.

What are Data Marts ?
A data mart is a focused subset of a data warehouse that deals with a single area(like different department) of data and is organized for quick analysis

What are the steps to build the datawarehouse ?
Gathering bussiness requiremntsIdentifying SourcesIdentifying FactsDefining DimensionsDefine AttribuesRedefine Dimensions & AttributesOrganise Attribute Hierarchy & Define RelationshipAssign Unique IdentifiersAdditional convetions:Cardinality/Adding ratios

What is Difference between E-R Modeling and Dimentional Modeling.?
Basic diff is E-R modeling will have logical and physical model. Dimensional model will have only physical model. E-R modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

Why fact table is in normal form?
Basically the fact table consists of the Index keys of the dimension/ook up tables and the measures. so when ever we have the keys in a table .that itself implies that the table is in the normal form.

What are the advantages data mining over traditional approaches?
Data Mining is used for the estimation of future. For example, if we take a company/business organization, by using the concept of Data Mining, we can predict the future of business interms of Revenue (or) Employees (or) Cutomers (or) Orders etc. Traditional approches use simple algorithms for estimating the future. But, it does not give accurate results when compared to Data Mining.

What are the vaious ETL tools in the Market?
Various ETL tools used in market are: InformaticaData StageOracle Warehouse BuliderAb InitioData Junction

What is a CUBE in datawarehousing concept?
Cubes are logical representation of multidimensional data.The edge of the cube contains dimension members and the body of the cube contains data values.

What is data validation strategies for data mart validation after loading process ?
Data validation is to make sure that the loaded data is accurate and meets the business requriments. Strategies are different methods followed to meet the validation requriments

what is the datatype of the surrgate key ?
Datatype of the surrgate key is either inteeger or numaric or number

What is degenerate dimension table?
Degenerate Dimensions : If a table contains the values, which r neither dimesion nor measures is called degenerate dimensions.Ex : invoice id,empno

What is Dimensional Modelling?
Dimensional Modelling is a design concept used by many data warehouse desginers to build thier datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measuremnets ie, the dimensions on which the facts are calculated.

What are the methodologies of Data Warehousing.?
Every company has methodology of their own. But to name a few SDLC Methodology, AIM methodology are stardadly used. Other methodologies are AMM, World class methodology and many more.

What is a linked cube?
Linked cube in which a sub-set of the data can be analysed into great detail. The linking ensures that the data in the cubes remain consistent.
What is the main difference between Inmon and Kimball philosophies of data warehousing?
Both differed in the concept of building teh datawarehosue.. According to Kimball ... Kimball views data warehousing as a constituency of Data marts. Data marts are focused on delivering business objectives for departments in the organization. And the data warehouse is a conformed dimension of the data marts. Hence a unified view of the enterprise can be obtain from the dimension modeling on a local departmental level. Inmon beliefs in creating a data warehouse on a subject-by-subject area basis. Hence the development of the data warehouse can start with data from the online store. Other subject areas can be added to the data warehouse as their needs arise. Point-of-sale (POS) data can be added later if management decides it is necessary. i.e.,Kimball--First DataMarts--Combined way ---Datawarehouse Inmon---First Datawarehouse--Later----Datamarts

What is Data warehosuing Hierarchy?
HierarchiesHierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure. Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies--one for product categories and one for product suppliers. Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse. When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization. Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly. LevelsA level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies. Level RelationshipsLevel relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy. Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.

What is the main differnce between schema in RDBMS and schemas in DataWarehouse....?
RDBMS Schema* Used for OLTP systems* Traditional and old schema* Normalized* Difficult to understand and navigate* Cannot solve extract and complex problems* Poorly modelledDWH Schema* Used for OLAP systems* New generation schema* De Normalized* Easy to understand and navigate* Extract and complex problems can be easily solved* Very good model
What is hybrid slowly changing dimension?
Hybrid SCDs are combination of both SCD 1 and SCD 2. It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care. For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.

What are the different architecture of datawarehouse?
There are two main things 1. Top down - (bill Inmon)2.Bottom up - (Ralph kimbol)

1.what is incremintal loading? 2.what is batch processing? 3.what is crass reference table? 4.what is aggregate fact table?
Incremental loading means loading the ongoing changes in the OLTP. Aggregate table contains the [measure] values ,aggregated /grouped/summed up to some level of hirarchy.

what is junk dimension? what is the difference between junk dimension and degenerated dimension?
Junk dimension: Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension. Degenerate Dimension: Keeping the control information on Fact table ex: Consider a Dimension table with fields like order number and order line number and have 1:1 relationship with Fact table, In this case this dimension is removed and the order information will be directly stored in a Fact table inorder eliminate unneccessary joins while retrieving order information..

What are the possible data marts in Retail sales.?
Product information,sales information

What is the definition of normalized and denormalized view and what are the differences between them?
Normalization is the process of removing redundancies. Denormalization is the process of allowing redundancies.

Data Warehousing Interview Questions and Answers
Part: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
(Continued from previous part...)
Data Warehousing Basics Interview Questions and Answers (5)

What is meant by metadata in context of a Datawarehouse and how it is important?
Meta data is the data about data; Business Analyst or data modeler usually capture information about data - the source (where and how the data is originated), nature of data (char, varchar, nullable, existance, valid values etc) and behavior of data (how it is modified / derived and the life cycle ) in data dictionary a.k.a metadata. Metadata is also presented at the Datamart level, subsets, fact and dimensions, ODS etc. For a DW user, metadata provides vital information for analysis / DSS.

Differences between star and snowflake schemas?
Star schemaA single fact table with N number of Dimension Snowflake schemaAny dimensions with extended dimensions are know as snowflake schema

Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?
Star schema contains the dimesion tables mapped around one or more fact tables. It is a denormalised model. No need to use complicated joins. Queries results fastly. Snowflake schema It is the normalised form of Star schema. contains indepth joins ,bcas the tbales r splitted in to many pieces.We can easily do modification directly in the tables. We hav to use comlicated joins ,since we hav more tables . There will be some delay in processing the Query .

What is VLDB?
The perception of what constitutes a VLDB continues to grow. A one terabyte database would normally be considered to be a VLDB.

What's the data types present in bo?n what happens if we implement view in the designer n report
Three different data types: Dimensions,Measure and Detail. View is nothing but an alias and it can be used to resolve the loops in the universe.

can a dimension table contains numeric values?
Yes.But those datatype will be char (only the values can numeric/char)

What is the difference between view and materialized view?
View - store the SQL statement in the database and let you use it as a table. Everytime you access the view, the SQL statement executes. Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that everytime you run the query, the stored result set is used. Pros include quick query results.
What is surrogate key ? where we use it expalin with examples
surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key. It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult. Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but ,not only can these change, indexing on a numerical value is probably better and you could consider creating a surrogate key called, say, AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME. 2. Adapted from response by Vincent on Thursday, March 13, 2003 Another benefit you can get from surrogate keys (SID) is : Tracking the SCD - Slowly Changing Dimension. Let me give you a simple, classical example: On the 1st of January 2002, Employee 'E1' belongs to Business Unit 'BU1' (that's what would be in your Employee Dimension). This employee has a turnover allocated to him on the Business Unit 'BU1' But on the 2nd of June the Employee 'E1' is muted from Business Unit 'BU1' to Business Unit 'BU2.' All the new turnover have to belong to the new Business Unit 'BU2' but the old one should Belong to the Business Unit 'BU1.' If you used the natural business key 'E1' for your employee within your datawarehouse everything would be allocated to Business Unit 'BU2' even what actualy belongs to 'BU1.' If you use surrogate keys, you could create on the 2nd of June a new record for the Employee 'E1' in your Employee Dimension with a new surrogate key. This way, in your fact table, you have your old data (before 2nd of June) with the SID of the Employee 'E1' + 'BU1.' All new data (after 2nd of June) would take the SID of the employee 'E1' + 'BU2.' You could consider Slowly Changing Dimension as an enlargement of your natural key: natural key of the Employee was Employee Code 'E1' but for you it becomesEmployee Code + Business Unit - 'E1' + 'BU1' or 'E1' + 'BU2.' But the difference with the natural key enlargement process, is that you might not have all part of your new key within your fact table, so you might not be able to do the join on the new enlarge key -> so you need another id.

What is ER Diagram?
The Entity-Relationship (ER) model was originally proposed by Peter in 1976 [Chen76] as a way to unify the network and relational database views. Simply stated the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. Since Chen wrote his paper the model has been extended and today it is commonly used for database design For the database designer, the utility of the ER model is: it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. it is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software.

What is aggregate table and aggregate fact table ... any examples of both?
Aggregate tableÂ contains summarised data. The materialized view are aggregated tables. for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the dateÂ vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.

What is active data warehousing?
An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively. Active data warehousing is all about integrating advanced decision support with day-to-day-even minute-to-minute-decision making in a way that increases quality of those customer touches which encourages customer loyalty and thus secure an organization's bottom line. The marketplace is coming of age as we progress from first-generation "passive" decision-support systems to current- and next-generation "active" data warehouse implementations

Why do we override the execute method is struts? Plz give me the details?
As part of Struts FrameWork we can decvelop the Action Servlet,ActionForm servlets(here ActionServlet means which class extends the Action class is called ActionServlet and ActionFome means which calss extends the ActionForm calss is called the Action Form servlet)and other servlets classes. In case of ActionForm class we can develop the validate().this method will return the ActionErrors object.In this method we can write the validation code.If this method return null or ActionErrors with size=0,the webcontainer will call the execute() as part of the Action class.if it returns size > 0 it willnot be call the execute().it will execute the jsp,servlet or html file as value for the input attribute as part of the attribute in struts-config.xml file.

What is the difference between Datawarehousing and BusinessIntelligence?
Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management, backup/recovery planning, etc. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions, etc. Typically, the term “business intelligence” is used to encompass OLAP, data visualization, data mining and query/reporting tools.Think of the data warehouse as the back office and business intelligence as the entire business including the back office. The business needs the back office on which to function, but the back office without a business to support, makes no sense.
What is the difference between OLAP and datawarehosue?
Datawarehouse is the place where the data is stored for analyzing where as OLAP is the process of analyzing the data,managing aggregations, partitioning information into cubes for indepth visualization.

What is fact less fact table? where you have used it in your project?
Factless table means only the key available in the Fact there is no mesures availalabl

Why Denormalization is promoted in Universe Designing?
In a relational data model, for normalization purposes, some lookup tables are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called DIMENSION table for performance and slicing data.Due to this merging of tables into one large Dimension table, it comes out of complex intermediate joins. Dimension tables are directly joined to Fact tables.Though, redundancy of data occurs in DIMENSION table, size of DIMENSION table is 15% only when compared to FACT table. So only Denormalization is promoted in Universe Desinging.

What is the difference between ODS and OLTP?
ODS:- It is nothing but a collection of tables created in the Datawarehouse that maintains only current data where as OLTP maintains the data only for transactions, these are designed for recording daily operations and transactions of a business

What is the difference between datawarehouse and BI?
Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.

Is OLAP databases are called decision support system ??? true/false?
True

explain in detail about type 1, type 2(SCD), type 3 ?
Type-1 Most Recent Value Type-2(full History) i) Version Number ii) Flag iii) Date Type-3 Current and one Perivies value

What is snapshot?
You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data. However, you must reconnect to the catalog if you want to refresh the data.

What is the difference between datawarehouse and BI?
Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.

What are non-additive facts in detail?
A fact may be measure, metric or a dollar value. Measure and metric are non additive facts. Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount. A non additive fact, for eg measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'

What is a correlated sub-query? How can these queries be useful?
The more seasoned developer will be able to accurately describe this type of query. A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article.

Sql Server 2008 & MSBI 2008 - Features &Enhancements

2010-07-26T09:07:00.001-07:00

Hi,
In Sql server 2008 and especially in BI enhancements are beautiful and nice.
They mainly focused on robust, security and Performance.
Nice articles are here.
http://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx
http://www.mssqltips.com/tip.asp?tip=1313
Regards,
Ravindra

SSAS-Videos

2010-07-26T08:32:00.001-07:00

Hi Guys,
Here are the video url’s from Microsoft.
· SSAS-info Webcasts
· Craig Utley's videos for SSAS Beginners - registration is free
· Creating an Analysis Services Project
· Defining and Deploying a Cube
· Modifying Measures, Attributes and Dimensions
· Implementing Best Practices for Large Analysis Services Databases – Part I
· Create and Publish a Data Cube for SQL Server Analysis Services 2008
· Viewing Analysis Services Data in Excel 2007 – SQL School Video
~Ravindra

SSRS Issue with IE8-Workaround

2010-07-26T08:31:00.002-07:00

Hi All,
We faced a issue SSRS report with ie8 like, “page number footer missing”.
Here is the nice blog and corresponding -workaround links.
http://blog.mgallen.com/?p=338
I hope it might helps you.
~Ravindra

SSRS Issue with IE8-Workaround

2010-07-26T08:31:00.001-07:00

SSRS Issue with IE8-Workaround

2010-07-26T08:31:00.000-07:00

SQL Server Data Warehouse basics

2010-07-20T01:50:00.000-07:00

Hi Guys,
Before going to start any datawarehouse project, every developer should know the below basic concepts on datawarehouse with Sql Server.
It’s really nice article from Robert Scheldon.
Pls go thru this.the below topics are covered in the post.
· The Data Warehouse
· Data Warehouse vs. Data Mart
· Relational Database vs. Dimensional Database
· Dimensional Database vs. Multidimensional Database
· The Data Model
· The Star Schema.
· The Snowflake Schema
· The Star Schema vs. the Snowflake Schema.
· The Fact Table
· The Dimension
· The Surrogate Key
· The Date Dimension
· The Slowly Changing Dimension.
· The Data
~Ravindra
http://www.simple-talk.com/sql/learn-sql-server/sql-server-data-warehouse-cribsheet/

Basic Steps for Implementing an Analysis Services Database

2010-07-20T01:49:00.000-07:00

Hi Friendz,
To achieve OLAP requirement, we have multiple tools in the competitive world, MS-OLAP tool is one of the best one.
Here I am sharing some details from Robert Scheldon blog about to know basic stuff on how to implement OLAP DB by using SSAS.
Nice one.
~Ravindra
http://www.simple-talk.com/sql/reporting-services/five-basic-steps-for-implementing-an-analysis-services-database/

I Passed 70-448 Exam

2010-07-02T05:39:00.000-07:00

Hi Friendz,
I am happy to share the news with you about-i passed the 'MCTS 70-448' exam.
Here are the details about the exam.
http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-448&locale=en-us
Regards,
Ravindra

SSAS - slowly changing dimension type 2 in ssas

2010-05-31T07:42:00.000-07:00

This scenario is from Erik like –
I have an SCD Type 2 dimension on customer demographics. I am in the process of creating a dimension using SQL Server Analysis Services, including hierarchies on for example geographic data and marketing hierarchy tiers. For example, I have the following hierarchy on geographic data:Continent --> Country --> State --> City --> Account (business key) --> Account Dimension (primary key)The deepest tier, the account dimension, is completely irrelevant for the end user, therefore when an end user browses the cube, the last tier should be completely hidden. Also, when some data of the account changes, I'll have an end date and new start date when it's an SCD Type 2 change. If I set the Account Dimension key in the hierarchy on Account Dimension and Account, I'll have the account listed twice when I browse the cube (I can't only set the account dimension key to the account dimension key field, since I'll have duplicate keys in the hierarchy when an account for example changes cities).
Useful links:
http://pedrocgd.blogspot.com/2007/10/bi-casestudy-slowly-changing-dimension.html
http://blogs.microsoft.co.il/blogs/barbaro/archive/2007/09/08/Slowly-Changing-Dimension-type-2-in-SSAS.aspx

How to call a WCF service using SSIS

2010-05-01T21:41:00.000-07:00

Hi Guys-
WCF posts are really interesting.
How to use WCF ( Windows Communication Foundation ) In 2005
http://www.technade.com/2009/08/how-to-call-wcf-service-using-web.html
How to use WCF ( Windows Communication Foundation ) In 2008
http://consultingblogs.emc.com/jamiethomson/archive/2007/06/19/SSIS_3A00_--Consuming-web-services-in-SSIS-2008.aspx
njoy;-)
-Rav’s

How to install Sql Server 2008

2010-04-29T05:32:00.000-07:00

http://www.sqlserverclub.com/pdfs/how-to-install-sql-server-2008-a-step-by-step-guide.pdf

Sql Server 2005 - How to Debug SP

2010-04-04T11:01:00.000-07:00

Hi,
we have different mechanism to debug a SP.If i have INPUT and OUTPUT parameters,we can execute SP by using the below method.
============
In order to illustrate the debugger features, create the stored procedure listed below.
CREATE procedure DebugA( @IntIn int, @IntOut int OUTPUT)as
Set @IntOut = @IntIn + 10
Return
This is a simple stored procedure that takes a number and adds 10 to that number and returns the new number in an output variable. You can execute the stored procedure with the following code in Query Analyzer:
Declare @NewInt intexec DebugA 5,@NewInt OUTPUTSelect @NewInt
Given a value of 5, adding 10 will return a value of 15.
================
-- Debug SP in Sql Server 2000
http://www.15seconds.com/Issue/050106.htm
-- Debug SP in Sql Server 2005
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/435c0c43-571a-442d-9fb9-f3f495b1f2ed
-- Debug SP in Sql Server 2008
http://msdn.microsoft.com/en-us/library/ms174763.aspx

SSMS-Identify Backend Process in Sql Server

2010-03-04T01:57:00.000-08:00

Hi,

While many process are running in sql server,if we want to see what are the processes are running on this server and what is the CPU utilization time,status and READ and WRITES full info we can see using below query.
Try to run it.
Rav's
Query:

------

SELECT r.session_id, s.HOST_NAME, wait_type, r.wait_resource,SUBSTRING(qt.text,(r.statement_start_offset/2) +1,(CASE WHEN r.statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2ELSE r.statement_end_offsetEND -r.statement_start_offset)/2)AS stmt_executing,r.blocking_session_id, s.PROGRAM_NAME,s.host_process_id, r.status, r.wait_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads, r.plan_handleFROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions sWHERE r.session_id > 50 and r.session_id=s.session_idORDER BY r.session_id, s.host_name, s.program_name, r.status

SSRS-Basic Understanding about Reports

2010-02-26T06:15:00.000-08:00

Hi,
Rajoo Jha explained clearly about SSRS reports and types,Design techniques in his blogs.
Really awesome.
If you want to know full functionality about SSRS,You must Read it.
http://ssrstips.blogspot.com/search?updated-max=2009-07-10T01%3A04%3A00-07%3A00&max-results=7

SSRS-Developing Report Navigation and Drilldown

2010-02-25T04:45:00.000-08:00

Hi,
Here is the link to explain about Report navigation,drilldown,drill through options in SSRS report.
Really good stuff!!!!!
Cheers,
Rav's
http://msdn.microsoft.com/en-us/library/aa964132(SQL.90).aspx

BIDS and TFS

2010-02-17T02:51:00.000-08:00

BIDS-TFS is a concept of configuration management.We have to maintain Source control to maintenance the code very clearly.
See the below link:
http://stevemchugh.blogspot.com/2007/10/bids-and-tfs.html

I Am Here

2010-02-17T02:22:00.000-08:00

I would like to share my pic is here.
Thanks&Regards,

Rav's

SSRS-Examples

2010-02-10T05:40:00.000-08:00

Hi All,
Here is the link for SSRS report details with examples.
Good Stuff.
Thanks Chris!!!!!!
Cheers,
Rav’s
LINK:-
http://blogs.msdn.com/ChrisHays/

All BI Blogs Details

2010-02-09T22:24:00.000-08:00

Fellow SQLTeam.com Bloggers
· Tara Kizer (rss)
· Ajarn Mark (rss)
· Bill Graziano (rss)
· Brett Kaiser (rss)
· Chris Miller (rss)
· Damian Maclennan (rss)
· Dan Guzman (rss)
· David M (rss)
· Davide Mauri (rss)
· Denise M (rss)
· Derrick Leggett (rss)
· Dinakar Nethi (rss)
· Geoff N. Hiten (rss)
· James Nix (rss)
· Jeff Maass (rss)
· Jeff Smith (rss)
· Jen (rss)
· Joe Webb (rss)
· Jon Hermiz (rss)
· Mladen Prajdić (rss)
· Peter Larsson (rss)
· Phil Streiff (rss)
· Randy Aldrich Paulo (rss)
· Rob Volk (rss)
· Travis Laborde (rss)
Hardware Blogs
Microsoft Blog Roll
· Ben Tamblyn (rss)
· DataAllegro Blog (rss)
· Excel Team Blog (rss)
· Howard Morgenstern's Selling MSFTBI Blog (rss)
· MCP Communications (rss)
· Microsoft's Official BI Blog (rss)
· PerformancePoint Team (rss)
· Roger Wolter (rss)
· SQL BI Product Team Recruiting (rss)
· SQL CAT Team (rss)
· SQL Manageability Team (rss)
· SQL Server Policy Based Mgmt (rss)
· SQL Server Reporting Services Team (rss)
· Technet Plus Blog
· The Data Platform Insider (rss)
SQL Server BI Blog Roll
· Chris Webb (rss)
· Dan Meyers (rss)
· Jess Orosz SSAS (rss)
· Mosha Pasumansky (rss)
· Patrick Husting (rss)
· Teo Lachev (rss)
· Vidas Matelis SSAS (rss)
SQL Server Blog Roll
· Jamie Thomson SSIS
· Jason Massie (rss)
· The Scary DBA (rss)
· Vinod Kumar (rss)
Post Categories
· Business Intelligence Nuggets (rss)
· General (rss)
· SharePoint for BI (rss)
· SQL Server 2008 (rss)
· SQL Server 2008 R2 (including Gemini) (rss)

SQL SERVER 2005-IMP QUERIES

2010-02-09T08:15:00.000-08:00

Update a table
=============
update cd
set cd.PrimaryContainerVolumeQty = pp.vol_per_physical_case,
cd.UnitsPerCaseQty = 1,
cd.UnitOfMeasure = 4
from #v_Source_ConversionData cd
inner join #OutletProducts pp on
cd.SalesProductID = pp.SalesProductID
and pp.VarietyPackIndicator = 'Y'
and pp.vol_per_physical_case != 0;
Duplicate Query
=============
SELECT DISTINCT l0_id ,COUNT(*) FROM
GROUP BY l0_id HAVING COUNT(l0_id) > 1

CASE Example
=============
select
case when manual_close = 'true' then 1 else
case when auto_close_without_est = 'false' then 2 else
case when auto_close_without_est = 'true' then 3 else 0 End
End
End
From
t_division

Usage Of Cursor
=============
--Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Nth Highest Salary
=============
SELECT TOP 1 division_id
FROM (
SELECT DISTINCT TOP 3 division_id
FROM t_division
ORDER BY division_id DESC) a
ORDER BY division_id

Date Functions
=============

1. select convert(varchar,datepart(yyyy,getdate())) + '-' + convert(varchar,datepart(mm,getdate())) + '-' + convert(varchar,datepart(dd,getdate()))
2. select datepart(yyyy,getdate()) as CYear,datepart(mm,getdate()) as CMonth,datepart(dd,getdate()) as CDate

3. select (MONTH(cast('Jan 01 2004' as datetime))) as YR
4. select (YEAR(cast('Jan 01 2004' as datetime))) as YR
5. select (DAY(cast('Jan 01 2004' as datetime))) as YR
Usage Of ROWNUMBER
=============

SELECT * FROM
(
select row_number() over( Partition By l0_id ORDER BY l0_id,row_ID) ROWNUMBER, l0_id,row_ID FROM local_hier.t_local_hier_edit
WHERE division_id=4 AND local_hier_id = 3 AND l0_id in ('-1','5415')
) A
where rownumber = 1

Data validation between Two tables with More JOINS
=============
/*Source */
select month,code, description,sum(sales) SourceSales
into #tempa
from sourcesales with (nolock) inner join
calendar_445 with (nolock)
sourcesales. daycode = calendar_445.day_key inner join
Sourcebot with (nolock)
on sourcesales.sourcebotcode= sourcebots.sourcebotcode
where month >=@monthid
and month <=@monthid
group by month,code, description
order by month,code, description

/*Olap*/

select month,code, description,
sum(sales) as PS ,sum(ucases) as US
into #tempb
from o_sales with (nolock) inner join
calendar_445 with (nolock)
on o_sales.dayid = calendar_445.day_id inner join
sourcebot with (nolock)
where month >=@monthid
and month <=@monthid
group by month,code, description
order by month,code, description
/*Temp Table Operation*/
select b.Month_id, b.sourcebotcd, b.description, b.PC, b.UC, a.SourceSales
,c.MonthlyPC, c.MonthlyUC
from #tempb b
join #tempc c
on b.Month_id= c.monthid
and b.sourcebotcd = c.sourcebotcd
left join #tempa a
on b.Month_id = a.Month_id
and b.sourcebotcd = a.sourcebotcd
order by 1,2

/*

Sql Server 2005-Basic SQL Stuff

2010-02-09T07:14:00.000-08:00

Hi All,
Here you can find good example of sql statements for Cursor,Triggeres,Indexes,Joins,UNIONS,Highest salary ,Having,Group By Order By,COALESCE,NOT IN,Time ‘N’ Date Format Func,TEMP table Usage,CASE statements,Split Columns,Duplicate identification.
Really Very good Stuff.
Please go through thi.
Cheers,
Rav’s
http://sqlservercodebook.blogspot.com/2008_03_01_archive.html

Sql Server 2005-Types of Joins

2010-02-09T02:28:00.000-08:00

First of all take a look on the names of different types of joins in SQL.
Here is the link to get more details.
http://programming.top54u.com/post/Joins-In-Sql-Server-2005.aspx

1. Inner Join
2. Outer Join
o Left Outer Join
o Right Outer Join
o Full Outer Join
3. Cross Join

Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.

1. Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins. Examples:SQL Inner Join Examples SQL Inner Natural Join Examples SQL Inner Equi Join Examples
2. Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.
o Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple. Examples:SQL Left Outer Join Examples
o Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table. Examples:SQL Right Outer Join Examples
o Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table. Examples:SQL Full Outer Join Examples
3. Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table. Examples:SQL Cross Join Cartesian product Examples

Other SQL Join Examples:
Joins In Sql Server 2005
Joins using table aliases in SQL Server 2005
Self Join In Sql Server 2000 2005
· Related Articles
· • SQL Cross Join Cartesian product Examples
· • SQL Full Outer Join Examples
· • SQL Right Outer Join Examples
· • SQL Left Outer Join Examples
· • SQL Inner Join Examples
· Inside SQL Server 2005 SQL Tutorials
Here is the link to get more details.
http://programming.top54u.com/post/Joins-In-Sql-Server-2005.aspx

Microsoft Sql Server 2005 Interview Questions and Answers

2010-01-20T03:20:00.000-08:00

1. What does integration of .NET Framework mean for SQL Server 2005?
This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in .net compatible language.
2. What is SSIS?
According to Microsoft SQL Server Integration Services, “(SSIS) is an effective set of tools for both the traditional demands of ETL operations, as well as for the evolving needs of general purpose data integration.” In short, it is the next version of DTS (Data Transformation Services). ETL stands for Extract, Transform and Loading. In short it is a data migration tool that is flexible, fast, and has scalable architecture that enables effective data integration in current business environments.
3. What is MARS?
In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Multiple Active Result Sets (MARS) is the ability to have more than one pending request under a given SQL Server connection. MARS is a programming model enhancement that allows multiple requests to interleave in the server. We need to note that it is not a parallel execution in the server. However, it may benefit us with some performance benefits if used correctly. By default, this feature is not set in SQL Server 2005.
4. What are the Security Enhancements in SQL Server 2005?
SQL Server 2005 enables administrators to manage permissions at a granular level.
· In the new SQL Server 2005, we can specify a context under which statements in a module can execute.
· SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.
· Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain.
· SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client-server communications are encrypted.
5. What is new with the Reporting services in SQL server 2005?
SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.
· Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity
· Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio
· Report Builder – A new reporting tool that enables business users to create their own reports
6. What is OLAP?
Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.
7. What is Data Mining?
According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant ways. Data Mining is a platform for developing intelligent applications, not a stand-alone application. You can build custom applications that are intelligent because the data mining models are easily accessible to the outside world. Further, the model is extensible so that third parties can add custom algorithms to support particular mining needs.
8. What is new with the Analysis Services (SSAS) in SQL Server 2005?
SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.
· Supports up to 16 instances of Analysis Services Service.
· As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.
· Uses the Proactive caching.
9. What is Information Schema in SQL Sever 2005?
Information Schema is the part of the SQL- 92 standard which exposes the metadata of the database. In SQL server, a set of views are created in each of the databases which exposes the metadata of the database. The information schema is kept in a separate schema – information schema – which exists in all databases, but which is not included in the search path by default. For more information regarding Information schema please read this article.
10. What is Full Text Search? How does it get implemented in SQL server 2005?
Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.
11. What is integration of Microsoft Office System mean?
The integration with Microsoft Office system means the following.
· Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind the scenes to perform powerful end user analysis on spreadsheet data.
· Data Mining Client for Excel: Offers a full data mining model development lifecycle directly within Excel 2007.
· Data Mining Templates for Visio: Enables powerful rendering and sharing of mining models as annotatable Visio 2007 drawings.
12. What is the support of Web Services in SQL Server 2005?
With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. With this new feature we can now connect to SQL Server not only with TDS- Tabular data stream (a binary protocol for connecting to SQL Server 2005) but also over SOAP/ HTTP.
13. What is OLTP?
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.
14. What is Snapshot in SQL Server 2005?
A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation.
15. What is snapshot isolation in SQL Server 2005?
SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.
16. What is Database Partitioning in SQL Server 2005?
SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.
17. What is SQL Server Agent?
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. We generally schedule the backups on the production databases using the SQL server agent. In SQL Server 2005 we have roles created for using SQL Server agents.
· SQLAgentUserRole
· SQLAgentReaderRole
· SQLAgentOperatorRole
SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service.
18. What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?
“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.
· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.
· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.
19. What are Business Logic Handlers?
Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic handler can perform one of the following three actions.
· Reject Data
· Accept Data
· Apply Custom Data
20. What are different variants of SQL Server 2005?
There are different variants of SQL Server 2005 commercially available.
· Express – Free and only for one user
· Enterprise – 5 users apart from server
· Workgroup – 10 users apart from server
· Standard – 25 users apart from server
21. What are Various Service packs available for SQL Server 2005?
As of now there are two service packs available for the SQL Server 2005.
· Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data Programmability, SSIS, and reporting services.
· Service Pack 2 – Unlike Service Pack 2, this service pack enables SQL Server 2005 customers to take advantage of the enhancements within Windows Vista and the 2007 Office system.
22. What are the New Data types introduced in SQL Server 2005?
SQL Server 2005 has added some new data types to its existing data types.
XML Data type
· VARCHAR (MAX)
· NVARCHAR (MAX)
· VARBINARY (MAX)
As we can see, the new term MAX has been introduced in SQL Server 2005. This new specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.
23. Does SQL Server 2005 support SMTP?
SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an e-mail was not supported in the previous versions of SQL Server.
24. What is SQL Management Object is SQL Server 2005?
These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a database, calling methods of the database that returns a table, using transactions, transferring data, scheduling administrative tasks, etc. The best part about SMO is that most of it can also be used with SQL server 2000.
25. What is SQL Service Broker in SQL Server 2005?
SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages are called conversations.
SSIS ‘N’ SSRS Interview questions....with Answers

26. What is the control flowANS: In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.27. what is a data flowANS: A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.28. how do you do error handling in SSISANS: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.Errors typically fall into one the following categories:-Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.-Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.-Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.29. How do you do logging in ssis.ANS: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.To enable logging in a package1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.2. On the SSIS menu, click Logging.3. Select a log provider in the Provider type list, and then click Add.30.what are variables and what is variable scope ?ANS: Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.Scope : A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.31. True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT command against the relational engine. It commits all of the data to the database.ANS: False. SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.32. True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.ANS: False, but a custom solution can be built to meet these needs.33. What is a breakpoint in SSIS? How is it setup? How do you disable it?ANS: A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.10 unique conditions exist for each breakpoint.Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.34. How do you eliminate quotes from being uploaded from a flat file to SQL Server? ANS: In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.Additional information: How to strip out double quotes from an import file in SQL Server Integration Services35. Can you explain how to setup a checkpoint file in SSIS?ANS: The following items need to be configured on the properties tab for SSIS package:CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.CheckpointUsage - Determines if/how checkpoints are used. Choose from these options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.SaveCheckpoints - Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.36. How do you upgrade an SSIS Package? ANS: Depending on the complexity of the package, one or two techniques are typically used:Recode the package based on the functionality in SQL Server DTSUse the Migrate DTS 2000 Package wizard in BIDS then recode any portion of the package that is not accurate.

SSAS-MDX Interview questions - Time based function-II

2010-01-20T03:19:00.001-08:00

Some of the important time related functions are available in this crb sheet. Query refers to [Adventure Works] cube that comes along with SSAS installation.
How do you get Last month in the time dimensionSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary]WHERE ([Measures].[Sales Amount]);
I Need an MDX statement to show the first day of the last month in the cubeSELECT OpeningPeriod([Date].[Calendar].[Date], ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary]
I Need an MDX statement to get the last Month loaded into a cubeSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary];
How do you write MDX query that uses execution date/time as a parameter?
SELECT {[Measures].[Internet Order Count]} ON 0, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]")} ON 1FROM [Direct Sales];
Need MDX Query to get latest months and previous years same months data
SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember), ParallelPeriod([Date].[Calendar].[Calendar Year] , 1, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) } ON 0FROM [Sales Summary];
How to create calculated member for AVG sales over last 3 years based on NOW()?
CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS Avg( {ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")): StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")}, [Measures].[Sales Qty]) ;
How can I get Last (Previous) Year to Date (YTD) values?
WITH MEMBER [Measures].[Current YTD] ASSUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year], 1 , [Date].[Calendar].CurrentMember)), [Measures].[Internet Order Quantity] )SELECT {[Measures].[Current YTD], [Measures].[Last YTD] } ON 0FROM [Adventure Works]WHERE ([Date].[Calendar].[Date].[March 22, 2004])
How do you calculate monthly average of a year?
WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), [Measures].[Internet Order Count] )SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
Need a MDX query that returns list of months from start of year up to specified month.
SELECT YTD([Date].[Calendar].[Month].&[2003]&[8])ON 0FROM [Sales Summary];
How in the report can I order date dimension members in descending order?
SELECT {[Measures].[Reseller Order Quantity]} ON 0 , ORDER(Tail([Date].[Calendar].[Calendar Year].Members, 3), [Date].[Calendar].CurrentMember.Member_Key, DESC ) ON 1 FROM [Adventure Works]
I Need an MDX statement to get the first month of the last year loaded into a cube
SELECT OpeningPeriod([Date].[Calendar].[Month], ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary];
I need an MDX query to show year level data for all years except the last one, and month level data for the last year.
SELECT {NULL:ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember), [Date].[Calendar].[Month]) } ON 0FROM [Sales Summary];
How do I calculate sales for 12 Month to date in MDX?
WITH MEMBER [Measures].[Last 12 Mth Order Count] AS SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12): ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]), [Measures].[Order Count])SELECT [Measures].[Last 12 Mth Order Count] ON 0FROM [Adventure Works]
MDX query to get count of months with sales amount > 0 in defined period
WITH Member [Measures].[Months With Above Zero Sales] AS COUNT(FILTER( DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]) , [Measures].[Sales Amount] > 0 ) )SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0, [Product].[Product Model Lines].[Product Line].Members on 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]);
How do you calculate monthly average of a year including empty months?WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), CoalesceEmpty([Measures].[Internet Order Count], 0))SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

SSAS - MDX Query Interview Questions and Answers-I

2010-01-20T03:17:00.000-08:00

Collection of some of the important type of MDX queries which you should be prepared with. The queries refer to sample SSAS database that comes with SSAS installation. Some of the queries used here link back to examples mentioned in Microsoft msn forums.
Q: How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?A: Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,BOTTOMCOUNT(NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer] ), ( [Measures].[Internet Sales Amount] ) ), 10, ( [Measures].[Internet Sales Amount] )) ON ROWSFROM [Adventure Works]WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;
Q: How in MDX query can I get top 3 sales years based on order quantity?A: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:SELECT {[Measures].[Reseller Order Quantity]} ON 0, [Date].[Calendar].[Calendar Year].Members ON 1FROM [Adventure Works];Same query using TopCount:SELECT{[Measures].[Reseller Order Quantity]} ON 0,TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1FROM [Adventure Works];
Q: How do you extract first tuple from the set?A: Use could usefunction Set.Item(0)Example:SELECT {{[Date].[Calendar].[Calendar Year].Members}.Item(0)}ON 0FROM [Adventure Works]
Q: How do you compare dimension level name to specific value?A: Best way to compare if specific dimension is at certain level is by using 'IS' operator:Example:WITH MEMBER [Measures].[TimeName] ASIIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')SELECT [Measures].[TimeName] ON 0FROM [Sales Summary]WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])
Q: MDX query to get sales by product line for specific period plus number of months with salesA: Function Count(, ExcludeEmpty) counts number of non empty set members. So if we crossjoin Month with measure we will get set that we can use to count members.Query example:WITH Member [Measures].[Months With Non Zero Sales] ASCOUNT(CROSSJOIN([Measures].[Sales Amount], DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month])), ExcludeEmpty)SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0, [Product].[Product Model Lines].[Product Line].Members on 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])
Q: How can I setup default dimension member in Calculation script?A: You can use ALTER CUBE statement. Syntax:ALTER CUBE CurrentCube YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';
Q: I would like to create MDX calculated measure that instead of summing children amounts,uses last child amountA: Normally best way to create this in SSAS 2005 is to create real measure with aggregation function LastChild. If for some reason you still need to create calculated measure, just use fuction .LastChild on current member of Date dimension, and you will allways get value of last period child.Example: We want to see last semester value for year level data. Lets first see what data values are at Calendar Semester level:SELECT {[Measures].[Internet Order Count]} ON 0, DESCENDANTS([Date].[Calendar].[All Periods],[Date].[Calendar].[Calendar Semester] ) ON 1FROM [Adventure Works]
Q: How to calculate YTD monthly average and compare it over several years for the same selected month?A: MDX Query:WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))MEMBER Measures.MyYTDAVG AS Measures.MyYTD / Measures.MyMonthCountSELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,[Date].[Calendar].[Month] On 1FROM [Adventure Works]WHERE ([Date].[Month of Year].&[7])
Q: MDX query to get sales by product line for specific period plus number of months with non empty sales.A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.WITH Member [Measures].[Months With Above Zero Sales] ASCOUNT(DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]) * [Measures].[Sales Amount], ExcludeEmpty)SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0, [Product].[Product Model Lines].[Product Line].Members on 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])
Q: How do I group dimension members dynamically in MDX? Source: MSDN SSAS Newsgroup.A: You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWSFROM [Adventure Works]
Q: How can I compare members from different dimensions that have the same key values?Lets say I have dimensions [Delivery Date] and [Ship Date]. How can I select just records that were Delivered and Shipped the same day?A: You can use FILTER function and compare member keys using Properties function:SELECT {[Measures].[Internet Order Count]} ON 0, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children), [Ship Date].[Date].CurrentMember.Properties('Key')= [Delivery Date].[Date].Properties('Key')) ON 1FROM [Adventure Works]
Q: How can I get attribute key with MDXA:To do so, use Member_Key function:WITHMEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_KeySELECT {Measures.ProductKey} ON axis(0),[Product].[Product Categories].Members on axis(1)FROM [Adventure Works]
Q: How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?A: Here is query exampleWITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),[Measures].[Internet Sales Amount]),Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,[Date].[Calendar].[Month].Members On 1From [Adventure Works]Where ([Date].[Calendar Year].&[2004]);
Q: How to setup calculated measure as default measure for a cube?A: Use ALTER Cube statement on measures dimension. Example:ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]
Q: How can I write MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product CategoryA: Example of such query:WITH SET [FirstSales] ASFILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS* [Date].[Date].[Date].MEMBERS, [Measures].[Internet Sales Amount])AS MYSET,MYSET.CURRENTORDINAL = 1 orNOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))MEMBER [Measures].[CustomersW/FirstSales] ASCOUNT(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),FORMAT_STRING = '#,#'SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} ON 0,[Product].[Product Categories].[Category] ON 1FROM [Adventure Works]WHERE ({[Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003]}, [Customer].[Customer Geography].[City].&[Calgary]&[AB]);
Q: How do you write MDX query that returns measure ratio to parent value?A: Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independant on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:WITH MEMBER [Measures].[Order Count Ratio To Parent] ASIIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0, NULL, [Measures].[Order Count]/([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)), FORMAT_STRING = "Percent"SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]} ON 1FROM [Adventure Works]

SSIS – TOP 10 Best Practices

2010-01-07T06:35:00.001-08:00

Guys!!!
Read It.
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

SSAS - Analysis Services interview questions

2010-01-07T05:36:00.000-08:00

Dave Rodabaugh's shared his interview questions.
Have a look.Really Good.

http://www.ssas-info.com/analysis-services-implementations/69-experience/1352-dave-rodabaughs-analysis-services-interview-questions

Original broken links for reference:
My Analysis Services Interview Questions
Part II of My Analysis Services Interview Questions: Cool Business Problems
Part III of My Analysis Services Interview Questions: Architectural Philosophy
Part IV of My Analysis Services Interview Questions: Technical Features
Part V of My Analysis Services Interview Questions: The Most Common MDX Functions

SSIS - XML Destination using Script Component

2010-01-03T03:09:00.000-08:00

Hi,In MSBI-SSIS,we have XML Source but not destination.By using Script Component we can achive this.
Here are the clear explanation about XML Destination Using Script Component.
Rav's :-)
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

MCTS - I passed the 70-445 exam!

2009-12-28T02:52:00.000-08:00

Hi All,
I feel happy to inform you about today I cleared 70-445 exam with 89.9%!!!!!!!!!!!!!!!!!!
;-)
Rav’s

SSIS-Webservice && XML Task - Usage

2009-12-24T05:43:00.000-08:00

Hi,
Its a good video to know the usage of Webservice task in SSIS.
http://technet.microsoft.com/en-us/library/cc952927.aspx
In this video, you learned how to call a Web Service by using the Web Service task.
You also learned how to:
Configure an HTTP connection manager.
Configure the Web Service task itself.
Download the WSDL file that describes the Web service.
Call a Web method and supply the expected input values.
Read the return value by using an XML task.
And, use the results from the Web service in the package.
Thanks,Rav's

MSBI-'08 Videos=>> Setup '08,SSIS,SSAS,SSAS-Data Mining,SSRS

2009-12-24T05:41:00.000-08:00

'n Joy ;-)
http://technet.microsoft.com/en-us/library/bb418446(SQL.10).aspx

MSBI - Useful Videos

2009-12-23T07:23:00.000-08:00

http://siddhumehta.blogspot.com/2009/07/few-more-ssis-ssas-and-ssrs-video.html
http://siddhumehta.blogspot.com/2009/12/microsoft-business-intelligence-ssis.html

SSRS-Table Vs Matrix

2009-12-23T07:06:00.000-08:00

The report type defines the structure, or data region, of the data returnedby your query. The Report Wizard lets you present this information as eithera table or a matrix, though you have more options outside the wizard. Themain difference between these two types of data regions is the number ofcolumns. A table has a fixed number of columns; a matrix has a variablenumber determined by the query results.
More Details:
http://technet.microsoft.com/en-us/library/ms157334.aspx
http://www.bronios.com/index.php/2008/04/28/ssrs-performances-matrix-control-vs-table-control/

SSIS-Transactions usage

2009-12-22T22:57:00.000-08:00

Use the below Link:
http://www.mssqltips.com/tip.asp?tip=1585

SQL SERVER – Nth Highest Salary

2009-12-11T07:27:00.000-08:00

It's good one ;-)
http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/

SSIS - How to read vaiable values directly from Table

2009-12-11T04:11:00.000-08:00

Hi,
We have different ways to get the data from DB table to SSIS variables.
1.using Foreach loop container with ADO Enumerator option.2. Configurations like XML config,parent pakcege
Here we discuss with Script task takes the data fro mDatabase to local SSIS-Variables.
http://blog.boxedbits.com/archives/8

SSIS-Performance Tuning Techniques(SSIS Engine Overview,BlockingExecution Trees, Buffer Sizing, Parallelism)

2009-12-08T20:03:00.000-08:00

Hi Performance tuning is very important one as a process of data loading using ETL tools.
When you architect data integration solutions, your design decisions not only determine how successfully your solution meets functional requirements, but also how well your solution meets performance requirements. To make the right performance design decisions, you need to understand the performance architecture of your data integration tool and, just as importantly, the techniques that enable you to maximize the tool’s utilization of system resources such as memory and CPU.
Microsoft® SQL Server™ 2005 Integration Services (SSIS) provides full-featured data integration and workflow engines coupled with a rich development environment for building high-performance data integration solutions. SSIS provides a variety of optimization opportunities to help you maximize resource utilization while successfully meeting the needs of your specific data integration scenario.
http://msdn.microsoft.com/en-us/library/cc966529.aspx

http://shujaatsiddiqi.blogspot.com/2008/08/performance-improvement-of-sql-server.html

SSIS-Parallel Execution

2009-12-08T20:02:00.000-08:00

Hi,
this is important one to achieve parallel execution using SSIS.

http://shujaatsiddiqi.blogspot.com/2008/10/ssis-multithreading-parallel-execution.html

SQL SERVER-Rebuild Indexes AND Update Statistics

2009-12-08T05:59:00.000-08:00

Hi,
Rebuild Indexes and Update statistics is very important to improve the performance of the data.
SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in database. DBCC DBREINDEX is efficient over dropping and recreating indexes.
Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.
We have 2 good scripts to implement to achive our requirements.
Please have a look into this:
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx

http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

SSIS - Difference between Control Flow and Data Flow

2009-11-05T05:42:00.001-08:00

http://shahharsh.wordpress.com/2008/05/31/difference-between-control-flow-and-data-flow/

SSIS-Waiting for a file

2009-11-05T05:33:00.000-08:00

http://dichotic.wordpress.com/2009/10/20/ssis-waiting-for-a-file/

SSRS - Adding Subreports

2009-11-05T04:36:00.000-08:00

A subreport is a report item that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can design the parent report to pass parameters to the subreport. A subreport can be repeated within data regions, using a parameter to filter data in each instance of the subreport.
If you use subreports to display separate groups of data, consider using data regions (tables, matrices, lists, charts, or gauges) instead. Reports with data regions only may perform better than reports that include subreports.
Use data regions when you need to nest groups of data from the same data source within a single data region. Use subreports if you need to nest groups of data from different data sources within a single data region, reuse a subreport in multiple parent reports, or display a standalone report inside of another report. You can create a "briefing book" by placing multiple subreports inside the body of another report.
Note:
In Report Designer, if you preview a report that contains subreports, and then change the subreport, the preview may not be updated. To see the changes, click the Refresh button.
Using Parameters in Subreports
To pass parameters from the parent report to the subreport, define a report parameter in the report that you use as the subreport. When you place the subreport in the parent report, you can select the report parameter and a value to pass from the parent report to the report parameter in the subreport.
Note:
The parameter that you select from the subreport is a report parameter, not a query parameter. For more information about parameters, see Adding Parameters to Your Report.
You can place a subreport in the main body of the report, or in a data region. If you place a subreport in a data region, the subreport will repeat with each instance of the group or row in the data region. To pass a value from the group or row to the subreport, in the subreport value property, use a field expression for the field containing the value you want to pass to the subreport parameter.
For more information about working with subreports, see How to: Add a Subreport and Parameters (Reporting Services).

For More Details:-
Here is the good link aboutSubReport
http://www.simple-talk.com/content/print.aspx?article=278
Thanks,Rav’s

SSIS - Full-load vs. Delta-Load

2009-10-29T07:36:00.000-07:00

Nice discussion o nthis topix.Pls go thru the below URL.
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e17e8143-e19b-4cff-a1a0-49977cfaede9/
--Cheers, Rav

SSRS-Scale-Out Deployment Best Practices

2009-10-29T07:32:00.000-07:00

Check out the the third of five technical note as part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series: Reporting Services Scale-Out Deployment Best Practices
This technical note reviews the
SSRS Scale-Out Architecture
Report Catalog sizing
The benefits of File System snapshots for SSRS 2005
Why File System snapshots may not help for SSRS 2008
Using Cache Execution
Load Balancing your Network
Isolate your workloads
Report Data Performance Considerations

SSAS-Storage Modes in Analysis Services

2009-10-29T07:24:00.000-07:00

Why did we only use the MOLAP storage mode in Project REAL? There is also ROLAP and HOLAP storage, but they weren’t used at all. Also, why doesn’t Project REAL illustrate pro-active caching?

The Analysis Services 2005 Performance Guide contains a more complete discussion of the storage modes in Analysis Services in Appendix B. Because the question comes up from time to time regarding Project REAL, this write-up gives a summary.

Every partition in AS has an associated storage mode. MOLAP partitions store aggregations and a copy of the source data (fact and dimension data) in a multidimensional structure on the Analysis server. This is the optimal storage mode for several reasons:
Compression. When relational data is processed into AS, the storage needed is typically 20% to 40% of the size of the un-indexed relational fact table. Less I/O means that the MOLAP data is faster to access.
Multidimensional structure. The format in which AS data is stored is optimized for the kind of access that OLAP users do – access to the data using arbitrary dimensions and arbitrary slices of the dimension.
Data local to AS. There is no need for AS to call out to another service to obtain data when MOLAP storage is used.
Indexes are not needed on relational fact tables. When AS processes fact data, it generally can do a full table scan, so indexes are not needed. When there are multiple partitions in a measure group, there will be one join to the dimension that is used for partitioning, but that’s all. Then at query time, no requests are sent to the relational database, so no indexes are needed for querying. Given that indexes can multiply the size of data in the relational system, the space savings can be significant.

HOLAP partitions store aggregations in a multidimensional structure on the Analysis server, but do not copy fact data from the original relational database. As a result, whenever Analysis Services needs to resolve a query against fact data stored in a HOLAP partition, Analysis Services must query the relational database rather than using a multidimensional structure stored on the Analysis server. Some administrators choose HOLAP because HOLAP appears to require less total storage space while yielding high performance queries. However, the disadvantages almost always outweigh the advantages:
When a query “misses” the aggregations AS will have to query the relational database for fact-level data. This is necessarily slower than getting data from the compressed and natively multidimensional MOLAP storage.
Because of the above, many administrators will create more aggregations to decrease the number of “misses.” At some point the additional aggregations can become larger than the fact data would have been. But even if it doesn’t, more aggregations take more time to process.
Processing of HOLAP partitions is not significantly faster than processing of MOLAP partitions. In both cases the entire fact table must be read into AS in order to create aggregations. The only difference is whether AS also saves the fact data. However if more aggregations were created because of the previous point, processing will be slower.

ROLAP partitions store aggregations in the same relational database that stores the fact data. ROLAP partitions take longer to process, and because all queries must be redirected to the relational database, ROLAP partitions are slower to query against as well.

Given all this, it sounds like only MOLAP should ever be used! For most implementations that is the case. The exception is when near-real-time access is needed to data that changes throughout the day. Even there, pro-active caching is the first technique of choice. However, if even pro-active caching does not allow frequent enough updates, then the use of one ROLAP partition in the measure group is a way to handle the incoming data.

The recommended way to handle near-real-time scenarios is to take advantage of the fact that the storage mode is set on a per-partition basis. A vast majority of data in an OLAP cube does not change throughout the day. That data should be stored in MOLAP partitions. One partition in the measure group is designated to receive updates as they come in. That way, only that partition needs to be updated. This partition can be updated using proactive caching or it can be defined as a ROLAP partition with zero aggregations. In the latter case, all queries to this partition will be sent to the relational source, and current data will always be as current as that table.

For more details about near-real-time data access, see the section “Near Real-Time Data Refreshes” in the Analysis Services 2005 Performance Guide.

An example of how such a partitioning scheme would be updated might look like this:
Fact tables contain weekly partitions in the RDBMS, and there are corresponding weekly partitions in AS.
Each night, data for the previous day is processed into the correct weekly partition.
As new data comes in during the day, it flows into a relational partition for current updates. AS has a near-real-time partition that maps to that relational partition. Either using pro-active caching or a ROLAP partition with zero aggs, users get access to new data throughout the day.
At night, the collected daily data is moved to the weekly partition and processed (either by full process or using incremental update). The near-real-time partition is now ready to collect the next day’s updates.

Returning to the original question: Why did we only use the MOLAP storage mode in Project REAL? The simple answer is that MOLAP storage performs the best. The slightly extended answer is that Project REAL is based on the scenario in the Barnes & Noble data warehouse. The business model centered on daily updates, not near-real-time access. Therefore, there was no need for proactive caching or ROLAP partitions.

SSIS-ETL World Record

2009-10-29T07:21:00.000-07:00

Today at the launch of SQL Server 2008, you may have seen the references to world-record performance doing a load of data using SSIS. Microsoft and Unisys announced a record for loading data into a relational database using an Extract, Transform and Load (ETL) tool. Over 1 TB of TPC-H data was loaded in under 30 minutes. I wanted to provide some background material in the form of a Q&A on the record, since it’s hard to give many details in the context of a launch event. We are also planning a paper that talks about all this, so think of this article as a place-holder until the full paper comes along. I hope you find this background information useful.
-- See the below link for more info
http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

SQL collations in SQL SERVER – 2005

2009-10-12T23:09:00.000-07:00

SQL collations=>
The SQL collations option is used for compatibility with earlier versions of SQL Server. Select this option to match settings compatible with SQL Server 8.0, 7.0, or earlier. For more information, see Using SQL Collations in SQL Server 2005 Books Online.
Collation designator and sort order==>
Designates the collation to be used by this instance of SQL Server Express. An SQL collation is selected by default for English-language locales. The default collation for non-English locales is the Microsoft Windows locale setting for your computer — the Language for non-Unicode programs setting, or the closest equivalent from Regional and Language Options in Control Panel.
The check boxes under Collation designator and sort order let you specify the sort order to use with the Collation designator you have selected. Binary is the fastest sorting order and is case-sensitive. If Binary is selected, the Case-sensitive, Accent-sensitive, Kana-sensitive, and Width-sensitive options are not available. For more information, see Windows Collation Sorting Styles in SQL Server 2005 Books Online.
Note Change the default settings for the sort order only if your installation of SQL Server must match the collation settings used by another instance of SQL Server, or if it must match the Windows locale of another computer. Good Link:->
http://blog.sqlauthority.com/2007/08/30/sql-server-2005-find-database-collation-using-t-sql-and-ssms/#comment-56641

*->MSBI-Books to read**

2009-09-30T09:24:00.000-07:00

http://sqlblogcasts.com/blogs/martinisti/archive/2009/03/19/books-to-read.aspx

MSBI - Some IMP URL For this Week

2009-09-30T08:19:00.001-07:00

http://blogs.msdn.com/bi/
http://blogs.msdn.com/msbi/default.aspx

http://www.tech-faq.com/
(it is a good site for all basic concepts)

http://sqlblogcasts.com/blogs/martinisti/default.aspx
(70-446)

MSBI-Data Mining

2009-09-30T08:18:00.000-07:00

What is data Mining???
Data mining is usually defined as searching, analyzing and sifting through large amounts of data to find relationships, patterns, or any significant statistical correlations. With the advent of computers, large databases and the internet, it is easier than ever to collect millions, billions and even trillions of pieces of data that can then be systematically analyzed to help look for relationships and to seek solutions to difficult problems. Besides governmental uses, many marketers use data mining to find strong consumer patterns and relationships. Large organizations and educational institutions also data mine to find significant correlations that can enhance our society.
Purpose of Data Mining?
Data mining uses a relatively large amount of computing power operating on a large set of data to determine regularities and connections between data points. Algorithms that employ techniques from statistics, machine learning and pattern recognition are used to search large databases automatically. Data mining is also known as Knowledge-Discovery in Databases (KDD).
You can see video about Data Mining from the below link.

http://blog.sqlauthority.com/2009/09/03/sql-server-what-is-data-mining-a-simple-introductory-note/

SSIS Video’s-Some IMP Links

2009-09-30T08:17:00.002-07:00

Hi,
I captured some videos from the below blog.
Enjoy;-)

http://blogs.msdn.com/mattm/
blog.
Its really good one.
Please don’t miss it.

SQL PASS 2009
I was just looking at the sessions for the SQL PASS 2009 Summit and was happy to see so many SSIS presentations on the list!
Andy Leonard
Applied SSIS Design Patterns
Steve Simon
SQL Server Integration Services and the modern financial institution
Davide Mauri
Instrumenting, Monitoring and Auditing of SSIS ETL Solutions
Brian Knight
Loading a Data Warehouse with SSIS
Dave Fackler
ETL from the Trenches: Using SSIS in the Real World
Joy Mundy
ETL: The Linchpin for the Complete Data Warehouse
Erik Veerman
Overcoming SSIS Deployment and Configuration Challenges Data Profiling and Cleansing with Integration Services 2008

SSWUG.ORG Ultimate Virtual Conference – April 2009
Samples for the CDC & Merge demo
Avoiding Common Pitfalls in SSIS

Matt Masson - Incremental Data Warehouse Loads with Merge & CDC
John Welch - Doing More (ETL) With Less (Effort) by Automating SSIS
Brian Knight - Loading a Data Warehouse With SSIS
Anthony D’Angelo - Deep Dive: Extending SSIS with .NET Development

SSIS - Tutorial: SSIS Performance Videos

2009-09-30T08:17:00.001-07:00

These all are useful links about SSIS-Videos.
This post is coming a little late (these have already been announced here and here), but in case you haven’t seen these before, I wanted to bring attention to four new performance related created by the SQLCAT and SSIS teams. They mostly focus on larger scale/Enterprise level package deployments, but there are good tips for all levels of SSIS usage.
From the SQLCAT post:
Measuring and Understanding the Performance of Your SSIS Packages in the Enterprise (SQL Server Video) Link Watch this video
Author: Denny Lee, Microsoft Corporation
This video demonstrates how to measure and understand the performance of packages, based on lessons learned from enterprise customers. In this video, you will learn the following guidelines for improving performance:
How the limits of the source system affect performance.
Why disk I/O is important.
Why you should establish a package performance baseline
Tuning Your SSIS Package Data Flow in the Enterprise (SQL Server Video) Link Watch this video
Author: David Noor, Microsoft Corporation
This video demonstrates how to improve the performance of the data flow in an Integration Services package. In this video, you will learn how to tune the following phases of the data flow:
Extraction
Transformation
Loading
You can apply these performance tuning tips when you design, develop, and run the data flow.
Understanding SSIS Data Flow Buffers (SQL Server Video) Link Watch this video
Author: Bob Bojanic, Microsoft Corporation
This video looks at the memory buffers that transfer data to the data flow of an Integration Services package. The video includes the following items:
Demonstration of a simple package that shows you how Integration Services divides the data along the data flow paths and how buffers carry data along those paths. This simple package performs the following operations:
Data extraction
Character mapping
Creation of new columns by using a synchronous transformation
Multicasting
Sorting by using a blocking (asynchronous) transformation.
Design guidelines for building and maintaining the data flow of a package.
Designing Your SSIS Packages for Parallelism (SQL Server Video) Link Watch this video
Author: Matt Carroll, Microsoft Corporation
This video demonstrates how to increase the performance of your Integration Services packages by designing for parallelism.
------
These four videos join the growing list of SSIS tutorial videos in the SQL Server Video series. Others that might interest you:
Creating a Basic Package
Exporting SQL Server Data to Excel
How to: Automate SSIS Package Execution by Using the SQL Server Agent
How to: Call a Web Service by Using the Web Service Task
How to: Implement a Lookup Transformation in Full Cache Mode
How to: Use the Data Profiling Task
Troubleshooting: SSIS Package Execution Using SQL Server Agent

SQL Server Performance Tuning

2009-09-30T08:16:00.001-07:00

5 Key Areas You Must Focus on to Improve Performance & Availability.
www.Heroix.com

SQL Server – Use Of NOLOCK / ROWLOCK

2009-09-30T08:14:00.000-07:00

Hi,
While we are working to retrieve huge data or sum(*) data with (NOLOCK) will give better performance. After some quick searching, I found a great page on Sql-Server-Performance.com. Apparently SQL server puts a locking mechanism around all data access and manipulation to prevent things like dirty reads and the reading of uncommitted data. I was totally not aware of this - SQL server just worked and that was good.
Apparently though, these locks (as with most locking) comes with a perforance hit. This is good though as it ensures the integrity of your data. However, there are times when you just don't care. Some tables, especially look up tables, are not updated often (if ever) and locking around these brings on unnecessary overhead.
ROWLOCKUse row-level locks when reading or modifying data.
PAGLOCKUse page-level locks when reading or modifying data.
TABLOCKUse a table lock when reading or modifying data.
DBLOCKUse a database lock when reading or modifying data.
UPDLOCKUPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.XLOCKUse exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCKUse a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.
NOLOCKThis does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Using the SQL directives NOLOCK and ROWLOCK can circumvent this SQL Server's locking mechanism and speed up queries. Here, I demonstrate using it on a SQL Join query in which I get Blog entry information:
Examples:SELECT OrderIDFROM Orders (WITH ROWLOCK)WHERE OrderID BETWEEN 100AND 2000
UPDATE Products (WITH NOLOCK)SET ProductCat = 'Machine'WHERE ProductSubCat = 'Mac'

· SELECT
· b.id,
· b.name,
· ( t.id ) AS tag_id,
· ( t.name ) AS tag_name
· FROM
· blog_entry b
· INNER JOIN
· blog_entry_tag_jn btjn (NOLOCK)
· ON
· b.id = btjn.blog_entry_id
· INNER JOIN
· tag t (NOLOCK)
· ON
· btjn.tag_id = t.id
Notice that by using the NOLOCK directive on the blog_entry_tag_jn and the tag Tables. The Tag table pretty much never gets updated and the blog_entry_tag_jn (joining of entries to tags) table gets updated ONLY when add or update a blog entry. Due to the low frequency of updates, the requirement for locking on these tables is (next to) pointless. By using the NOLOCK directive I am asking SQL to ignore all locking mechanism surroundings those tables and proceed directly to data-retrieval. Theoretically, this should provide a small performance gain
Thanks
Rav’s.

SQL SERVER – How to Find List of Primary Keys and Foreign Keys in a Database

2009-08-18T23:37:00.000-07:00

Good Article from Pinal.

http://blog.sqlauthority.com/2009/07/17/sql-server-two-methods-to-retrieve-list-of-primary-keys-and-foreign-keys-of-database/

SQL SERVER – MSBI – Design Process Decision Flow

2009-08-18T23:36:00.000-07:00

MicrosoftMicrosoft Business Intelligence offers a complete suite of programs that supports all facets of decision-making. Through tight integration with the Microsoft SQL Server 2005 platform, Microsoft provides comprehensive business intelligence (BI) capabilities that deliver the right information, at the right time, and in the right format. It has emerged in the Leaders Quadrant in
MS BI expertise which include MS BI Program Implementation Strategy, MS Reporting/OLAP implementation Roadmap, SSIS Implementation Roadmap, SQL Server 2005 Upgrade Roadmap, SSIS Migration Roadmap and MS Scorecard.
Leveraging the entire MS BI technology stack,.
We have expertise in MS BI products such as MS SQL Server, MS SQL Server Analysis Services, MS SQL Server Reporting Services, MS SQL Server Integration Services, Microsoft Office Business Scorecard Manager, Microsoft PerformancePoint Server and MS SharePoint Portal.
MS BI Services include:
· BI Readiness Assessment, Tool Evaluation (ETL & OLAP) and Tool Standardization.
· Design and Implementation of ETL strategy using MS SQL Server Integration Services with extensive custom programmability on Microsoft SQL Server Business Intelligence Development Studio and SQL Server Management Studio.
· Design and development of reports using MS SQL Server Reporting Services (Enterprise, ad-hoc, web-based and embedded reports) and reporting on Microsoft SharePoint Portal.
· Design and development of analytical solutions and design of multi-dimensional cubes for OLAP analysis applying dimensional modelling approach using MS SQL Server Analysis Services
· Product Upgradation for SQL Server with detailed platform change analysis, enterprise security framework and step-wise comprehensive approach
· Migration and consolidation of data from legacy VSAM, ERP and web portal with SQL Server encompassing ETL activities with MS SQL Server Integration Services and progress reporting with MS SQL Server Reporting Services with tight integration of .NET and SQL Server stored procedures.
· Web based reporting using advance visualization, dashboard and analytics through Microsoft PerformancePoint Server.

http://blog.sqlauthority.com/2009/07/28/sql-server-2008-design-process-decision-flow/

SQL Server 2005 – Use Of Import/Export wizard

2009-08-18T23:35:00.001-07:00

As per the requirement we can easily transfer the data from one table to another table in same server as well in other server by using the import/export wizard.

Schema and as well as data we can transfer at a time using the SQL Server 2008 option.

See for more Details:

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

SQL Server 2005 – How Much Space Does My Database Use?

2009-08-18T23:33:00.001-07:00

Hi it’s very important to identify the following things as sql server developer. Like-
how large your database is, how full the files are, which tables and indexes use the most space, which tables might be dormant, etc…
Here is the link for excellent script from Glen Berry.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1619.entry

SQL Server 2005 – Job History

2009-08-18T23:31:00.000-07:00

Hi
Sql Server jobs are writing log info into the system tables.
If we get struck at any Job or related steps,by using the Sys.job history we can easily identify the problem is.It will give the description at high level.(success,failure,stopped all the info)

By using the following query we can get complete job info from sys tables.

SELECT a.name
,count(a.name ) as CNT
,b.[step_id]
,b.[step_name]
,b.[message]
,b.[run_date]
,b.[run_status]
,b.run_time
,b.run_duration
FROM [sysjobhistory] b JOIN sysjobs a
ON a.job_id = b.job_id

WHERE b.[run_date] ='20090101'
and b.[run_date] <='20090818'

See the below link.
http://sqlservernation.com/blogs/tipweek/archive/2009/02/25/viewing-sql-server-jobs-and-history.aspx

SQL Server 2005 – Use “FileStream”

2009-08-18T23:29:00.000-07:00

We are getting problem while we are inserting the data into database for especially datatype of varbinary and image .Instead of using those datatype we have one more better option like - Filestream storage type. As you are probably aware, varbinary and image are very difficult data types to handle and it is a pain to manage them. Filestream storage overcomes all the limitations and has emerged as a true winner in terms of storage.
With FILESTREAM, the SQL Server team not only added a feature to handle unstructured data, but also made sure that it smoothly integrates with many of the existing features of SQL Server.
· FILESTREAM feature is available with all versions of SQL Server 2008, including SQL Server Express.
· SQL Server Express database has a 4 GB limitation; however this limitation does not apply to the FILESTREAM data stored in a SQL Server Express database.
· FILESTREAM Columns can be replicated.
· FILESTREAM enabled databases can be used with LOG Shipping
· FILESTREAM columns can be used in Full Text Indexes
· FILESTREAM works with all recovery models
· FILESTREAM File Groups can be placed on compressed disk volumes
· The maximum size of the file that can be stored into the FILESTREAM data storage is limited by the size of the disk volume only.

I have been added some more useful links here.

http://blog.sqlauthority.com/2009/07/13/sql-server-blob-pointer-to-image-image-in-database-filestream-storage/
http://www.simple-talk.com/sql/sql-training/an-introduction-to-sql-server-filestream/

Sql Server Links

2009-07-26T10:40:00.000-07:00

---------------SQL Server 2005---------------http://www.accelebrate.com/sql_training/ssis_tutorial.htm http://www.accelebrate.com/sql_training/ssas_tutorial.htm http://www.accelebrate.com/sql_training/ssrs_tutorial.htm
---------------SQL Server 2008---------------
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm http://www.accelebrate.com/sql_training/ssas_2008_tutorial.htm http://www.accelebrate.com/sql_training/ssrs_2008_tutorial.htm

SSIS - Adventure Approach - Best Practice

2009-07-26T10:37:00.000-07:00

Integration Services Tutorial
Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including the extraction, transformation, and load (ETL) packages for data warehousing. Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, Integration Services Service, for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model.
In this tutorial, you will learn how to use SSIS Designer to create a simple Microsoft SQL Server 2005 Integration Services package. The package that you create takes data from a flat file, reformats the data, and then inserts the reformatted data into a fact table. In following lessons, the package will be expanded to demonstrate looping, package configurations, logging and error flow.
What You Will Learn
The best way to become acquainted with the new tools, controls and features available in Microsoft SQL Server 2005 Integration Services is to use them. This tutorial walks you through SSIS Designer to create a simple ETL package that includes looping, configurations, error flow logic and logging.
Lessons
Description
Lesson 1: Creating a Simple ETL Package
In this lesson, you will create a simple ETL package that extracts data from a single flat file, transforms the data using lookup transformations and finally loads the result into a fact table destination.
Lesson 2: Adding Looping
In this lesson, you will expand the package you created in Lesson 1 to take advantage of new looping features to extract multiple flat files into a single data flow process.
Lesson 3: Adding Package Configurations
In this lesson, you will expand the package you created in Lesson 2 to take advantage of new package configuration options.
Lesson 4: Adding Logging
In this lesson, you will expand the package you created in Lesson 3 to take advantage of new logging features.
Lesson 5: Adding Error Flow Redirection
In this lesson, you will expand the package you created in lesson 4 to take advantage of new error output configurations.
Requirements
This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL Server 2005 Integration Services.
To use this tutorial, your system must have the following components installed:
· SQL Server 2005 with the AdventureWorksDW database. To enhance security, the sample databases are not installed by default. To install the sample databases, see Running Setup to Install AdventureWorks Sample Databases and Samples.
· This tutorial also requires sample data. The sample data is installed together with the samples. If you cannot find the sample data, return to the procedure above and complete installation as described.

In this lesson, you will create a simple ETL package that extracts data from a single flat file source, transforms the data using two lookup transformation components, and writes that data to the FactCurrencyRate fact table in AdventureWorksDW. As part of this lesson, you will learn how to create new packages, add and configure data source and destination connections, and work with new control flow and data flow components.
Understanding the Package Requirements
Before creating a package, you need a good understanding of the formatting used in both the source data and the destination. Once you understand both of these data formats, you are then able to define the transformations necessary to map the source data to the destination.
Looking at the Source
For this tutorial, the source data is a set of historical currency data contained in the flat file, SampleCurrencyData.txt. The source data has the following four columns: the average rate of the currency, a currency key, a date key, and the end-of-day rate.
Here is an example of the source data contained in the SampleCurrencyData.txt file:
1.00010001 ARS 9/3/2001 0:00 0.99960016
1.00010001 ARS 9/4/2001 0:00 1.001001001
1.00020004 ARS 9/5/2001 0:00 0.99990001
1.00020004 ARS 9/6/2001 0:00 1.00040016
1.00050025 ARS 9/7/2001 0:00 0.99990001
1.00050025 ARS 9/8/2001 0:00 1.001001001
1.00050025 ARS 9/9/2001 0:00 1
1.00010001 ARS 9/10/2001 0:00 1.00040016
1.00020004 ARS 9/11/2001 0:00 0.99990001
1.00020004 ARS 9/12/2001 0:00 1.001101211

When working with flat file source data, it is important to understand how the Flat File connection manager interprets the flat file data. If the flat file source is Unicode, the Flat File connection manager defines all columns as [DT_WSTR] with a default column width of 50. If the flat file source is ANSI-encoded, the columns are defined as [DT_STR] with a column width of 50. You will probably have to change these defaults to make the string column types more appropriate for your data. To do this, you will need to look at the data type of the destination where the data will be written to and then choose the correct type within the Flat File connection manager.
Looking at the Destination
The ultimate destination for the source data is the FactCurrencyRate fact table in AdventureWorksDW. The FactCurrencyRate fact table has four columns, and has relationships to two dimension tables, as shown in the following table.
Column Name
Data Type
Lookup Table
Lookup Column
AverageRate
float
None
None
CurrencyKey
int (FK)
DimCurrency
CurrencyKey (PK)
TimeKey
Int (FK)
DimTime
TimeKey (PK)
EndOfDayRate
float
None
None
Mapping Source Data to be Compatible with the Destination
Analysis of the source and destination data formats indicates that lookups will be necessary for the CurrencyKey and TimeKey values. The transformations that will perform these lookups will obtain the CurrencyKey and TimeKey values by using the alternate keys from DimCurrency and DimTime dimension tables.
Flat File Column
Table Name
Column Name
Data Type
0
FactCurrencyRate
AverageRate
Float
1
DimCurrency
CurrencyAlternateKey
nchar (3)
2
DimTime
FullDateAlternateKey
Datetime
3
FactCurrencyRate
EndOfDayRate
Float

The first step in creating a package in Microsoft SQL Server 2005 Integration Services (SSIS) is to create an Integration Services project. This project includes the templates for the objects — data sources, data source views, and packages — that you use in a data transformation solution.
To create a new Integration Services project
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, and click SQL Server Business Intelligence Development Studio.
2. On the File menu, point to New, and click Project to create a new Integration Services project.
3. In the New Project dialog box, select Integration Services Project in the Templates pane.
4. In the Name box, change the default name to SSIS Tutorial.
5. Click OK.
By default, an empty package, titled Package.dtsx, will be created and added to your project.
6. In the Solution Explorer toolbar, right-click Package.dtsx, click Rename, and rename the default package to Lesson 1.dtsx.
7. (Optional) When prompted to rename the package object, click Yes.
In this task, you add a Flat File connection manager to the package that you just created. A Flat File connection manager enables a package to extract data from a flat file. Using the Flat File connection manager, you can specify the file name and location, the locale and code page, and the file format, including column delimiters, to apply when the package extracts data from the flat file. In addition, you can manually specify the data type for the individual columns, or use the Suggest Column Types dialog box to automatically map the columns of extracted data to Integration Services data types.
You must create a new Flat File connection manager for each file format that you work with. Because this tutorial extracts data from multiple flat files that have exactly the same data format, you will need to add configure only one Flat File connection manager to your package.
For this tutorial, you will configure the following properties in your Flat File connection manager:
· Column names Because the flat file does not have column names, the Flat File connection manager creates default column names. These default names are not useful for identifying what each column represents. To make these default names more useful, you need to change the default names to names that match the fact table into which the flat file data is to be loaded.
· Data mappings The data type mappings that you specify for the Flat File connection manager will be used by all flat file data source components that reference the connection manager. You can either manually map the data types by using the Flat File connection manager, or you can use the Suggest Column Types dialog box. In this tutorial, you will view the mappings suggested in the Suggest Column Types dialog box and then manually make the necessary mappings in the Flat File Connection Manager Editor dialog box.
To add a Flat File connection manager
1. Right-click anywhere in the Connection Managers area, and then click New Flat File Connection.
2. In the Flat File Connection Manager Editor dialog box, for Connection manager name, type Sample Flat File Source Data.
3. Click Browse.
4. In the Open dialog box, browse to the sample data folder and open the SampleCurrencyData.txt file. By default, the tutorial sample data is installed to the c:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder.
To rename columns in the Flat File connection manager
1. In the Flat File Connection Manager Editor dialog box, click Advanced.
2. In the property pane, makes the following changes:
· Change the Column 0 name property to AverageRate.
· Change the Column 1 name property to CurrencyID.
· Change the Column 2 name property to CurrencyDate.
· Change the Column 3 name property to EndOfDayRate.
Note:
By default, all four of the columns are initially set to a string data type [DT_STR] with an OutputColumnWidth of 50.
To remap column data types
1. In the Flat File Connection Manager Editor dialog box, click Suggest Types.
Integration Services automatically suggests data types based on the first 100 rows of data, but you can change the suggestion options to sample more or less data, specify the default data type for integer or Boolean data, or add spaces added as padding to string columns.
For now, make no changes, and click Cancel to return to the Advanced pane of the Flat File connection Manager Editor dialog box and view the suggested column data types.
In this tutorial, Integration Services suggests the data types shown in the second column of the table below for the data from the SampleCurrencyData.txt file. However, the data types that are required for the columns in the destination, which will be defined in a later step, are shown in the last column of the following table.
Flat File Column
Suggested Type
Destination Column
Destination Type
AverageRate
Float [DT_R4]
FactCurrencyRate.AverageRate
Float
CurrencyID
String [DT_STR]
DimCurrency,CurrencyAlternateKey
nchar(3)
CurrencyDate
Date [DT_DATE]
DimTime.FullDateAlternateKey
datetime
EndOfDayRate
Float [DT_R4]
FactCurrencyRate.EndOfDayRate
Float
The data types suggested for the CurrencyID and CurrencyDate columns are not compatible with the data types of the fields in the destination table. Because the data type of DimCurrency.CurrencyAlternateKey is nchar (3), CurrencyID needs to be changed from string [DT_STR] to string [DT_WSTR]. Additionally, the field DimTime.FullDateAlternateKey is defined as a DateTime data type; therefore, CurrencyDate needs to be changed from date [DT_Date] to database timestamp [DT_DBTIMESTAMP].
2. In the property pane, change the data type of column CurrencyID from string [DT_STR] to Unicode string [DT_WSTR].
3. In the property pane, change the data type of column CurrencyDate from date [DT_DATE] to database timestamp [DT_DBTIMESTAMP].
4. Click OK.

After you have added a Flat File connection manager to connect to the data source, the next task is to add an OLE DB connection manager to connect to the destination. An OLE DB connection manager enables a package to extract data from or load data into any OLE DB–compliant data source. Using the OLE DB Connection manager, you can specify the server, the authentication method, and the default database for the connection.
In this lesson, you will create an OLE DB connection manager that uses Windows Authentication to connect to the local instance of AdventureWorksDB. The OLE DB connection manager that you create will also be referenced by other components that you will create later in this tutorial, such as the Lookup transformation and the OLE DB destination.
To add and configure an OLE DB Connection Manager
1. Right-click anywhere in the Connection Managers area, and then click New OLE DB Connection.
2. In the Configure OLE DB Connection Manager dialog box, click New.
3. For Server name, enter localhost.
When you specify localhost as the server name, the connection manager connects to the default instance of Microsoft SQL Server 2005 on the local computer. To use a remote instance of SQL Server 2005, replace localhost with the name of the server to which you want to connect.
4. In the Log on to the server group, verify that Use Windows Authentication is selected.
5. In the Connect to a database group, in the Select or enter a database name box, type or select AdventureWorksDW.
6. Click Test Connection to verify that the connection settings you have specified are valid.
7. Click OK.
8. Click OK.
9. In the Data Connections pane of the Configure OLE DB Connection Manager dialog box, verify that localhost.AdventureWorksDW is selected.
10. Click OK.
After you have created the connection managers for the source and destination data, the next task is to add a Data Flow task to your package. The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and provides the functionality for transforming, cleaning, and modifying data as it is moved. The Data Flow task is where most of the work of an extract, transform, and load (ETL) process occurs.
Note:
Microsoft SQL Server 2005 Integration Services separates data flow from control flow. This separation of data flow from control flow is one of the dramatic differences between Integration Services and Microsoft SQL Server 2000 Data Transformation Services.
To add a Data Flow task
1. Click the Control Flow tab.
2. In the Toolbox, expand Control Flow Items, and drag a Data Flow Task onto the design surface of the Control Flow tab.
3. On the Control Flow design surface, right-click the newly added Data Flow Task, click Rename, and change the name to Extract Sample Currency Data.
It is good practice to provide unique names to all components that you add to a design surface. For ease of use and maintainability, the names should describe the function that each component performs. Following these naming guidelines allows your Integration Services packages to be self-documenting. Another way to document your packages is by using annotations. For more information about annotations, see Using Annotations in Packages.
In this task, you will add and configure a Flat File source to your package. A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify the format and structure of the data to be extracted from the flat file by a transform process. The Flat File source can be configured to extract data from a single flat file by using the file format definition provided by the Flat File connection manager.
For this tutorial, you will configure the Flat File source to use the Sample Flat File Source Data connection manager that you previously created.
To add a Flat File Source component
1. Open the Data Flow designer, either by double-clicking the Extract Sample Currency Data data flow task or by clicking the Data Flow tab.
2. In the Toolbox, expand Data Flow Sources, and then drag a Flat File Source onto the design surface of the Data Flow tab.
3. On the Data Flow design surface, right-click the newly added Flat File Source, click Rename, and change the name to Extract Sample Currency Data.
4. Right-click the Flat File source to open the Flat File Source Editor dialog box.
5. In the Flat file connection manager box, type or select Sample Flat File Data.
6. Click Columns and verify that the names of the columns are correct.
7. Click OK.
After you have configured the Flat File source to extract data from the source file, the next task is to define the Lookup transformations needed to obtain the values for the CurrencyKey and TimeKey. A Lookup transformation performs a lookup by joining data in the specified input column to a column in a reference dataset. The reference dataset can be an existing table or view, a new table, or the result of an SQL statement. The Lookup transformation uses an OLE DB connection manager to connect to the database that contains the data that is the source of the reference dataset.
For this tutorial, you will add and configure the following two Lookup transformation components to the package:
· One transformation to perform a lookup of values from the CurrencyKey column of the DimCurrency dimension table based on matching CurrencyID column values from the flat file.
· One transformation to perform a lookup of values from the TimeKey column of the DimTime dimension table based on matching CurrencyDate column values from the flat file.
In both cases, the Lookup transformations will utilize the OLE DB connection manager that you previously created.
To add and configure the CurrencyKey Lookup transformation
1. In the Toolbox, expand Data Flow Transformations, and then drag Lookup onto the design surface of the Data Flow tab.
2. Click the Extract Sample Currency Data flat file source and drag the green arrow onto the newly added Lookup transformation to connect the two components.
3. On the Data Flow design surface, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup Currency Key.
4. Double-click the Lookup Currency Key transformation.
5. In the Lookup Transformation Editor dialog box, in the OLE DB connection manager box, ensure that localhost.AdventureWorksDW is displayed.
6. In the Use a table or view box, type or select [dbo].[DimCurrency].
7. Click the Columns tab.
8. In the Available Input Columns panel, drag CurrencyID to the Available Lookup Columns panel and drop it on CurrencyAlternateKey.
9. Select CurrencyKey.
10. Click OK.
To add and configure the DateKey Lookup transformation
1. In the Toolbox, drag Lookup onto the Data Flow design surface.
2. Click the Lookup Currency Key transformation and drag the green arrow onto the newly added Lookup transformation to connect the two components.
3. On the Data Flow design surface, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup Date Key.
4. Double-click the Lookup Date Key transformation.
5. In the Lookup Transformation Editor dialog box, in the OLE DB connection manager box, ensure that localhost.AdventureWorksDW is displayed.
6. In the Use a table or view box, type or select [dbo].[DimTime].
7. Click the Columns tab.
8. In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.
9. Select TimeKey.
10. Click OK.
Your package now can extract data from the flat file source and transform that data into a format that is compatible with the destination. The next task is to actually load the transformed data into the destination. To load the data, you must add an OLE DB destination to the data flow. The OLE DB destination can use a database table, view, or an SQL command to load data into a variety of OLE DB-compliant databases.
In this procedure, you add and configure an OLE DB destination to use the OLE DB connection manager that you previously created.
To add and configure the Sample OLE DB destination
1. In the Toolbox, expand Data Flow Destinations, and drag OLE DB Destination onto the design surface of the Data Flow tab.
2. Click the Lookup Date Key transformation and drag the green arrow over to the newly added OLE DB Destination to connect the two components together.
3. On the Data Flow design surface, right-click the newly added OLE DB Destination component, click Rename, and change the name to Sample OLE DB Destination.
4. Double-click Sample OLE DB Destination.
5. In the OLE DB Destination Editor dialog box, ensure that localhost.AdventureWorksDW is selected in the OLE DB Connection manager box.
6. In the Name of the table or the view box, type or select [dbo].[FactCurrencyRate].
7. Click Mappings.
8. Verify that the input columns are mapped correctly to the destination columns.
9. Click OK.
In this lesson, you have done the following tasks:
· Created a new Microsoft SQL Server 2005 Integration Services (SSIS) project.
· Configured the connection managers that the package needs to connect to the source and destination data.
· Added a data flow that takes the data from a flat file source, performs the necessary Lookup transformations on the data, and configures the data for the destination.
Your package is now complete! It is time to test your package.
To run the Lesson 1 tutorial package
1. On the Debug menu, click Start Debugging.
The package will run, resulting in 1097 rows successfully added into the FactCurrency fact table in AdventureWorksDW.
2. After the package has completed running, on the Debug menu, click Stop Debugging

DataMining Concepts

2009-07-26T10:31:00.000-07:00

Module 8: Data Mining

Data Mining........................................................................................ 2
Introduction........................................................................................ 2
Demonstration 1: Browse Data Mining Models..................................... 2
Demonstration 2: Add DM Viewers to a Web Page............................... 5

Data Mining
Introduction
This section provides an overview of the data mining features in SQL Server 2005. The following features are covered in the demonstrations:
· Naïve Bayes Model
· Decision Tree Model
· Association Model
· Data Mining Viewer
Demonstration 1: Browse Data Mining Models
This demonstration introduces you to mining models through the built-in browsers for developers and administrators. You will explore a Naïve Bayes mining model to review attribute characteristics, attribute discrimination, and dependencies. You will also explore a Decision Tree model, an Association model, and compare the predictive capability of multiple models.
#
Task
Notes
1
Browse the Naïve Bayes model of the Targeted Mailing mining structure of the Adventure Works DW database

Before you start, you need to make sure you have the AdventureWorks DW Analysis Services database installed if you did not already deploy this database during an earlier module.:

· In Business Intelligence Development Studio, open the Adventure Works solution at C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\awasdb\AdventureWorksAS.slnbi.
· Deploy the project.

In SQL Server Management Studio (SSMS):
Connect to the local Analysis Server.
Open the Mining Structures folder of Adventure Works DW.
Open the Targeted Mailing mining structure.
Each mining structure is a set of mining models that goes against a single data source. You can use the same source as shown in the Targeted Mailing structure, which has four different mining models. By contrast, the Market Basket structure has only one. Think of the mining structure as the problem to solve, with each model as an option to consider for solving the problem.
Right-click TM_Naive_Bayes and click Browse.
Naïve Bayes is the simplest model, so it’s a good one to start with.
2
Review the Attribute Characteristics of the model
The Targeted Mailing “problem” is to predict who will buy a bike. Using a training set of known bike buyers, each record is flagged with a value where 1 is assigned to people who did buy bikes and 0 is assigned to people who did not. Use the Attribute Characteristics to see how attributes compare with the population for each group – bike buyers and non-bike buyers.

§ Select the Attribute Characteristics tab, and choose 1 in the Value list
This shows the most frequent attribute states (in descending order) for bike buyers (Bike Buyer = 1). Note that Number Children at Home = 0 is at the top of the list. It is not necessarily the best predictor, because it is also high for non-bike buyers. This is an example where correlation does not imply causation.
§ Change the Value option to 0 (non-bike buyers).
Notice that Number Children at Home = 0 is still at the top of the list. The population of the sample just doesn’t have many kids.
3
Review the Attribute Discrimination of the model
Attribute Discrimination is a one-dimensional sequence of the importance of different attributes. Naïve Bayes is a fast and simple model to use to compare attributes.

§ Select the Attribute Discrimination tab, with 1 for Value 1 and 0 for Value 2:
This shows which attributes are the best predictors (discriminators) for buying bikes. Not having a car is the highest positive predictor, followed closely age in the mid-30s. Having 2 cars means you’re unlikely to buy a bike. Notice that Number of Children at Home doesn’t show up for a while.
4
Review the Dependency Network
§ Select the Dependency Network tab.

§ In the Dependency Network, click Bike Buyer.

The nodes change colors to show the prediction relationship. This becomes more useful as models become more complex. In this example, the network is very simple. All attributes here are predicting the bike buyer value. Nodes that predict BikeBuyer are indicated by color, which you can identify by the legend.

§ Click Number of Cars Owned.

Now you can see what this attribute predicts. As you can see, because this model is simple, the relationships are easy to see.

§ Gradually drag the Links bar down.
Weaker links drop off the model. The strongest predictors are Age and Number Cars Owned.

5
Browse the Decision Tree model of the Targeted Mailing mining structure of the Adventure Works DW database

Right-click TM_Decision_Tree and click Browse.
Decision Tree examines relationships in a more complex way than Naïve Bayes. It looks more closely at the interplay between attributes.
§ Choose TM_Decision_Tree in the model list.
The decision tree shows all the possible values of the strongest variable—number of cars owned. But for people who own 3 cars, the second most important factor is Income, while for people who own 1 car, the second most important factor is Age. The decision tree allows you to see how different factors interrelate to create discrete groups within the population.
§ In the Background drop-down, choose 1.
This shades the background darker based on the percent of cases that meet the condition—in this case, bike buyers.
§ Hover the mouse over the three Yearly Income boxes for Number Cars Owned = 3.
There are not as many rich people (income > 122,000) in this group, but the ratio of bike buyers is very high.
6
Browse the Association mining structure
Association is very different from the prediction models. This model is checking for which products are purchased together. Sometimes one, two, or three different products are purchased together.
Open the Market Basket structure, right-click Association and click Browse.
§ Start with the Itemsets tab.
This shows a list of “shopping carts” along with how many times each happened. Lots of people just bought a single Sport-100.
§ Change the Minimum Itemset Size to 3.
This model shows only “shopping carts” that had at least three items purchased together.
§ In the Filter Itemset box, type water.
This shows only shopping carts that included something to do with water. In this case, the sales are primarily water bottles.
§ Change to the Rules tab.
Probability just shows how often a combination occurs, but not whether one item is a good predictor of another. For a while, everybody who bought a Betty Crocker book also bought a Harry Potter book, but that doesn’t mean that purchasing a Betty Crocker book was an important predictor for purchasing a Harry Potter book.
Remember that the goal is to find which product purchases predict the others. For example, if someone bought a Road Bottle Cage and a Cycling Cap, then bought a Water Bottle, there is 100% probability that the first two strongly predict the third.
§ Sort in descending order of Importance.
This view shows the combinations that the model judges to be the most use in making predictions. Its purpose is to measure the competition between other products that could predict purchases.
§ Select the Dependency Network, drag the Links bar to the middle, and choose Water Bottle = Existing.
Notice that this dependency network is much more complex than the previous one you viewed. A hydration pack is likely to be accompanied by a water bottle, but just because you bought a water bottle, you won’t necessarily buy a Hydration Pack.
7
Compare the mining models of the Targeted Mailing mining structure
When a mining structure contains more than one mining model, you can compare their predictive ability.
§ Close the mining viewer.
§ Right-click the Targeted Mailing structure, and choose Lift Chart.
§ As the Case table (also known as Input Table), select the Case Table vTargetMail.
This is the same view that was used to generate the models, so the columns should match. A common practice is to divide the data in half and use half to train the model and the other half to test it.
§ In the Predict Value column of the grid, choose 1.
This tests how well the models predict bike buyers, rather than how the models work in general.
§ Select the Lift Chart tab.
The Red line is theoretically perfect model. The Yellow line is pure chance. The lines in between are the four models in the mining structure. The “best” model for this data appears to be Decision Tree, while the “worst” model appears to be Clustering.
Note:
You may receive a permissions error at this point. If so, implement the following workaround:
Right-click on the Roles folder underneath the AdventureWorksAS database and add a new role. Change the membership of the role by adding the ‘Everyone group.
On the Data Source Access page give read/write access to adventure_works_dw_3.0
On the Mining Structure page give read access to Targeted Mailing
Underneath Target Mailing, give read access to each Mining Model in turn.
Please note that this is not a recommended security practice.
Demonstration 2: Add DM Viewers to a Web Page
In this demonstration, you will learn how to add a data mining viewer to a web page.
#
Task
Notes
1
Run the DM Viewer application
In Visual Studio 2005:
Open the DM Viewer solution as a Web Site (File/Open/Web Site) in the folder C:\BID\Finished\08 DM\DM Viewer folder.
Press F5 to run the page.
This is a simple application that you can include in your own application with very little work. Just add the control and set the properties. It’s not beautiful, but it’s functional. You may find that the colored bars do not appear in this version of the control
2
Review the Default.aspx code
Open the Default.aspx page.
View the Design page.
Select the data mining viewer control on the page, and then review properties in the Properties window.
You can see that this application is pretty simple. DM Viewer uses a self-contained control. Just set the database, mining model, server, and add a few attributes.
3
Add a second copy of control to the Default.aspx page
§ Right-click on Toolbox window and select Choose Items.
§ Click the Browse button to add the DLL to your toolbox from C:\BID\Finished\08 DM\DM Viewer.
§ Drag the DMNaiveBayesViewer onto the Default.aspx page.
§ As the database, assign Adventure Works DW.
§ As the model, assign TM_Naive_Bayes.
§ Run the project to see the second copy of the control.

You can learn more about the controls if you first install the Analysis Services samples and then look at the Data Mining samples found in C:\Program Files\Microsoft SQL Server 2005 Samples\Analysis Services\DataMining\Data Mining Web Controls.

RAVINDRANATHREDDY - MSBI - BLOG

How to generate SSRS Report and How to deploy in MOSS

Linked server in sql server 2008

SSIS Migration from 2000 (dts) to 2008 (dtsx)

INDIA won the world cup 2011

Geospatial Data with Sql Server 2008 R2

URGENT REQUIREMENT IN A MNC FOR MSBI RESOURCE(SSIS/SSAS/SSRS)-***only in USA***

SSAS-Dynamic Cube Partition Refresh

Table Partition and Archive the OLD data from a Table - Sql Server

Happy Maha-Shivaratri to All...!

Data Architect, Data Warehouse Architect and BI Solution Architect

SSAS-Implementing Analysis Services synchronization

We Are What We Think...!

MSBI Interview Questions and Answers

Happy Valentine's Day

SSRS-Types of Reports

SSRS report Design Style-Details(***Tablix***)

SSRS Complex Expressions

Sql Server/SSAS/SSRS Discussion Forum AND Questions

!!~~~HAPPY NEW YEAR~~~!!

New Features in PerformancePoint Services (SharePoint Server 2010)

PerformancePoint Server 2007 Top Blogs

About PerformancePoint Server 2007

MSBI(SSIS,SSAS with MDX,SSRS) No 1 Training Institute && Online Training

Future MSBI-Power Pivot ‘N’ DAX

My latest Pics-Smoky mountain,tennesse

SSRS Report Deployment Procedure in Sharepoint server

SSAS-Data Security

SSAS-Data Security

**SSAS-Interview Questions**

SSAS-MDX functions

SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution

My lovely Photos

Here is my Latest Photo

SSAS - Usage Of OLAP PivotTable Extensions && PT Power

Capacity Planning for the DW/BI Environment

Sql Server Useful Date() FUNC

Datawherehouse Questions & Answers

Sql Server 2008 & MSBI 2008 - **Features &Enhancements**

SSAS-Videos

SSRS Issue with IE8-Workaround

SSRS Issue with IE8-Workaround

SSRS Issue with IE8-Workaround

SQL Server Data Warehouse basics

Basic Steps for Implementing an Analysis Services Database

I Passed **70-448** Exam

SSAS - slowly changing dimension type 2 in ssas

How to call a WCF service using SSIS

How to install Sql Server 2008

Sql Server 2005 - How to Debug SP

SSMS-Identify Backend Process in Sql Server

SSRS-***Basic Understanding about Reports***

SSRS-Developing Report Navigation and Drilldown

BIDS and TFS

I Am Here

SSRS-Examples

***All BI Blogs Details***

***SQL SERVER 2005-IMP QUERIES***

Sql Server 2005-Basic SQL Stuff

Sql Server 2005-Types of Joins

Microsoft Sql Server 2005 Interview Questions and Answers

SSAS-MDX Interview questions - Time based function-II

SSAS - MDX Query Interview Questions and Answers-I

SSIS – TOP 10 Best Practices

SSAS - Analysis Services interview questions

SSIS - XML Destination using Script Component

MCTS - I passed the 70-445 exam!

SSIS-Webservice && XML Task - Usage

MSBI-'08 Videos=>> Setup '08,SSIS,SSAS,SSAS-Data Mining,SSRS

MSBI - Useful Videos

SSRS-Table Vs Matrix

SSIS-Transactions usage

SQL SERVER – Nth Highest Salary

SSIS - How to read vaiable values directly from Table

SSIS-Performance Tuning Techniques(SSIS Engine Overview,BlockingExecution Trees, Buffer Sizing, Parallelism)

SSIS-Parallel Execution

SQL SERVER-Rebuild Indexes AND Update Statistics

SSIS - Difference between Control Flow and Data Flow

SSIS-Waiting for a file

SSRS - Adding Subreports

URGENT REQUIREMENT IN A MNC FOR MSBI RESOURCE(SSIS/SSAS/SSRS)-only in USA

SSRS report Design Style-Details(Tablix)

!!~HAPPY NEW YEAR~!!

SSAS-Interview Questions

Sql Server 2008 & MSBI 2008 - Features &Enhancements

I Passed 70-448 Exam

SSRS-Basic Understanding about Reports

All BI Blogs Details

SQL SERVER 2005-IMP QUERIES

*->MSBI-Books to read**