IS NULL

Fact and Dimension Relationship

2016-09-18T20:36:00.002+05:30

Following are the four different types of relationships between a Cube Dimension and a Measure Group:

Regular:
In a Regular relationship, primary key column of a dimension is directly connected to the fact table. This type of relationship is similar to the relationship between a dimension and a fact in a Star Schema, and it can be based on either the physical primary key-foreign key relationship in the underlying relational database or the logical primary key-foreign key relationship defined in the Data Source View.
Referenced:
In a Referenced relationship, primary key columns of a dimension is indirectly connected to the fact table through a key column in the intermediate dimension table. This type of relationship is similar to the indirect relationship between a dimension and a fact, through an intermediate dimension, in a Snowflake Schema.
Fact:
In a Fact relationship, the dimension table and the fact table are one and the same. Basically a Fact Dimension or Degenerate Dimensionis created using one or more columns from the fact table and this degenerate dimension is used while defining/establishing the relationship in case of a fact relationship.
Many-to-Many:
In a Many-to-Many relationship, a dimension is indirectly connected to a Measure Group through an intermediate fact table which joins with the dimension table. It is analogous to a scenario, where one project can have multiple project managers and one project manager can manage multiple projects.

SSIS FAQ

2015-11-07T21:21:00.000+05:30

What do we mean by dataflow in SSIS?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.

• What is a breakpoint in SSIS? How is it setup? How do you disable it?
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.

• Can you name 5 or more of the native SSIS connection managers?

1) OLEDB connection – Used to connect to any data source requiring an OLEDB connection (i.e.,
SQL Server 2000)
2) Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
3) ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other
connection exposed through managed code (like C#) in a custom task
4) Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
5) File connection – Used to reference a file or folder. The options are to either use or create a file or folder
6) Excel

• What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.

• What is Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions. For example, we can organize the transform for the students in a class who have marks greater than 40 to one path and the students who score less than 40 to another path.

• How do you eliminate quotes from being uploaded from a flat file to SQL Server?
This can be done using TEXT QUALIFIER property. 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.

• Can you explain how to setup a checkpoint file in SSIS?
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.

• What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution:
1) Never: The package will not use a checkpoint file and therefore will never restart.
2) If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
3) Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

• What is the ONLY Property you need to set on TASKS in order to configure CHECKPOINTS to RESTART package from failure?
The one property you have to set on the task is FailPackageOnFailure. This must be set for each task or container that you want to be the point for a checkpoint and restart. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.

• Where can we set the CHECKPOINTS, in DataFlow or ControlFlow ?
Checkpoints only happen at the Control Flow; it is not possible to checkpoint transformations or restart inside a Data Flow. The Data Flow Task can be a checkpoint, but it is treated as any other task.

• Can you explain different options for dynamic configurations in SSIS?
1) XML file
2) custom variables
3) Database per environment with the variables
4) Use a centralized database with all variables

• What is the use of Percentage Sampling transformation in SSIS?
Percentage Sampling transformation is generally used for data mining. This transformation builds a random sample of set of output rows by choosing specified percentage of input rows. For example if the input has 1000 rows and if I specify 10 as percentage sample then the transformation returns 10% of the RANDOM records from the input data.

• What is the use of Term Extraction transformation in SSIS?
Term Extraction transformation is used to extract nouns or noun phrases or both noun and noun phrases only from English text. It extracts terms from text in a transformation input column and then writes the terms to a transformation output column. It can be also used to find out the content of a dataset.

• What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at runtime. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the runtime and how it looks like after the transformation occurred. The different types of data viewers are:

1. Grid
2. Histogram
3. Scatter Plot
4. Column Chart.

• What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.

• Which are the different types of Control Flow components in SSIS?
The different types of Control Flow components are: Data Flow Tasks, SQL Server Tasks, Data Preparation Tasks, Work flow Tasks, Scripting Tasks, Analysis Services Tasks, Maintenance Tasks, Containers.

• What are containers? What are the different types of containers in SSIS?
Containers are objects that provide structures to packages and extra functionality to tasks. There are four types of containers in SSIS, they are: Foreach Loop Container, For Loop Container, Sequence Container and Task Host Container.

• What are the different types of Data flow components in SSIS?
There are 3 data flow components in SSIS.
1. Sources
2. Transformations
3. Destinations

• What are the different types of data sources available in SSIS?
There are 7 types of data sources provided by SSIS: a.) Data Reader source b.) Excel source c.) Flat file source d.) OLEDB source e.) Raw file source f.) XML source g.) Script component

• What is SSIS Designer?
It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.

• What is Control Flow tab?
It is the tab in SSIS designer where various Tasks can be arranged and configured. This is the tab where we provide and control the program flow of the project.

