Cube, Fact Table, Dimension Table

Q : What is Cube ?

A cube is a set of related measures and dimensions that is used to analyze data. A Cube is the primary storage mechanism for SSAS data. It consists of at least one fact table and many dimension tables. The rows in these tables have a key relationship to each other.

Q : What is fact table ?

A fact table is the center table surrounded by a dimension tables. All the dimension tables directly or indirectly connected with the fact table.

A fact table contains foreign keys to relate the rows in the fact table to the dimension table rows and facts. Facts are key performance indicators, for example net sales dollar, net sales unit, etc. Facts are usually numeric and additive.

Q : What is a dimension table ?

Dimension table consists of one or more source tables. Dimension tables have three types of columns. The first is the original primary key for each row. The second is a new primary key (Or surrogate key) for each row. This key is used to guarantee uniqueness, because the original data could come from multiple sources. The third is a column for each attribute that describes each dimensional value.

How many types Control Flow elements provides SSIS

Q: How many types Control Flow elements provides SSIS ?

SSIS provides three different types of Control Flow elements

i)                    Containers that provides structure in packages

ii)                   Tasks that provides functionality

iii)                 Precedence Constraints that connects the executables, containers and tasks into an ordered control flow.

SSIS Package, Tasks, Containers, Variables, Data Pipeline, Event Handler

SSIS Package, Tasks, Containers, Variables, Data Pipeline, Event Handler

SSIS 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, error and event handling and data destinations.

Containers :  Package objects that provide structure to packages and special services to tasks. Containers are used to support repeating control flows in packages and to group tasks. Containers can include other containers in addition to tasks.

Tasks : Package elements that define activities and processes, including data sources, destinations, transformations, and others.

Precedence Constraints :  Constraints that link executables, containers, and tasks within the package control flow and specify conditions that determine the sequence and conditions for determining whether executables run.

Variables :  Storage for values that an SSIS package and its containers , tasks, and event handlers can use at run time. The scripts in the scripts task and the script component can also use variables.

Control Flow :  An SSIS package process control component used to control flow elements that containers that provide structure in packages and services to tasks, tasks that provide functionality in packages, and precedence constraints that connect containers and tasks.

Data Flow :   An SSIS package data process  control component defined from within package control flow  that loads data from sources, transforms  and routes it through transformations, and saves it to destinations.

Event Handler :  An SSIS package process control component used to define the process activities to be performed at the time of a specific event state for the package or for any of its tasks or containers.

Data Pipeline :  The memory – based, multithreaded, buffered transformation process flow of data through an SSIS data flow task during package execution.

BIDS :   SQL Server Business Intelligence Development Studio. Provides the integration services project in which you create packages, their data sources, and data sources views. Read more ...

import column, export column, Copy Column transformation

Q: What is import column transformation ?

This transformation would be useful for loading a database with images, documents of media files.

Q: What is export column ?

It exports Binary data which means things like images, documents and other media which have been stored in a relational database. It exports them out to the file system.

Q: What is Copy Column ?

It takes an input column and creates a duplicate of it with a new name.

When we use For Loop, For Each Loop and Sequence Container task

Q: When we use For Loop, For Each Loop and Sequence Container task ?

For Loop Container Task : When we Know the exact how many times we have repeat given data flow for execution.

For Each Loop Container Task : When there is dependency of number of time task should execute on data such as files, variables we will use this.

Sequence Container Task : When we the exact Sequence and sometimes we need to perform parallel operation based on our client requirement.

Command line utility in SSIS

Q: Command line utility in SSIS ?

SQL Server Provides a Command Line Utility (DTEXEC.EXE) that helps the developers to execute the SSIS package. It can be directly used from the command prompt by moving around to the folder where the package is available and run the utility using this EXE.

DTEXEC  /? Provides the list of available options to execute the package from the command prompt.

DTEXECUI : This approach is a straight forward user interface option to execute a package.

How to deploy SSIS package

Q: How to deploy SSIS package ?

We have 3(Three) options are available for deploying the SSIS package. These are

·         Deployment Utility

·         Command Line Executable

·         SQL Server Management Studio.

What is SSIS

Q: What is SSIS ?

SSIS is an ETL tool (Extract , Transform , Load) Which is very much needed for Data warehousing applications.  SSIS  is also used to perform the operation like loading data based on the need , performing different transformations on the data like doing calculations (Sum, Average, etc.) and to define workflow of the process flow and perform some tasks on the day to day activities.

How do you create share schedule in SSRS

Q: How do you create share schedule in SSRS ?

To create shared schedules

1. Click Site Actions.
2. Click Site Settings.
3. In the Reporting Services section, click Manage Shared Schedules.
4. Click Add Schedule to open the Schedule Properties page.
5. Enter a descriptive name for the schedule. On the application pages used to work with Reporting Services reports, this name will appear in drop-down lists in schedule definition pages throughout the site. Avoid long names that are hard to read. Do follow a naming convention that puts the most description information at the beginning of the name.
6. Choose a frequency. Depending on the frequency you choose, the schedule options that appear on the page might change to support that frequency (for example, if you choose Month, the name of each month will appear on the page).
7. Define the schedule. Not all schedule combinations can be supported in a single schedule.
8. Set a start and end date.
9. Click OK.  Read more ...

What is difference between table and metrix report in SSRS

Q: What is difference between table and metrix report in SSRS ?

Table : Table report is just like a grid view.

Matrix : Matrix report gives us cross tab view it is also known as pivot report . 

What are data regions

Q: What are data regions ?

Data regions are report items that display repeated rows of summarized information from the data set. For 

ex. Table, Matrix, Chart etc.