MSBI Question And Answer By Hussain

SSIS Fast Load Oracle Destination Step by Step :

2014-05-05T10:26:00.000-07:00

SSIS Fast Load Oracle Destination Step by Step :

To fast load the data into Oracle destination I am using here Oracle Attunity. Attunity help us to fast load the data. Here I am showing you step by step to load the data fastly for this I am using DB2 source.

Step 1: Take Dataflow Task for perform ETL operation.

Step 2 : Take OLEDB Source and create a connection which is your source database and choose SQL Command to write the script for fetch only those field which required.

Step 3 : Click on Columns tab.

Step 4 : Take Data Conversion Transformation if there is need in my scenario I need Data Conversion transformation to change some data types and their size.

Step 5: Here I have changed some columns data types according to destination columns data types.

Step 6: Take Oracle Attunity Destination and configure the connection string.
TNS Service Name: Is your TNS for Destination server
User Name: Server User name
Password: Server Password

Then Click on Test Connection if Test connection succeeded then press OK and again OK.

Step 7: Click on Name Of The Table Or The View Dropdown to select the correct table.

Step 8: Click on Mapping tab to map the columns and Click on OK.

Step 9: Click on RUN to Execute the Package.

It has taken only 0.22 Seconds to fetch 87,840 records.

Difference between OLAP and OLTP

2014-01-10T09:51:00.001-08:00

What is the difference between OLAP and OLTP ?

* OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually3NF). Example: In a banking System, you withdraw amount through an ATM. Then account Number,ATM PIN Number,Amount you are withdrawing, Balance amount in account etc are operational data elements.

* OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.

Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your application.	Designed to hold historical data for analyses and forecast business needs.
Holds daily Latest Transactional Data related to your application.	Data is consistent up to the last update that occurred in your Cube.
Data stored in normalized format.	Data stored in denormalized format.
Databases size is usually around 100 MB to 100 GB.	Databases size is usually around 100 GB to a few TB.
Used by normal users.	Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less.	CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large,it can impact the reporting performance.	Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query.	MDX is used for querying on OLAP Cube.
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability.	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method.

Difference between MDX and T-SQL

2013-03-21T22:27:00.000-07:00

MDX Vs T-SQL

i) The ability of MDX to reference multiple dimensions but SQL refers to only two dimensions, columns and rows when processing queries.

ii) In SQL, the SELECT clause is used to define the column layout for a query, while the WHERE clause is used to define the row layout. However, in MDX the SELECT clause can be used to define several axis dimensions, while the WHERE clause is used to restrict multidimensional data to a specific dimension or member.

iii) The process of creating an SQL query is also different than that of creating an MDX query.

iv) The visualization of an SQL result set is intuitive; the set is a two-dimensional grid of columns and rows. The visualization of an MDX result set is not as intuitive, however. Because a multidimensional result set can have more than three dimensions.

v) To refer to such two-dimensional data in SQL, the name of a column and the unique identification of a row, in whatever method is appropriate for the data, are used to refer to a single cell of data, called a field. However, MDX uses a very specific and uniform syntax to refer to cells of data, whether the data forms a single cell or a group of cells.

Difference Between Script Task and Script Component

2013-03-09T22:20:00.000-08:00

Script Task VS Script Component :

While both the objects provide scripting environments using the VSTA engine, yet there are inherent differences in their purpose and functions. This is due to their design architecture and the placement in development environment.

Ø The main difference of Script Task and Script Component is the way the script is executed by both the objects. The Script Task executes the script only once for each execution instance of the task, while the Script Component generally executes the script for each row, as it applies the custom transformation on each of the data row reads.

Ø The Script Task is available in the Control Flow task tab, while the Script Component works in the pipeline or the Data Flow Task.

Ø You can choose Script Component as a Source, a transformation, or a destination. The choice of function and the metadata and properties you configure in the editor results in creation of different members of the base classes in the auto-generated code. The Script Task does not provide any such facility, as the code generated is always the same.