• What is Data Flow tab?
This is the tab where we do all the work related to ETL job. It is the tab in SSIS Designer where we can extract data from sources, transform the data and then load them into destinations.

• What is the function of control flow tab in SSIS?
On the control flow tab, the tasks including dataflow task, containers and precedence constraints that connect containers and tasks can be arranged and configured.

• What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when ANY task Failes or Stops or Starts ..

• What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.

• What is Solution Explorer?
It is a place in SSIS Designer where all the projects, Data Sources, Data Source Views and other miscellaneous files can be viewed and accessed for modification.

• How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.

• How are variables useful in ssis package?
Variables can provide communication among objects in the package. Variables can provide communication between parent and child packages. Variables can also be used in expressions and scripts. This helps in providing dynamic values to tasks.

• Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying TSQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in datagrid, and also operations for each Output Alias in Operation columns of the same datagrid. Some of operation functions listed below :
• Group By
• Average
• Count
• Count Distinct : count distinct and non null column value
• Min, Max, Sum
In Advanced tab, you can do some optimization here, such as setting up Key Scale option (low, medium, high), Count Distinct scale option (low, medium, high), Auto Extend factor and Warn On Division By Zero. If you check Warn On Division By Zero, the component will give warning instead of error. Key Scale option will optimize transformation cache to certain number of key threshold. If you set it low, optimization will target to 500,000 keys written to cache, medium can handle up to 5 million keys, and high can handle up to 25 million keys, or you can specify particular number of keys here. Default value is unspecified. Similar to number of keys for Count Distinct scale option. It is used to optimize number of distinct value written to memory, default value is unspecified. Auto Extend Factor is used when you want some portion of memory is used for this component. Default value is 25% of memory.

• Explain Audit Transformation ?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
1. Execution of Instance GUID : ID of execution instance of the package
2. PackageID : ID of the package
3. PackageName
4. VersionID : GUID version of the package
5. Execution StartTime
6. MachineName
7. UserName
8. TaskName
9. TaskID : uniqueidentifier type of the data flow task that contains audit transformation.

• Explain Character Map Transformation?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are:
1. Uppercase
2. Lowercase
3. Byte reversal : such as from 0×1234 to 0×4321
4. Full width
5. Half width
6. Hiragana/katakana/traditional Chinese/simplified Chinese
7. Linguistic casing

• Explain Conditional split Transformation ?
It functions as if…then…else construct. It enables send input data to a satisfied conditional branch. For example you want to split product quantity between less than 500 and greater or equal to 500. You can give the conditional a name that easily identifies its purpose. Else section will be covered in Default Output Column name.
After you configure the component, it connect to subsequent transformation/destination, when connected, it pops up dialog box to let you choose which conditional options will apply to the destination transformation/destination.

• Explain Copy column Transformation?
This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.

• Explain Data conversion Transformation?
This component does conversion data type, similar to TSQL function CAST or CONVERT. If you wish to convery the data from one type to another then this is the best bet. But please make sure that you have COMPATABLE data in the column.

• Explain Data Mining query Transformation?
This component does prediction on the data or fills gap on it. Some good scenarios uses this component is:
1. Take some input columns as number of children, domestic income, and marital income to predict whether someone owns a house or not.
2. Take prediction what a customer would buy based analysis buying pattern on their shopping cart.
3. Filling blank data or default values when customer doesn’t fill some items in the questionnaire.

• Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.

• Explain Merge Transformation?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions :
1. Data should be in sorted order
2. Data type , data length and other meta data attribute must be similar before merged.

• Explain Merge Join Transformation?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.

• Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.

• Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.

• Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.

• Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.

• What r the possible locations to save SSIS package?
You can save a package wherever you want.
SQL Server
Package Store
File System

• What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.

