Q.What is SQL Server 2005 Analysis Services (SSAS)?
A.SSAS gives the business data an integrated view. This integrated view is provided by combining online analytical processing (OLAP) and data mining functionality. SSAS supports OLAP and allows data collected from various sources to be managed in an efficient way. Analysis services, specifically for data mining, allow use of a wide array of data mining algorithms that allows creation, designing of data mining models.
A.SSAS gives the business data an integrated view. This integrated view is provided by combining online analytical processing (OLAP) and data mining functionality. SSAS supports OLAP and allows data collected from various sources to be managed in an efficient way. Analysis services, specifically for data mining, allow use of a wide array of data mining algorithms that allows creation, designing of data mining models.
Q.What are the new features with SQL Server 2005 Analysis Services (SSAS)?
A.
1.It offers interoperability with Microsoft office 2007.
2.It eases data mining by offering better data mining algorithms and enables better predictive analysis.
3.Provides a faster query time and data refresh rates.
4.Improved tools – the business intelligence development studio integrated into visual studio allows to add data mining into the development tool box.
5.New wizards and designers for all major objects.
6.Provides a new Data Source View (DSV) object, which provides an abstraction layer on top of the data source specifying which tables from the source are available.
A.
1.It offers interoperability with Microsoft office 2007.
2.It eases data mining by offering better data mining algorithms and enables better predictive analysis.
3.Provides a faster query time and data refresh rates.
4.Improved tools – the business intelligence development studio integrated into visual studio allows to add data mining into the development tool box.
5.New wizards and designers for all major objects.
6.Provides a new Data Source View (DSV) object, which provides an abstraction layer on top of the data source specifying which tables from the source are available.
Q.What are SQL Server Analysis Services cubes?
A.In analysis services, cube is the basic unit of storage. A cube has data collected from various sources that enables faster execution of queries. Cubes have dimensions and measures.Example: A cube storing employee details may have dimensions like date of joining and name which helps in faster queries requesting for finding employees who joined in a particular week.
A.In analysis services, cube is the basic unit of storage. A cube has data collected from various sources that enables faster execution of queries. Cubes have dimensions and measures.Example: A cube storing employee details may have dimensions like date of joining and name which helps in faster queries requesting for finding employees who joined in a particular week.
Q.Explain the purpose of synchronization feature provided in Analysis Services 2005.
A.Synchronization feature is used to copy database from one source server to a destination server. While the synchronization is in progress, users can still browse the cubes. Once the synchronization is complete, the user is redirected to the new Synchronized database.
A.Synchronization feature is used to copy database from one source server to a destination server. While the synchronization is in progress, users can still browse the cubes. Once the synchronization is complete, the user is redirected to the new Synchronized database.
Q.Explain the new features of SQL Server 2005 Analysis Services (SSAS).
A.Unified Dimensional Model: - This model defines the entities used in the business, the business logic used to implement, metrics and calculations. This model is used by different analytical applications, spreadsheets etc for verification of the reports data.
A.Unified Dimensional Model: - This model defines the entities used in the business, the business logic used to implement, metrics and calculations. This model is used by different analytical applications, spreadsheets etc for verification of the reports data.
Data Source View: - The UML using the data source view is mapped to a wide array of back end data sources. This provides a comprehensive picture of the business irrespective of the location of data. With the new Data source view designer, a very user friendly interface is provided for navigation and exploring the data.
New aggregation functions: - Previous analysis services aggregate functions like SUM, COUNT, DISTINCT etc were not suitable for all business needs. For instance, financial organizations cannot use aggregate functions like SUM or COUNT or finding out the first and the last record. New aggregate functions like FIRST CHILD, LAST CHILD, FIRST NON-EMPTY and LAST NON-EMPTY functions are introduced.
Querying tools: - An enhanced query and browsing tool allows drag and drop dimensions and measures to the viewing pane of a cube. MDX queries and data mining extensions (DMX) can also be written. The new tool is easier and automatically alerts for any syntax errors.
Q.MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language,Explain
A.MDX in SQL server 2005 Analysis services offers CASE and SCOPE statements. CASE returns specific values based upon its comparison of an expression to a set of simple expressions. It can perform conditional tests within multiple comparisons. SCOPE is used to define the current subcube. CALCULATE statement is used to populate each cell in the cube with aggregated data.
A.MDX in SQL server 2005 Analysis services offers CASE and SCOPE statements. CASE returns specific values based upon its comparison of an expression to a set of simple expressions. It can perform conditional tests within multiple comparisons. SCOPE is used to define the current subcube. CALCULATE statement is used to populate each cell in the cube with aggregated data.
Q.Can you explain how to deploy an SSIS package?
A.A SSIS package can be deployed using the Deploy SSIS Packages page. The package and its dependencies can be either deployed in a specified folder in the file system or in an instance of SQL server. The package needs to be indicated for any validation after installation. The next page of the wizard needs to be displayed. Skip to the Finish the Package Installation Wizard page.
A.A SSIS package can be deployed using the Deploy SSIS Packages page. The package and its dependencies can be either deployed in a specified folder in the file system or in an instance of SQL server. The package needs to be indicated for any validation after installation. The next page of the wizard needs to be displayed. Skip to the Finish the Package Installation Wizard page.
Q.Can you explain the difference between the INTERSECT and EXCEPT operators?
A.INTERSECT returns data value common to BOTH queries (queries on the left and right side of the operand). On the other hand, EXCEPT returns the distinct data value from the left query (query on left side of the operand) which does not exist in the right query (query on the right side of the operand).
Example:
Table 1 has values: jim, tony, john
Table 2 has values: Tony, jason, marie
Except query:
SELECT COL1 FROM Tbl_1 EXCEPT SELECT COL1 FROM tbl_2
GO
This will return Jim, John
A.INTERSECT returns data value common to BOTH queries (queries on the left and right side of the operand). On the other hand, EXCEPT returns the distinct data value from the left query (query on left side of the operand) which does not exist in the right query (query on the right side of the operand).
Example:
Table 1 has values: jim, tony, john
Table 2 has values: Tony, jason, marie
Except query:
SELECT COL1 FROM Tbl_1 EXCEPT SELECT COL1 FROM tbl_2
GO
This will return Jim, John
Q.What is the new error handling technique in SQL Server 2005?
A.Previously, error handling was done using @@ERROR or check @@ROWCOUNT, which didn’t turn out to be a very feasible option for fatal errors. New error handling technique in SQL Server 2005 provides a TRY and CATCH block mechanism. When an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block. TRY CATCH constructs traps errors that have a severity of 11 through 19 as well.
A.Previously, error handling was done using @@ERROR or check @@ROWCOUNT, which didn’t turn out to be a very feasible option for fatal errors. New error handling technique in SQL Server 2005 provides a TRY and CATCH block mechanism. When an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block. TRY CATCH constructs traps errors that have a severity of 11 through 19 as well.
Q.What exactly is SQL Server 2005 Service Broker?
A.Servive brokers allow build applications in which independent components work together to accomplish a task. They help build scalable, secure database applications. The brokers provide a message based communication and can be used for applications within a single database. It helps reducing development time by providing an enriched environment.
A.Servive brokers allow build applications in which independent components work together to accomplish a task. They help build scalable, secure database applications. The brokers provide a message based communication and can be used for applications within a single database. It helps reducing development time by providing an enriched environment.
Q.Explain the Service Broker components.
A.Service broker components help build applications in which independent components work together to accomplish a task. These applications are independent, asynchronous and the components work together by exchanging information via messages. The service broker’s main job is to send and receive messages.
A.Service broker components help build applications in which independent components work together to accomplish a task. These applications are independent, asynchronous and the components work together by exchanging information via messages. The service broker’s main job is to send and receive messages.
Q.What is a breakpoint in SSIS? How is it setup? How do you disable it?
A.Breakpoints allow the execution to be paused in order o review the status of the data, variables and the overall status of the SSIS package. Breakpoints in SSIS are set up through the BIDS wizard. In this wizard, one needs to navigate to the control flow interface. The object where the breakpoint needs to be applied needs to be selected. Following which, Edit breakpoints can be clicked.
A.Breakpoints allow the execution to be paused in order o review the status of the data, variables and the overall status of the SSIS package. Breakpoints in SSIS are set up through the BIDS wizard. In this wizard, one needs to navigate to the control flow interface. The object where the breakpoint needs to be applied needs to be selected. Following which, Edit breakpoints can be clicked.
Q.What is XPath?
A.XPath is a language for addressing an XML document's elements and attributes. As an example, say you receive an XML document that contains the details of a shipment and you want to retrieve the element/attribute values from the XML document. You don't just want to list the values of all the nodes, but also want to output the values of specifi c elements or attributes. In such a case, you would use XPath to retrieve the values of those elements and attributes. XPath constructs a hierarchical structure of an XML document, a tree of nodes, which is the XPath data model. The XPath data model consists of seven node types.
A.XPath is a language for addressing an XML document's elements and attributes. As an example, say you receive an XML document that contains the details of a shipment and you want to retrieve the element/attribute values from the XML document. You don't just want to list the values of all the nodes, but also want to output the values of specifi c elements or attributes. In such a case, you would use XPath to retrieve the values of those elements and attributes. XPath constructs a hierarchical structure of an XML document, a tree of nodes, which is the XPath data model. The XPath data model consists of seven node types.
Q.What is Untyped XML?
A.In untyped xml data type you do not have a schema for your XML data.Although the schemas exist, they may not be validated on the server side.
At times applications perform client-side validation before storing the data at the server. Invalid XML Data may be stored temporarily according to the schema, or schema components that are not supported at the server are also used to store at the client side.
A.In untyped xml data type you do not have a schema for your XML data.Although the schemas exist, they may not be validated on the server side.
At times applications perform client-side validation before storing the data at the server. Invalid XML Data may be stored temporarily according to the schema, or schema components that are not supported at the server are also used to store at the client side.
Q.What is typed XML?
A.Typed XML columns, parameters, and variables can store XML documents or content which need to be specified at the time of declaration with a flag whether you are storing a document or content.A DOCUMENT should be specified if each XML instance has exactly one top-level element else CONTENT should be specified.
A.Typed XML columns, parameters, and variables can store XML documents or content which need to be specified at the time of declaration with a flag whether you are storing a document or content.A DOCUMENT should be specified if each XML instance has exactly one top-level element else CONTENT should be specified.
Situations to use Typed XML:
1.Typed XML data have schemas. The server validates the XML data according to the XML schemas.
2.Advantage of storage and query optimizations based on type information can be taken.
3.Advantage of type information during compilation of the queries can be taken.
2.Advantage of storage and query optimizations based on type information can be taken.
3.Advantage of type information during compilation of the queries can be taken.
Q.The XML data type comes with five methods. Explain them
A.
1.query() - method to query over an XML instance.
2.value() - method to retrieve a value of SQL type from an XML instance.
3.exist() - method to determine whether a query returns a nonempty result.
4.modify() - method to specify XML Data Modification Language statements to perform updates.
5.nodes() - method to shred XML into multiple rows, which propagates parts of XML documents into rowsets.
A.
1.query() - method to query over an XML instance.
2.value() - method to retrieve a value of SQL type from an XML instance.
3.exist() - method to determine whether a query returns a nonempty result.
4.modify() - method to specify XML Data Modification Language statements to perform updates.
5.nodes() - method to shred XML into multiple rows, which propagates parts of XML documents into rowsets.
Q.Explain with an example how to apply defaults constraint to an XML data type column.
A.An xml column type can be assigned a default XML instance in a table.
A.An xml column type can be assigned a default XML instance in a table.
It can be done in 2 ways:
1.by using an XML constant
Syntax:
CREATE TABLE T (XmlColumn xml default N'')
CREATE TABLE T (XmlColumn xml default N'')
2.by using an explicit cast to the xml type.
Syntax:
CREATE TABLE T (XmlColumn xml
default CAST(N'' AS xml))
CREATE TABLE T (XmlColumn xml
default CAST(N'' AS xml))
SQL Server also supports NULL and NOT NULL constraints on columns of xml type.
Example:CREATE TABLE T (XmlColumn xml NOT NULL)
Q.Explain the concepts of indexing XML data in SQL Server 2005.
A.SQL Server 2005 supports four different types of XML indexes.The primary XML index on an XML column is a clustered index on an internal table known as the node table. It cannot be used directly from the T-SQL statements. The primary XML index is a B+tree.The primary XML index contains one row for each node in the XML instance.SQL Server 2005 executes a SQL query that contains an XML data type method.
A.SQL Server 2005 supports four different types of XML indexes.The primary XML index on an XML column is a clustered index on an internal table known as the node table. It cannot be used directly from the T-SQL statements. The primary XML index is a B+tree.The primary XML index contains one row for each node in the XML instance.SQL Server 2005 executes a SQL query that contains an XML data type method.
When an SQL-with-XML query is executed against a table containing an XML data type column, the query must process every XML instance in every row. At the top level, there are two ways that such a query can be executed Select the rows in the base table (that is, the relational table that contains the XML data type column) that qualify first, and then process each XML instance using XQuery. This is known as top-down query processing.
Process all XML instances using the XQuery first, and then join the rows that qualify to the base table. This is known as bottom-up query processing.
Process all XML instances using the XQuery first, and then join the rows that qualify to the base table. This is known as bottom-up query processing.
Q.Provide basic syntax for creating index on XML data type column.
A.Syntax to create a primary index
A.Syntax to create a primary index
CREATE PRIMARY XML INDEX idx_xCol on T (xCol)
Q.What is content indexing/full text indexing?
A.A full text index indexes the entire XML instance and ignores the values, nodes and other XML syntax.
Only one content index is allowed per table.A content index is applied to a column and not a table.When an XMl index is applied, the index is applied first and then the XQuery is applied. For a table to which a content index is applied, a unique primary key column must already have been defined on the table for which the index is created.
A.A full text index indexes the entire XML instance and ignores the values, nodes and other XML syntax.
Only one content index is allowed per table.A content index is applied to a column and not a table.When an XMl index is applied, the index is applied first and then the XQuery is applied. For a table to which a content index is applied, a unique primary key column must already have been defined on the table for which the index is created.
Q.Explain the reason to index XML data type column.
A.Index building avoids parsing the whole data at run time and benefits index lookups for efficient query processing.
A.Index building avoids parsing the whole data at run time and benefits index lookups for efficient query processing.
XML indexes can be created on xml data type columns. They index all tags, values and paths over the XML instances in the column and benefit query performance.
Application can benefit from an XML index in the following situations:
1.When queries on XML columns are a common workload.
2.When XML index maintenance cost during data modification must be considered.
3.When XML values are relatively large and the retrieved parts are relatively small.
2.When XML index maintenance cost during data modification must be considered.
3.When XML values are relatively large and the retrieved parts are relatively small.
Q.What are the guidelines to be adhered when creating a XML index?
A.A primary index can be created on only one XML data type column.Primary keys of a table cannot be modified if a primary key exists on a same table.A table cant have XMl primary index and non-XML index with the same name.A table on which a primary key is being created needs to have a clustered index created on it.An XML index needs to be dropped before changing datatype column from typed to untyped.Indexes cannot be created on XML type or views with XML type column.XMl index should be created on the same file or partition as the table.
A.A primary index can be created on only one XML data type column.Primary keys of a table cannot be modified if a primary key exists on a same table.A table cant have XMl primary index and non-XML index with the same name.A table on which a primary key is being created needs to have a clustered index created on it.An XML index needs to be dropped before changing datatype column from typed to untyped.Indexes cannot be created on XML type or views with XML type column.XMl index should be created on the same file or partition as the table.
No comments:
Post a Comment