Ø The Dts property works with only the Script Task and it is not available in the Script Component.

Ø The Script Task supports breakpoints while Script Component does not support breakpoints.

Nonblocking, Partial Blocking and Blocking Transformations In SSIS

2013-03-04T02:41:00.000-08:00

Classifying Data Flow Transformations on performance considerations:

The three classifications of Data Flow transformations that are based mainly on

performance considerations.

Nonblocking Synchronous Row-Based Transformations:

These transformations work on a row-by-row basis and modify the data by changing

the data in columns, adding new columns, or removing columns from the data rows,

but these components do not add new rows to the data flow. The rows that arrive at the

input are those that leave at the output of these transformations. These transformations

have synchronous outputs and make data rows available to the downstream components

straightaway. In fact, these transformations do not cause data to move from one buffer

to another; instead, they traverse over the data buffer and make the changes to the data

columns. So these transformations are efficient and lightweight from the process point

of view. The transformations that readily pass the data to the downstream components are classified as Row Transformations.

The following Row Transformations are examples of Nonblocking synchronous row-based transformations:

Ø Audit transformation

Ø Character Map transformation

Ø Conditional Split transformation

Ø Copy Column transformation

Ø Data Conversion transformation

Ø Derived Column transformation

Ø Export Column transformation

Ø Import Column transformation

Ø Lookup transformation

Ø Multicast transformation

Ø OLE DB Command transformation

Ø Percentage Sampling transformation

Ø Row Count transformation

Ø Row Sampling transformation

Ø Script Component transformation configured as Nonblocking Synchronous

Row–based transformation

Ø Slowly Changing Dimension transformation

Partially Blocking Asynchronous Row-Set-Based Transformations:

Integration Services provides some transformations that essentially add new rows in the

data flow and hold on to the data buffers before they can perform the transformation.

The nature of such transformations is defined as Partially Blocking because these

transformations hold on to data for a while before they start releasing buffers. As

these transformations add new rows in the data flow, they are asynchronous in

nature. For example, a Merge transformation combines two sorted data sets that may

be the same data, but it essentially adds new rows in the data flow. The time taken

by this transformation before starting to release the buffers depends on when these

components receive matching data from both inputs.

Following is a list of Partially Blocking asynchronous row set-based transformations:

Ø Data Mining Query transformation

Ø Merge Join transformation

Ø Merge transformation

Ø Pivot transformation

Ø Term Lookup transformation

Ø UnPivot transformation

Ø Union All transformation

Ø Script Component transformation configured as Partially Blocking Asynchronous

Row-set-based transformation

Blocking Asynchronous Full Row-Set-Based Transformations:

These transformations require all the rows to be assembled before they can perform

their operation. These transformations can also change the number of rows in the

data flow and have asynchronous outputs. For example, the Aggregate transformation

needs to see each and every row to perform aggregations such as summation or

finding an average. Similarly, the Sort transformation needs to see all the rows to

sort them in proper order. This requirement of collecting all rows before performing

a transformation operation puts a heavy load on both processor and memory of the

server. You can understand from the nature of the function they perform that these

transformations block data until they have seen all the rows and do not pass the data to

the downstream component until they have finished their operation.

The following are Blocking asynchronous full row-set-based transformations:

Ø Aggregate transformation

Ø Fuzzy Grouping transformation

Ø Fuzzy Lookup transformation

Ø Sort transformation

Ø Term Extraction transformation

Resources:

For more detailed information regarding this study, please see

Ø Microsoft SQL Server 2008 Integration Services by Ashwani Nanda

Ø Microsoft Books Online

Ø http://blogs.msdn.com

Ø http://forums.microsoft.com/MSDN

STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE

2013-03-02T23:44:00.000-08:00

STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE IN T-SQL

Stored Procedure:

To create Stored procedure, the CREATE PROCEDURE Statement is used. Stored procedures are created in the current database unless a temporary stored procedure is created in tempdb. Because stored procedure are precompiled, they usually provide the best performance of any type of query.

Rules for creating Stored procedure:

i) The name must follow the rules for identifiers.

ii) Referenced objects must exist when your stored procedure runs.

iii) Object with the same name in a single stored procedure can’t be created and then drop or re-create.

iv) Temporary tables can be referenced within the stored procedure. Local temporary tables disappear when the procedure ends.

v) Upto 1024 parameters can be allowed.

vi) Procedure can be nested within procedure upto 32 levels deep.

vii) Stored Procedure can’t have the following T-SQL create statement in them:

Ø CREATE DEFAULT

Ø CREATE RULE

Ø CREATE PROCEDURE

Ø CREATE TRIGGER

Ø CREATE VIEW

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS

2013-03-02T23:37:00.000-08:00

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS IN T-SQL

Triggers:

A Trigger is a special type of stored procedure that’s automatically invoked when the user try to modify data that it’s designed to protect. Triggers help to secure data’s integrity by preventing unauthorized or inconsistent changes from being made.

Triggers Performance:

From the performance point-of-view, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory or a bit slower if they read from disk.

Rules for creating Triggers:

i) Triggers can’t be created on temporary tables. They can, however, reference views and temporary tables.

ii) Triggers can’t return result sets. Using the IF EXISTS clause as a part of a SELECT Statement in trigger code is a common practice.

iii) Triggers should be used to maintain data integrity, maintain referential integrity, and encapsulate business rules.

iv) Triggers can be encrypted in the syscomments table if you specify the WITH ENCRYPTION option.

v) WRITETEXT statement don’t activate triggers. They are used to modify text or image data, and it’s a nonlogged transaction.

vi) The following SQL Statement can’t be used in a trigger:

Ø All CREATE Statements

Ø All DROP Statements

Ø ALTER TABLE and ALTER DATABASE

Ø TRUNCATE TABLE

Ø RECONFIGURE

Ø LOAD DATABASE or TRANSACTION

Ø UPDATE STATISTICS

Ø SELECT INTO

Ø DISK STATEMENT

Redirect Duplicate Rows Using SSIS Step By Step

2013-02-09T07:03:00.000-08:00

Redirect Duplicate Rows Using SSIS

There are many ways to redirect the duplicate into another direction. You can use Script Component, Lookup Transformation etc. I am showing you the simplest way to redirect the duplicate. For this, here I am using Fuzzy Group Transformation to redirect the duplicate into another direction.

Here, I am taking Repeat_Data table from my Employee Database. In this table I have created some duplicate records for better understand.

Now Go to BIDS, Start New projects, Name the Project.

Take Data flow Task and double click on it.

Take OLEDB Data Source, double click on it and configure it.

Choose Table Repeat_Data.

Go to Columns and take all columns.

Take Fuzzy Group Transformation and double Click on it. A Fuzzy Group Transformation editor will open. Leave the first tab that is Connection Manager . Click the second tab i.e. Columns and configure it as directed below.

Make the Minimum Similarity of name column to 0.25.

Go to Advanced tab and set the Threshold to 0.50 and press OK button.

Take Conditional Split Transformation and double Click on it and write Condition.

Condition: _key_in == _key_out

Make the Default output name as Duplicate Rows.

Your all configuration has completed now you can take any destination where you want to keep your unique records and where you want to keep your duplicate records. Here I am taking Multicast Transformation for just showing you the output of this configuration with the help of Data Viewer.

It is the Preview of your Package.

(Note: It is very easy way to redirect the duplicate into another direction but it has a drawback. It is a blocking transformation so if you use it then may be you have some performance issue.)

Dynamic Column Report In SSRS Step By Step

2013-02-02T22:56:00.000-08:00

Dynamic Column Report In SSRS

