Archive for the ‘OLAP’ Category

How to make OLAP Cube faster

How to make SSAS Cube faster

 

In order to make the cube much faster both on processing and querying one should define the data type as Analysis Services does not treat all the data types in the same way below is the brief description that help during the design of the data mart. Read full story »

Considering Key errors while cube processing

Considering Key errors while OLAP (SSAS) Cube processing:

In designing a data mart we often come across a situation while defining relationship between the fact and the dimension tables that is it feasible or not to have foreign keys NULLable? Are the built-in foreign keys of SQL Server are sufficient to handle relationship.

Foreign Key Null ability

It is not a good practice to leave the foreign key NULLable , because while moving data into the cubes causes relationship failure between the fact and

Read full story »

Junk Dimensions

Junk Dimension:-

Often at the end of the dimension modeling process, we come across with some attributes that do not belong to any specific diemsion these attributes are either have a very limited range or they seems to be not important enough to identified as dimension. One of the advantage of junk dimension is that if we join all these similar dimension in a single dimension we can reduce the number of fields in the fact table.

Dimensional Modeling- Star Schema

Dimension Modeling Methodologies:

The below are most used methodologies that are used for building a data mart.

Data warehouse schemas:

Collection of database objects, including tables, views, indexes, and synonyms are termed as schema.

Star Schema:

When you have identified your dimension and fact tables now it is needed to relate them, star schema methodology bound these tables in such a way that the fact table is kept center surrounded by any number of dimension tables representing a star style and called star schema. The Fact table is normally in the third normal form because the dependency is either on one dimension or all of them but not on combination of few dimensions.

The reason to choose a star schema is its simplicity from the users’ point of view: queries are never complex because the only joins and conditions involve a fact table and a single level of dimension tables, without the indirect dependencies.

Snowflake Schema:

A Kind of Star schema that is more complex data warehouse schema option to choose than its predecessor, It is called a snowflake schema because the diagram of the schema resembles a snowflake. The focus of this schema is to normalize the data warehouse to reduce the query performance, the stated schema normalized a dimension table into more sub tables although it saves space as it increases the dimension tables and require more foreign key to join tables but leaving the extraction process little bit complex as it increases the usage of complex SQL queries.

OLAP Terminology- About OLAP

OLAP and Data warehouse made easy

Developers of OLAP and multidimensional databases speak their own slang and use terms such as cubes, dimensions, measures, and members. Allow me to define these terms with a real life example that illustrates these terms more easily. Read full story »

Finding Sales Trend through SSAS MDX

Microsoft SQL Server Analysis Services is one of the leading OLAP server available in the market. Its often SSAS developers are required to provide trailing monthly, quarterly or year Sales or any measure trend analysis. There are many ways to perform Trend analysis, but I like this very simple and basic approach for getting trailing trend analysis.

There is a very simple and straight forward function in MDX “TAIL“. The Tail function returns the specified number of tuples from Read full story »

Error 44. No mapping between account names and security IDs was done

SSAS 2008While adding user or Group in Roles for Cube or while deploying SSAS 2008 cube you may encounter this error

The following system error occurred: No mapping between account names and security IDs was done.

You will receive this error when you have windows accounts or groups in an Analysis Service project that you are deploying to a server in a different domain. i.e. the server does not recognize these accounts.

A simple work around is to leave the windows users and groups out of the roles and add them in from SQL Server Management Studio. Another option, if it is a common deployment is to use AMO and add the appropriate accounts automatically. Also, if you use the Analysis Services Deployment wizard there are options to omit role deployment.

Managing “OLAP Databases with appropriate roles is a crucial function for the OLAP developer.

Hope this helps.

SSAS 2008 Cube Process OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

SSAS 2008 Don’t be confused when ever you face this clueless error while processing your cubes in SSAS 2008. I spent hours searching the internet and found no appropriate answer to over come this problem…. until I scroll my huge list of processing errors and found a ‘warningthat my dimension does not have the Date_SK that is being processed in my fact.


I simply incremental update my time dimension and here it goes. This error is a really fuss for the Olap developers as its clueless and on the internet we have a lot of stories and myths on this issue.

Well this is no myth for me, it worked for me.

Einfobuzz Site Map