• What is a workflow in SSIS?
A workflow is a set of instructions on how to execute tasks.
(It is a set of instructions on how to execute tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.

• What is the diff between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process activities of data flow and other processes within a package.
When we want to extract, transform and load data within a package. You add an SSIS dataflow task to the package control flow.

• What are the main component of SSIS(project-architecture)?
SSIS archItecture has 4 main components
1.ssis service
2.ssis runtime engine & runtime executables
3.ssis dataflow engine & dataflow components
4.ssis clients

• Different components in SSIS package?
Control flow
Data flow
Event handler
Package explorer

• What are Connection Managers?
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.

• What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.

• How to provide securIty to packages?
We can provide security in two ways
1. Package encryption
2. Password protection.

• What are Precedence constraints?
Constraints that link executable, container, and tasks wIthin the package control flow and specify condItion that determine the sequence and condItions for determine whether executable run.

• What is Design time Deployment in SSIS ?

When you run a package from with in BIDS,it is built and temporarily deployed to the folder. By default the package will be deployed to the BIN folder in the Package’s Project folder and you can configure for custom folder for deployment. When the Package’s execution is completed and stopped in BIDS,the deployed package will be deleted and this is called as Design Time Deployment.

What are types of storage modes and Explain

2014-11-29T20:33:00.000+05:30

There are three standard storage modes in OLAP applications

1. MOLAP

2. ROLAP

3. HOLAP

MOLAP

MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.

Advantages:

· Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.

· The data is compressed so it takes up less space.

· And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.

· Cube browsing is fastest using MOLAP.

ROLAP

ROLAP (Relational Online Analytical Processing) : ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.

Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

Advantages:

· Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.

· Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.

· Low latency.

HOLAP

Hybrid Online Analytical Processing (HOLAP): HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Advantages:

· HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.

· Cubes are smaller than MOLAP since the detail data is kept in the relational database.

· Processing time is less than MOLAP since only aggregations are stored in multidimensional format.

· Low latency since processing takes place when changes occur and detail data is kept in the relational database.

What is use of AttributeHierarchyOrdered ?

2014-09-01T23:36:00.001+05:30

Determines whether the associated attribute hierarchy is ordered. The default value is True.

if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.

SSAS AttributeHierarchyEnabled

2014-08-10T22:34:00.002+05:30

The value of the AttributeHierarchyEnabled property determines whether an attribute hierarchy is created. If this property is set to False, the attribute hierarchy is not created and the attribute cannot be used as a level in a user hierarchy; the attribute hierarchy exists as a member property only. However, a disabled attribute hierarchy can still be used to order the members of another attribute. If the value of the AttributeHierarchyEnabled property is set to True, the value of the AttributeHierarchyVisible property determines whether the attribute hierarchy is visible independent of its use in a user-defined hierarchy.

SSAS AttributeHierarchyDisplayFolder

2014-08-10T22:22:00.002+05:30

The AttributeHierarchyDisplayFolder property serves as a point of identification for the folder in which we intend to display the associated attribute hierarchy to information consumers.

what is hierarchy and types of hierarchy ?

2014-07-29T11:26:00.000+05:30

A hierarchy is a very important part in OLAP Cube. Hierarchy is an Arrangement of Attributes in a dimension to improve the performance of Analysis and allows users to roll up attributes from lower level to upper level or in another word to drill down attributes from summary levels down to detail levels.

Natural Hierarchy – When we create a user-defined hierarchy, we define relationship between hierarchy levels. It helps the Analysis Services to create useful aggregations which in turn increases query performance. When levels in a hierarchy are linked in a natural relationship like one-to-one or many-to-one, such hierarchies are known as Natural Hierarchies. Like in a Calendar hierarchy, Day level related to the month level, month level to the Quarter and so on.

Non-Natural Hierarchy - In some type of hierarchies the natural relationship is lacking between the hierarchy levels and branches of the hierarchy descend to different levels. Such hierarchies are known as Non-Natural or Unbalanced hierarchies. For example, the Marital Status à Gender hierarchy is a non-natural hierarchy as Marital Status and gender attributes do not have a relationship to each other.
Ragged Hierarchies - Are the type of unbalanced hierarchies wherein, the logical parent member of at least one member is not in the level immediately above the member. In this case the hierarchy descends to different levels for different drilldown paths. Take an example of geographic hierarchy. The levels available are Country à State / Province à City. But some countries like Greece do not have provinces thus creating a ragged hierarchy.

In dimension table supporting a ragged hierarchy, the logically missing members can be represented in using nulls or empty strings, or they can contain the same value as their parent to serve as a placeholder.

Parent-Child Hierarchies – When an attribute in a dimension table has the parent attribute which is related using a self-referencing relationship, it is known as Parent-Child Hierarchy. Such hierarchies are constructed from a single parent attribute. Please refer to the DimEmployee dimension schema in the AdventureWorks2008 database. The ParentEmployeeKey column in the table is related with EmployeeKey primary key column with foreign key relationship. This means each record in a table is related with another record in the same table through a parent-child relationship. In this kind of structure the data can be derived using self-join.

What are the differnces between a RDBMS schema and a data warehouse schema?

2014-07-27T22:48:00.003+05:30

RDBMS Schema

Used for OLTP systems
Highly Normalized
Difficult to understand and navigate
Difficult to extract and solve complex problems

DWH Schema

Used for OLAP systems
De-normalized
Easy to understand and navigate
Relatively easier in extracting the data and solving complex problems

What is use of IsAggregatable property?

2014-07-17T23:08:00.000+05:30

In Analysis Service we generally see all dimension has All member. This is because of IsAggregatable property of the attribute. You can set its value to false, so that it will not show All member. Its default member for that attribute. If you hide this member than you will have to set other attribute value to default member else it will pick some value as default and this will create confusion in browsing data if someone is not known to change in default member

How will you make an attribute not process in ssas?

2014-07-17T23:05:00.001+05:30

By selecting “ AttributeHierarchyEnabled = False”, we can make an attribute not in process.

How will you hide an attribute in ssas ?

2014-07-17T22:58:00.000+05:30

We can hide the attribute by selecting “AttributeHierarchyVisible = False” in properties of the attribute.

What are the types of database schema?

2014-07-17T22:41:00.001+05:30

They are 3 types of database schema they are

Star
Snowflake
Starflake

Star schema:

In star schema fact table will be directly linked with all dimension tables. The star schema’s dimensions are denormalized with each dimension being represented by a single table. In a star schema a central fact table connects a number of individual dimension tables.

Snowflake:

The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. In snow flake schema fact table will be linked directly as well as there will be some intermediate dimension tables between fact and dimension tables.

Star flake:

A hybrid structure that contains a mixture of star(denormalized) and snowflake(normalized) schema’s.

How many types of dimensions?

2014-07-17T22:38:00.001+05:30

They are 4 types of dimensions:

Conformed dimension
Junk dimension
Degenerate attribute
Role-playing dimension

Conformed dimension:

It is the dimension which is sharable across the multiple facts or data model. This is also called as Role Playing Dimensions.

Junk dimension:

A number of very small dimensions might be lumped (a small irregularly shaped) together to form a single dimension, a junk dimension – the attributes are not closely related. Grouping of Random flags and text attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.

Degenerated dimension:

In this degenerate dimension contains their values in fact table and the dimension id not available in dimension table. Degenerated Dimension is a dimension key without corresponding dimension.

Role-playing dimension:

Dimensions which are often used for multiple purposes within the same database are called role-playing dimensions. For example, a date dimension can be used for “date of sale", as well as "date of delivery", or "date of hire".

Why use a SSAS cube?

2014-07-16T23:23:00.003+05:30

1. Speed: Aggregating (Summarizing) the data for performance: During cube processing SSAS will pre-calculate and physically stores aggregations of facts (Amounts, Quantities, Money). These aggregations, for example Turnover by Year and Region, are used when a business user queries the cube for this type of information. Therefore the query response time can be very short. When the query is fired SSAS does not have to calculate the outcome from the underlying details (like T-SQL has to do), but can take the values directly from the stored aggregations. Besides that SSAS stores query-results in a cache. So the next time the same type of query is fired, it will try to get it from the cache. Speed is especially important for a dashboard that an executive is using to slice-and-dice as any mouse click that takes more than a few milliseconds to return data will draw complaints

2. Multidimensional analysis – slice, dice, drilldown: This very much depends on the tool or front end that is layered over the data, but the idea is that you can very quickly navigate around the data, finding trends, spotting patterns, ‘drilling down’, ‘slicing and dicing’ – all key to the concept of cubes. Allowing the user to intuitively ‘wander’ around the data, not even realising that they performing analysis

3. Multiple data intergration. On a cube you can easily use multiple data sources and do minimal work with many automated tasks (especially when you use SSIS) to intergrate them on a single analysis system.

4. Can store Hierarchies

5. Built-in advanced time-calculations – i.e. 12-month rolling average: It’s very easy to implement advanced time calculations like 12-month rolling average, year-to-date and references to parallel periods in previous years. This is typically the stuff decision-makers in the organisation want to have. Imagine how many T-SQL queries are required for calculating rolling averages for each of the previous 12 months (2009-May..2010 April) => 12. Using the cube as a datasource => Only 1

6. Easily use Excel to view data via Pivot Tables

7. Security: You can use the security setting to give end-users access to only those parts (slices) of the cube relevant to them

8. Automatically handles Slowly Changing Dimensions (SCD)

9. Built-in support for KPI’s

10. Ability to automatically link and display the records that make up an aggregation (“Show Details”)

11. Support for drillthrough actions such as generating an SSRS report or linking to a URL based on the value selected

12. Can use Analysis Services Data Mining

More info:

What are the advantages of using BI Cubes over a regular Warehouse?
Beginner questions: Benefits of using an AS cube, over just database & SSRS?
Why Use SSAS?
Why Analysis Services?
Back to basics: Why do you need OLAP cubes?

SSAS - SQL Server Analysis Services Interview Questions / FAQs

2014-07-12T19:30:00.000+05:30

SSAS - SQL Server Analysis Services

Q: What is Analysis Services? List out the features?
Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting we design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets we design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard
data mining algorithms.
Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence also including reporting features.
Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.
Some of the key features are:

Ease of use with a lot of wizards and designers.
Flexible data model creation and management
Scalable architecture to handle OLAP
Provides integration of administration tools, data sources, security, caching, and reporting etc.
Provides extensive support for custom applications

Q: What is UDM? Its significance in SSAS?
The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the construction of the UDM, additional benefits accrue from the richness of metadata that the model can provide.
The UDM provides the following benefits:
• Allows the user model to be greatly enriched.
• Provides high performance queries supporting interactive analysis, even over huge data volumes.
• Allows business rules to be captured in the model to support richer analysis.

Q: What is the need for SSAS component?

Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations.
SSAS is very easy to use and interactive.
Faster Analysis and Troubleshooting.
Ability to create and manage Data warehouses.
Apply efficient Security Principles.

Q: Explain the TWO-Tier Architecture of SSAS?

SSAS uses both server and client components to supply OLAP and data mining functionality BI Applications.
The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services implemented as a separate instance of the Windows service.
Clients communicate with Analysis Services using the standard the XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as a web service.

Q: What are the components of SSAS?

An OLAP Engine is used for enabling fast ad hoc queries by end users. A user can interactively explore data by drilling, slicing or pivoting.
Drilling refers to the process of exploring details of the data.
Slicing refers to the process of placing data in rows and columns.
Pivoting refers to switching categories of data between rows and columns.
In OLAP, we will be using what are called as Dimensional Databases.

Q: What is FASMI ?
A database is called a OLAP Database if the database satisfies the FASMI rules :

Fast Analysis– is defined in the OLAP scenario in five seconds or less.
Shared – Must support access to data by many users in the factors of Sensitivity and Write Backs.
Multidimensional – The data inside the OLAP Database must be multidimensional in structure.
Information – The OLAP database Must support large volumes of data..

Q: What languages are used in SSAS ?

Structured Query Language (SQL)
Multidimensional Expressions (MDX) - an industry standard query language orientated towards analysis
Data Mining Extensions (DMX) - an industry standard query language oriented toward data mining.
Analysis Services Scripting Language (ASSL) - used to manage Analysis Services database objects.

Q: How Cubes are implemented in SSAS ?

Cubes are multidimensional models that store data from one or more sources.
Cubes can also store aggregations
SSAS Cubes are created using the Cube Wizard.
We also build Dimensions when creating Cubes.
Cubes can see only the DSV( logical View).

Q: What is the difference between a derived measure and a calculated measure?
The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

Q: What is a partition?
A partition in Analysis Services is the physical location of stored cube data. Every cube has at least one partition by default. Each time we create a measure group, another partition is created. Queries run faster against a partitioned cube because Analysis Services only needs to read data from the partitions that contain the answers to the queries. Queries run even faster when partition also stores aggregations, the pre calculated totals for additive measures. Partitions are a powerful and flexible means of managing cubes, especially large cubes.

Q: While creating a new calculated member in a cube what is the use of property
called non-empty behavior?
Nonempty behavior is important property for ratio calculations. If the denominator Is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then The expression is also treated as empty and no error is returned.

Q: What is a RAGGED hierarchy?
Under normal circumstances, each level in a hierarchy in Microsoft SQL Server 2005 Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.

Q: What are the roles of an Analysis Services Information Worker?
The role of an Analysis Services information worker is the traditional "domain expert" role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.

Q: What are the different ways of creating Aggregations?
We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO – Usage Based Optimizations. Always, prefer UBO method in realtime performance troubleshooting.

Q: What is WriteBack? What are the pre-conditions?
The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.

Q: What is processing?
Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

Q: Name few Business Analysis Enhancements for SSAS?
The following table lists the business intelligence enhancements that are available in Microsoft SQL Server 2005 Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

Enhancement	Type	Applied to	No data source
Time Intelligence	Cube	Cube	No
Account Intelligence	Dimension	Dimension or cube	No
Dimension Intelligence	Dimension	Dimension or cube	Yes
Custom Aggregation	Dimension	Dimension (unary operator) or cube	No
Semiadditive Behavior	Cube	Cube	Yes>
Custom Member Formula	Dimension	Dimension or cube	No
Custom Sorting and Uniqueness Settings	Dimension	Dimension or cube	Yes
Dimension Writeback	Dimension	Dimension or cube	Yes

Q: What MDX functions do you most commonly use?
This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

Q: Where do you put calculated members?
The reflexive answer is “in the Measures dimension” but this is the obvious answer. So I always follow up with another question. “If you want to create a calculated member that intersects all measures, where do you put it?” A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.” If they can answer it, I immediately ask them why. The answer is “Because a member in a dimension cannot intersect its own relatives in that dimension.”

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 ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

Q: How in MDX query can I get top 3 sales years based on order quantity?

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 1
FROM [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 1
FROM [Adventure Works];

Q: How do you extract first tuple from the set?

Use could usefunction Set.Item(0)
Example:

SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]

Q: How can I setup default dimension member in Calculation script?

You can use ALTER CUBE statement. Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';

SQL Server Reporting Services(SSRS) Interview Questions Part-2

2014-07-12T19:28:00.001+05:30

How do you reference an embedded function in a report expression?
Use the Code prefix and the name of the function to reference an embedded function in a report expression.
Which of the following are valid options for deploying a report? (Choose all that apply.)
a. With BIDS
b. With the Computer Management console
c. With the .nET START command
d. With SSMS
e. With Report Manager
The correct answers are a and e, BIDS and Report Manager.
Why should you not overwrite a shared data source in production?
You should not overwrite a production-shared data source because the administrator has probably already changed some connection string properties.
Can you edit a report that an end user created by using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.
How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?
Use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.
In which processing mode of a report viewer control can you use the full functionality of your report server?
You should use the remote processing mode to use the full functionality of your report server.
What types of roles are available in SSRS 2008, and what are their purposes?
Item-level roles and system-level roles are the two types of roles available in SSRS 2008. An item-level role is a collection of tasks related to operations on an object of the report object hierarchy of SSRS 2008. A system-level role is a collection of tasks related to operations on server objects outside the report object hierarchy of SSRS 2008.
Can a user or group belong to more than one item-level or system-level role?
Yes, in SSRS 2008, a user or group can have more than one association to a system-level or an item-level role.
When storing the credentials of a data source in the server, are those credentials safe?
Yes, the data source credentials are safe because Reporting Services encrypts them and stores them in the ReportServer SQL Server database.
What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value?
If you do not specify a parameter value in a subscription and the parameter does not have a default value, the execution of the report will fail.
You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong?
When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own ReportServer database to execute a report on a schedule.
What can you do if your query with properties for a data-driven subscription does not provide values for all of the subscription properties?
If your query with properties for a data-driven subscription does not provide values for all of the subscription properties, you can use text and default values instead. These values are then used for parameters of all subscriptions you get from the query.
What mechanisms do you identify to reduce the overhead of Reporting Services data sources?
Snapshots and cached reports can help reduce the processing pressure on data sources and improve report response time.
Can you always create a cache of a report?
No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.
Can you edit the .rdl code associated with a linked report?
No, because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.
Which of the tools and utilities described in this lesson can change or create the virtual directories for the report server and Report Manager after installation?
Only Reporting Services Configuration Manager can enable and name the virtual directories for the report server and Report Manager.
What is the file name extension for an encryption key backup?
Encryption key backups have an .snk file name extension.
What are the three SSRS command-line utilities and their primary functions?
RSConfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup; RS.exe runs Report Server Script files that can perform report deployment and management operations.
If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that?
The visibility property for any of the rendering devices can be changed by modifying the RSReportServer.config file and locating the tag for the specific device.

SQL Server Reporting Services(SSRS) Interview Questions Part-1

2014-07-12T19:27:00.001+05:30

You want to include an image in a report. How do you display the Image Properties dialog box?
When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens.
You want to configure an amount to display the value in a currency format. Which property do you use?
To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.
What are data regions?
Data regions are report items that display repeated rows of summarized information from datasets.
You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?
No, the Report Wizard lets you create only tabular and matrix reports. you must create the chart report directly by using the Report Designer.
You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?
You can right-click the project in Solution Explorer and then change the Target-Server URL property.
Which rendering formats are affected by the PageSize properties?
Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.
Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.
You want to use a perspective in an MDX query. How do you select the perspective?
Use the Cube Selector in the MDX Query Designer to select a perspective.
Can you use data mining models in SSRS?
Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.
You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?
Create a text box item, set the action to Go To URL, and then configure the URL.
You want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?
Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the SubCategory item.
You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web?
No, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.
What is the main difference between a Matrix report item and a Table report item?
The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.
When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?
From a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.
How do you configure a running aggregate in SSRS?
You can use the RunningValue function to configure a running aggregate.
What is the main purpose of a report parameter?
The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.
What is the main purpose of a query parameter?
The main purpose of a query parameter is to filter data in the data source.
You want your users to select a parameter from a list of values in a list box. How should you configure the parameter?
You should create a data source that contains the possible values and then bind the data source to the parameter.
What is the main benefit of using embedded code in a report?
The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.
What programming language would you use to create embedded functions in SSRS?
An SSRS report supports only visual Basic .nET embedded code.

SSRS - SQL Server Reporting Services Interview Questions / FAQs

2014-07-12T19:23:00.000+05:30

What is a dataset and what are the different types of datasets?

A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.

What are parameterized reports? What are cascading parameters in SSRS reports?

Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.

What is Tablix?

A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings

What is the report builder?

Report builder is used to create small reports and it a define interface. You can’t change the

report interface in report builder it pre designed. You can just drag columns in the report.

Report builder creates reports on database objects available with report model project.

In which SQL Server version report builder introduced?

Report builder introduced in SQL Server 2005. While creating or deploying report model

project on report server you can get error or it might not get created. For this you need to

check whether the service pack 22 is installed or not.

How to deploy the Report?

Report can be deployed in three ways.

1. Using visual studio: In visual studio you can directly deploy the report through

solution explorer by providing the report server URL in project properties at Target

Server URL. This will deploy entire project or single report as per you selection.

2. Using report server: Can directly go to the report server and deploy the report by

browsing the report from the disk location on server.

3. Creating the utility: SQL server provides the utility using that which can be used to

create a customize utility for your report deployment in bulk.

What is RS.exe utility?

Rs.exe utility is used for deploying the report on report server. It comes with the report

server and can be customize accordingly.

What is the name of reporting services config file and what’s it’s used for?

Reporting service config file is used for report configuration details. It contains the report

format and also the report import types. Report service config reside at ISS.

What are the three different part of RDL file explain them?

In visual studio RDL files has three parts.

1. Data: It contains the dataset on which we write the query. Data set is connected with

data source.

2. Design: In design you can design report. Can create tables and matrix reports. Drag

columns values from source.

3. Preview: to check the preview after the report run.

Which language rdl files made of?

RDL files are written in XML.

What is the chart in report?

Chart reports are for graphical representation. You can get pie charts columns harts and

various other options.

3d charts are also available in reporting services.

What is Data Set in report?

Data set are the set of data which we want to show in report. Data creates on data source.

Data source is the source of data from where we are getting this data i.e. database server and

database name connection string.

What are the different types of data sources in SSRS?

SSRS use different data source. Some of them are listed below.

1. Microsoft SQL Server

2. OLEDB 3. Oracle

4. ODBC

5. SQL Server Analysis Service

6. Report Server Model

7. SAP Net weaver BI

8. Hyperion

9. Teradata

10. XML

What is the web service used for reporting services?

Reporting Service Web Service used in SSRS. By accessing this web service you can access all

report server component and also get the report deployed on report server.

How to add the custom code in Report?

To add the custom codes in report go to report tab on top then properties and there you will

find the options for custom code.

What are sub reports and how to create them?

A sub report is like any other reports which can be called in main report and can be generate

through main report. Parameters can be passed from main report to sub report and basis of

that report can be generated.

What is the report model project?

Report model project is for creating Adhoc reporting. You can create the adhoc reports

through report builder. Report model project can be created on bids or report server. This

model can have simple view. And using

What is report server project?

Report Server Project contains the RDL file and it need to be deployed on report server to

view the report files to application and user.

It a solution where we design our reports. You can add it by going into BIDS clicking on

new item and then selecting reports server project. Once the solution is created you can start

creating reports.

Advance SSRS Interview Questions / FAQs

SQL Server Reporting Services(SSRS)?

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

Architecture of SSRS:

-Admin

Q What are the three stages of Enterprise Reporting Life Cycle ?

a. Authoring

b. Management

c. Access and Delivery

Q What are the components included in SSRS?

1. A Complete set of Tools that can be used to create, manage and view reports

2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.

3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

Q What is the benefit of using embedded code in a report?

1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions

2. Centralized code: helps in better manageability of code.

Q Which programming language can be used to code embedded functions in SSRS?

Visual Basic .NET Code.

Q Important terms used in the reporting services?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.

a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

Q8. what are the Command Line Utilities available In Reporting Services?

· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing

· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access

· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.

Q. How to know Report Execution History?

ExecutionLog table in ReportServer database store all the logs from last two months.

SELECT * FROM ReportServer.dbo.ExecutionLog

Q. What is difference between Tablular and Matrix report?

OR What are the different styles of reports?

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

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:

a. One group of data is displayed across the page.

b. One group of data is displayed down the page.

c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.

d. One group of data is displayed as the "filler" of the cells.

Martix reports can be considered more of a Pivot table.

Q. How to create Drill-through reports?

Using Navigation property of a cell and setting child report and its parameters in it.

Q. How to create Drill-Down reports?

To cut the story short:

- By grouping data on required fields

-Then toggle visibility based on the grouped filed

Q. What is drill-down and drill-through in SSRS?

Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report

Data warehouse FAQ

2014-07-10T22:51:00.001+05:30

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 do not care.

What is BUS Schema?

BUS Schema consists of a master suite of confirmed dimension and standardized definition of facts.

What is a Star Schema?

Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What Snow Flake Schema?

In 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.

Differences between the Star and Snowflake Schema?

Star schema: A single fact table with N number of dimensions; all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.

Snow schema: Any dimension with extended dimensions is known as snowflake schema; dimensions maybe interlinked or may have one-to-many relationship with other tables. This schema is normalized, and results in complex join leading very complex query (as well as slower results).

What is Difference between ER Modeling and Dimensional Modeling?

ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is Degenerate Dimension Table?

If a table contains values, which are neither dimension nor measures, then it is called a degenerate dimension table.

Why is Data Modeling Important?

Data modeling is probably the most labor intensive and time consuming part of the development process. 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.

In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. In data modeling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure.

Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video. (Reference: Wikipedia)

What is a Surrogate Key?

A 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 should be unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.

What is Junk Dimension?

A number of very small dimensions may get lumped together to form a single dimension, i.e. a junk dimension – the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.

What is a Data Mart?

A data mart (DM) is a specialized version of a data warehouse (DW). Like data warehouses, data marts contain a snapshot of operational data that helps business people to strategize based on analyses of past trends and experiences. The key difference is that the creation of a data mart is predicated on a specific, predefined need for a certain grouping and configuration of select data. A data mart configuration emphasizes easy access to relevant information (Reference: Wiki). Data Marts are designed to help the manager make strategic decisions about their business.

What is the Difference between OLAP and Data Warehouse?

Data Warehouse is the place where the data is stored for analysis, whereas OLAP is the process of analyzing the data, managing aggregations, partitioning information into cubes for in depth visualization.

What is a Cube and Linked Cube with Reference to Data Warehouse?

Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values. The linking in cube ensures that the data in the cubes remain consistent.

What is Snapshot with Reference to Data Warehouse?

You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data.

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.

What is the Difference between Data Warehousing and Business Intelligence?

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 and backup/recovery planning. 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. Typically, the term ’business intelligence’ is used to encompass OLAP, data visualization, data mining and query/reporting tools. (Reference: Les Barbusinski)

What is MDS?

Master Data Services or MDS helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. (Read more here)

Explain the Paradigm of Bill Inmon and Ralph Kimball.

Bill Inmon’s paradigm: Data warehouse is one part of the overall business intelligence system. An enterprise has one data warehouse, and data marts source their information from the data warehouse. In the data warehouse, information is stored in the 3rd normal form.

Ralph Kimball’s paradigm: Data warehouse is the conglomerate of all data marts within the enterprise. Information is always stored in the dimensional model.

How to Optimize Stored Procedure Optimization?

2014-07-10T22:47:00.000+05:30

Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName

AS

SET NOCOUNT ON;

--Procedure code here

SELECT column1 FROM dbo.TblTable1

-- Reset SET NOCOUNT to OFF

SET NOCOUNT OFF;

GO

Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method

-- Instead of

SELECT * FROM MyTable -- Avoid this method

--And finally call the stored procedure with qualified name like:

EXEC dbo.MyProc -- Preferred method

--Instead of

EXEC MyProc -- Avoid this method

Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects

WHERE name = 'MyTable' AND type = 'U')

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)