I am showing you how to create Dynamic Column Report. It means when a user select two column the report will display only two columns and if they select five columns then it will display five columns. So we can say that the report will display the field on the basis of user selection.

For this we have to create two datasets.

Here is an example from Northwind Database.

Create first dataset named Hussain_Dataset.

SQL Scripts:

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone FROM Customers

Insert a Table in design area and create a Table Like that:

Now go to the dataset and create another dataset Hussain_Dataset1

SQL Scripts:

SELECT 1 AS ID, 'Customer Id' AS CustomerID

UNION

SELECT 2 AS ID, 'Company Name' AS CompanyName

UNION

SELECT 3 AS ID, 'Contact Name' AS ContactName

UNION

SELECT 4 AS ID, 'Contact Title' AS ContactTitle

UNION

SELECT 5 AS ID, 'Address' AS Address

UNION

SELECT 6 AS ID, 'Postal Code' AS PostalCode

UNION

SELECT 7 AS ID, 'Country' AS Country

UNION

SELECT 8 AS ID, 'City' AS City

UNION

SELECT 9 AS ID, 'Phone' AS Phone

FROM Customers

Now create a parameter DCParamaeter and click the Allow Multiple value.

Go to the Available Values and choose Get values from query and set dataset to Hussain_Dataset1 , Value Field CustomerID, Label Field CustomerID.

And Now the time to set the Column Visibility. For this we go to first column of the Tablix and right click after that we click on Column Visibility option.

A new popup box will appear. Here we choose Show Or Hide Based On Expression and click on fx button.

A new popup box will appear here we write the expression.

Expression :

=IIF(InStr(JOIN(Parameters!DCParameter.Value,","),"Customer Id")>0,False,True)

We have to perform this step to each column of Tablix.

(Note:Don’t forget to modify the expression for the respective column name accordingly.)

Your Dynamic Column report has completed. Now run the Report. Here is the some preview of your Dynamic Column Report.

Remove Duplicate Records Using SSIS

2012-10-03T03:16:00.000-07:00

Remove Duplicate Records Using SSIS Step by Step

It is very easy task to remove the duplicate rows from the table using SSIS package. To remove the duplicate records in SSIS there are multiple ways and I am using SORT transformation. Below I am showing you a simple example for removing the duplicates.

Go to the BIDS >> choose new project >> choose Integration Service Project >> give your package name and press OK button.

Now inside the Toolbox from Control flow item select the Data Flow Task and drag and drop into design area.

Rename the Data Flow Task.

Double click on Data Flow Task OR Right Click on Data Flow Task and choose edit.

Inside the Data Flow Task choose OLEDB Data Source and Drag and drop it into design area.

Rename it.

Double click on OLEDB Data Source.

OLEDB Data Source Editor will open. Now click on NEW button.

Again a new Editor will open here we again click on NEW button to configure the connection from the Database.

Here we create connection to Database. For this I am using AdventureWorks Database So I use our local Server and Database AdventureWorks. After choosing the Database just check that everything is ok or not by clicking Test Connection button. If everything is ok then press OK button.

Click on Name of the Table or View and choose the table then click on column to select the column.

Now choose the columns which you want to display and click on OK.

Drag the SORT Transformation and double click on it for configure.

Here enable the check box i.e. at the bottom of the SORT Transformation to remove the duplicate row and press OK.

Now you can choose any destination to load the data. I am using derived column to show you the result.

Here Data Viewer Shows the output rows.

MOUSE HOVER REPORT OR TOOL TIP REPORT STEP BY STEP

2012-09-02T21:35:00.000-07:00

MOUSE HOVER REPORT OR TOOL TIP REPORT IN SSRS

I am showing you that how to create Mouse Hover or Tool Tip report in this article.

Here is an example from AdventureWorks Database.

SQL Scripts :

SELECT Person.Contact.ContactID, (Person.Contact.FirstName+' '+ Person.Contact.MiddleName+' '+Person.Contact.LastName) As FullName,

