What are the types of database schema in data warehouse ?

Star Schema:

A star schema is the one in which a central fact table is sourrounded by denormalized dimensional tables. A star schema can be simple or complex. A simple star schema consists of one fact table where as a complex star schema have more than one fact table.

Snow Flake Schema:

A snow flake schema is an enhancement of star schema by adding additional dimensions. Snow flake schema are useful when there are low cardinality attributes in the dimensions.

Galaxy Schema:

Galaxy schema contains many fact tables with some common dimensions (conformed dimensions). This schema is a combination of many data marts.

Fact Constellation Schema:

The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy. For example, if geography has five levels of hierarchy like teritary, region, country, state and city; constellation schema would have five dimensions instead of one.

What are the limitations in SSRS on SQL Server express edition?

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

Management Studio cannot be used to administer report server

Report Models will not be available

Report Builder is not available

Caching, History and Delivery of Report is not available.

SQL Server agent is not available

No scheduling is possible

Remote server database is not available for Report Data Source (Local SQL Server is a only option,)

We cannot store the report server database on a remote server (it has to be local only)

Reports can be rendered only in Excel, PDF, Image formats only

Reporting Services will not be able to use more than 1 GB of RAM

No Subscriptions (Standard and Data Driven) can be made

Can not be integrated with Share Point

Can not implement Role based security

Only named instances is supported

Scale-out Report Servers will not be available

What is Statistic?

The science that deals with the collection, classification, analysis, and interpretation of numerical facts or data, and that, by use of mathematical theories of probability, impose order and regularity on aggregates of more or less disparate elements.

There are 2 parts of definition

 the collection, classification, analysis, and interpretation of numerical facts or data

the use of probability theory to impose order on aggregates of data

In simpler words, statistics deals with summarizing information about data in a meaningful and relevant way.

Statistics is also using data to predict things that are unknown. We can be 95% confident more people will vote for Candidate A than B.

Statistical analysis is like solving mysteries with data. We start with questions and attempt to answer them with data instead of our intuition. When we assemble enough data we make predictions.

With predictions, there's always a chance that we'll be wrong. Much of statistics is understanding what we know from data we do have, making our best prediction about data we don't have, and clearly understanding the chance that we're wrong and quantifying that

Different types of dimensions

Conformed Dimension

         A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.

         Example:  Date Dimension table can be shared by multiple fact tables. Date Dimension table connected to sales fact is identical to the one connected to inventory facts.

Junk Dimension

         A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags,

         Example: non-generic comments or just simple yes/no or true/false indicators.

Degenerated dimension

         A degenerated dimension is data that is dimensional in nature but stored in fact table, doesn’t have any dimensional table on its own.

         Example: A dimension having Order Number and Order line number having 1:1 relationship with fact table. Instead of creating as a separate dimension, containing billions of rows, those 2 columns can be included as part of the fact table.

Role Playing dimension

Dimensions that can be joined to the same fact table multiple times, each time to a different column. These dimensions are referred to as role playing dimensions, as the same dimension plays different roles.

Example:  Suppose, if you want to examine sales by order date, ship date and delivery date. Instead of creating 3 different date dimensions, create a single date dimension and join it to the fact table 3 times in data source view.

Parent-child dimension

Based on self-referencing relationship

Example: If you have a employee dimension and want to add parent child relationship to employee manager, you must include both employee key and parent employee key in your dimension as attributes.

Time Dimension

            SSAS provides 2 ways of creating time dimension

•       Base the time dimension on a dimension table that exists in a relational warehouse

•       Create the time dimension based on system generated time values. (server time dimension)