DECLARE @Age INT

SET @Age = 25

SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)

EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)

SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'

EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY

--Your t-sql code goes here

END TRY

BEGIN CATCH

--Your error handling code goes here

END CATCH

Reference: Pinal Dave

Difference between Data types

2014-07-10T22:44:00.002+05:30

CHAR and VARCHAR

VARCHARS are variable length strings with a specified maximum length. If a string is less than the maximum length, then it is stored verbatim without any extra characters, e.g. names and emails. CHARS are fixed-length strings with a specified set length. If a string is less than the set length, then it is padded with extra characters, e.g. phone number and zip codes. For instance, for a column which is declared as VARCHAR(30) and populated with the word ‘SQL Server,’ only 10 bytes will be stored in it. However, if we have declared the column as CHAR(30) and populated with the word ‘SQL Server,’ it will still occupy 30 bytes in database.

VARCHAR and VARCHAR(MAX)

VARCHAR stores variable-length character data whose range varies up to 8000 bytes; varchar(MAX) stores variable-length character data whose range may vary beyond 8000 bytes and till 2 GB. TEXT datatype is going to be deprecated in future versions, and the usage of VARCHAR(MAX) is strongly recommended instead of TEXT datatypes.

VARCHAR and NVARCHAR

In principle, they are the same and are handled in the same way by your application. The only difference is that NVARCHAR can handle unicode characters, allowing you to use multiple languages in the database (Arabian, Chinese, etc.). NVARCHAR takes twice as much space when compared to VARCHAR. Use NVARCHAR only if you are using foreign languages.

What is an Index?

2014-07-10T22:39:00.001+05:30

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results

The difference between Stored Procedure and Function?

2014-07-10T22:30:00.003+05:30

Difference between Index Scan and Index Seek?

2014-07-07T22:10:00.003+05:30

Index Scan:
Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan. Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

What is an execution plan? When would you use it? How would you view the execution plan?

2014-07-07T22:07:00.002+05:30

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.