Sales.ContactCreditCard.CreditCardID, Sales.CreditCard.CardType, Sales.CreditCard.CardNumber, convert(varchar(10),Sales.CreditCard.ExpMonth) as months, Sales.CreditCard.ExpYear

FROM Person.Contact INNER JOIN

Sales.ContactCreditCard ON Person.Contact.ContactID = Sales.ContactCreditCard.ContactID AND

Person.Contact.ContactID = Sales.ContactCreditCard.ContactID INNER JOIN

Sales.CreditCard ON Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND

Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND

Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID

where Person.Contact.MiddleName <> 'NULL'

order by ContactID

Select the Header Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, Select color from drop down.

Repeat previous step for data row to set color for better look

Now select the ContactID column and go to the properties window.

Select Expression from Tool Tip

Set the expression for Tool Tip

Expression :

=("FULL NAME : " & Fields!FullName.Value & chr(10)

&"EXPIRY MONTH : " & Fields!months.Value & chr (10)

& "EXPIRY YEAR : " & Fields!ExpYear.Value & chr (10))

Click Ok

Now Run the report

Above the preview of report.

Drillthrough report in SSRS Step by Step

2012-08-06T10:03:00.000-07:00

Drillthrough report in SSRS

A Drillthrough is a kind of report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until the user clicks the link in the main report.

Here I am showing you how to create Drillthrough Report.

For this I am creating two Report i.e. Parent and Child Report.

Here is an example from AdventureWorks Database for Parent Report.

SQL Scripts

SELECT CustomerID, ContactID, Demographics, ModifiedDate

FROM Sales.Individual

Now insert a table and Format the column

And Now create a Child Report

Here I am also using AdventureWorks Database for Child Report.

SQL Scripts

SELECT CustomerID, ContactID, Demographics, ModifiedDate

FROM Sales.Individual

WHERE (CustomerID = @CustID)

Here I am creating a Parameter Custid.

Here both report created and now its time to map the parent report to child report. For this

We go to the parent report [CustomerID] column.

Right click on [CustomerID] column and choose Text Box Properties

Go to Action tab and map these:

Then Click on OK.

And now run the report. Below the preview of Drillthrough report.

Page Break at Nth row in SSRS Report Step by Step

2012-07-29T12:53:00.000-07:00

We can design a SSRS report with a constraint of only N^th rows per page.

I am showing you that how to display only N^th rows in the report in this article.

Here is an example from AdventureWorks Database.

SQL Scripts.

SELECT TOP 1000 [StateProvinceID]

,[StateProvinceCode]

,[CountryRegionCode]

,[Name]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, Select color from drop down.

Now create a group.

After creating the group go to the group properties and click on fx button.

And set the expression for group properties.

Expression :

=Ceiling((RowNumber(Nothing)) / 15)

( Note : You can put N^th number in place of 15. For example if you want to your page break affect after each 25^th row than you have to put 25 in place of 15 )

Now, report will show 15 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option.

And now run the report. Below the preview of report.

Create SSRS report using Excel Data Source Step by Step

2012-07-24T14:46:00.004-07:00

By creating a report using Excel as a Data Source we will follow the following steps ...

Right click on Shared Data Source folder and choose Add New Data Source. A new popup screen will open. Give the Data Source Name and Choose ODBC from the dropdown box.

Then Click on Edit button. A new screen will open. From the new screen i.e. Connection Properties go to the Use user or system data source name and from the drop down choose Excel Files .

Then Choose the Use Connection String and click on Build button. A new pop up screen will open . Click on New button.

Then click on Next button

Choose the Microsoft Excel Driver and click on Next button and then click on Finish button.

Then click on Select Workbook button and choose the file and click on OK button.

Click on OK.

Test the connection and press OK.

Click on OK.

Click on Reports folder and choose add new items and choose Report. Map your Data Source and click OK.

Right click on Dataset Folder and Write the query for the table. (I have used a very simple query .For ex.)

This is Your Report from Excel data source.

Alternating Table Row Color In SSRS Step by Step

2012-06-17T02:38:00.001-07:00

Alternating Table Row Color In SSRS Step by Step

Here is an example from AdventureWorks Database.

Sql Script.

SELECT TOP 1000 [StateProvinceID]

,[StateProvinceCode]

,[CountryRegionCode]

,[Name]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, Select color from drop down.

Now, Select the Data Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, select expression from the drop down.

Enter the expression "=IIF(RowNumber(Nothing) mod 2 = 0, "SeaGreen","Pink") " in the Expression Window.

Click OK.

Below is a Preview of a SSRS report with two different colors for alternate rows.

When should you use a Star and when should you use Snowflake Schema

2012-06-02T21:59:00.000-07:00

Q: When should you use a Star and when should you use Snowflake Schema ?

Star Schema : If the performance is the priority Than go for Star Schema, Since here dimension tables are denormalized.

Snowflake Schema : If memory space is the priority than go for Snowflake Schema, since here dimension tables are normalized.

Advantage and disadvantage of Snowflake Schema

2012-06-02T21:53:00.000-07:00

Q: What are the Advantage and disadvantage of Snowflake Schema ?

Advantage of Snowflake Schema

The main advantage of Snowflake Schema is the improvement of query performance due to minimized disk storage requirements and joining smaller lookup tables.
It is easier to maintain.
Increase flexibility.

Disadvantage of Snowflake Schema

The main disadvantage of the Snowflake Schema is the additional maintenance efforts needed to the increase number of lookup tables.
Makes the queries much more difficult to create because more tables need to be joined.

Snowflake Schema

2012-06-02T21:48:00.000-07:00

Q : What is Snowflake Schema in SSAS ?

A Snowflake Schema contains fact table and dimension tables, with the fact table storing facts about business transactions and some columns of the fact containing foreign key references to the dimension tables. The dimension tables of a snowflake schema are typically normalized to the third normal form (3 NF) or higher. As a result the data for a given dimension is spread out over multiple tables, and a diagram of the database resembles a snowflake.

Difference between a data source and data source view

2012-05-27T12:10:00.000-07:00

Q 3 : What is the difference between a data source and data source view ?

A Data Source represents a connection to a particular data source using one of the supplied providers. A Data Source View is an abstraction layer that you design using a data source. The latter also contains expressions that create calculated fields.

Difference between online and offline mode in SSAS

2012-05-27T12:07:00.000-07:00

Q 2. What is the difference between online and offline mode in SSAS ?

Online mode means that you are directly connected to a particular SSAS database instance. Any changes you make are implemented on save. Offline mode means that means that you are creating metadata files that can be deployed to the SSAS server when you want. An important consideration for offline mode is that BIDS includes no granular conflict resolution.

Aggregation types are available for measures in BIDS

2012-05-27T12:01:00.000-07:00

Q 4 : What aggregation types are available for measures in BIDS ?

The default aggregation is SUM. Other available aggregation types include the following: MIN, MAX, COUNT, DISTINCT COUNT, AVERAGE OF CHILDREN, FIRST CHILD, LAST CHILD, FIRST NON EMPTY, LAST NON EMPTY. You can also design custom aggregations using MDX.

What is star schema

2012-05-27T05:29:00.003-07:00

Q 1. What is star schema in SSAS ?

A star schema is a type of modeling used as a basis for building an OLAP cube. It consists of at least one fact table and many dimension tables. The rows in the dimension tables have a 1:M relationship with the rows in the fact table.

In a star schema every dimension table is related directly to the fact table.

Cube, Fact Table, Dimension Table

2012-04-19T04:15:00.000-07:00

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

2012-04-16T05:11:00.000-07:00

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

2012-04-14T23:42:00.000-07:00

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