What’s on our mind?

Exsilio Solutions is proud to introduce our new blog! Not only is this forum completely integrated with all our social networks, but it is also 100% responsive. Now, you can take Exsilio with you on your phone, tablet, and desktop - redefine what you thought possible!

SSAS Dimensions and Cube Basics

SSAS Dimensions

1. Introduction to Dimensions

SSAS dimensions are groups of attributes based on columns from tables or views in a data source view. Dimensions exist independent of a cube, can be used in multiple cubes, can be used multiple times in a single cube, and can be linked between Analysis Services instances. A dimension that exists independent of a cube is called a database dimension and an instance of a database dimension within a cube is called a cube dimension. A dimension cannot be added directly to a cube until it is added as a database dimension.

  • Properties: Name column: e.g. Key column is ProductID, while Name Column is Product Name (it helps you to view and understand the data).
  • Why you should have a key in each and every dimension table? Because without the key attribute, we cannot join the dimension and fact table, and therefore, cannot analyze the data on the dimensional level.

2. Dimension Structure

Each dimension contains a key attribute. The key attribute is the attribute in a dimension that identifies the columns in the dimension main table that are used in foreign key relationships to the fact table. Typically, the key attribute represents the primary key column or columns in the dimension table. You can define a logical primary key on a table in a data source view which has no physical primary key in the underlying data source. We can add additional columns at this level.

3. Dimension Hierarchies

Hierarchies in SSAS optimize performance of cube by improving cube aggregations. There are two major types of hierarchies:

  • Natural (automatically comes within the dimension)
  • User-defined
    • Balanced: In a balanced hierarchy, all branches of the hierarchy descend to the same level, and each member's logical parent is the level immediately above the member. (Year > Semester > Quarter > Month)
    • Unbalanced: In an unbalanced hierarchy, branches of the hierarchy descend to different levels. Parent-child hierarchies are unbalanced hierarchies. (Manager ID à Employee ID)
    • Ragged: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.

4. Attribute Relationships

Each and every non-key attribute in a dimension must be directly or indirectly related to Key attribute. Relationship between attributes can be changed to either “Flexible” or “Rigid”

  • Flexible -> the relationship will change over time.
  • Rigid -> the relationship will not change over time.

5. Dimension Translations

In SSAS a dimension translation is a language-specific representation of the name of a dimension, the name of an Analysis Services object or one of its members, such as a caption, member, or hierarchy level. Translations provide server support for client applications that can support multiple languages. Frequently, users from different countries view a cube and its dimensions. It is useful to be able to translate various elements of a cube and its dimensions into a different language so that these users can view and understand the cube.

6. Browser

Once the dimension is Processed and Deployed, the data and hierarchy behavior can be checked in Browser Pane.

SSAS Cube

1. Cube Structure

Measures: A measure represents a column that contains quantifiable data, usually numeric, that can be aggregated. Group of measures having same granularity forms a Measure Group.

Dimensions: All dimension added in Dimension folder can be used in instance of a Cube. Attributes that are added and processed at Dimension level are showed in Cube Dimension. Dimensions that are available at DSV are only pulled into Cube Structure. Cube dimensions can be edited to add new attributes or hierarchies.

2. Dimension Usage

After you got the inappropriate data after you processing the cube, the first place to check is the Dimension Usage. The second cube object Dimension Usage describes how a Dimension is related to Measure Group in Cube. (e.g. data from AdventureWorksDW2012)

SSAS Cube

Dimension Keys are automatically related to respective Measure Group. If there is no relation between a Dimension and Measure Group, there cannot be any aggregations created across that Measure Group. A Dimension and Measure Group can have the following relations:

  • No Relation (facts and dimensions are not connected, because no connecting keys)
  • Regular (fact referencing dimensions, one-to-one or one-to-many relationship)
  • Referenced (fact and dimension table is joined though an intermediate dimension table.)
  • Many-to-Many (4 tables involved)
  • Fact (a de-generated dimension that is any dimension member which is increasing with the fact measures could be included in a fact table. Indirectly, the dimension and the fact could be combined)

SSAS Fact

3. Calculations

We can add additional measures, dimension attributes, new calculated columns (called calculated members). A calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set in a cube in SSAS. Calculations let you add objects that are defined not by the data of the cube, but by expressions that can reference other parts of the cube, other cubes, or even information outside the Analysis Services database.

4. KPIs (Key Performance Indicator)

In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business success. A KPI is frequently evaluated over time. KPI components are:

  • Value: An MDX numeric expression that returns the actual value of the KPI.
  • Goal: An MDX numeric expression or a calculation that returns the target value of the KPI.
  • Status: An MDX expression that represents the state of the KPI at a specified point in time. The status MDX expression should return a normalized value between -1 and 1.
  • Trend: An MDX expression that evaluates the value of the KPI over time. The trend MDX expression enables a business user to determine whether the KPI is improving over time or degrading over time.

5. Partitions

Cube partitions are used by SSAS to manage and store data and aggregations for a measure group in a cube. Every measure group has at least one partition; this partition is created when the measure group is defined. When you create a new partition for a measure group, the new partition is added to the set of partitions that already exist for the measure group. A Cube in general stores aggregations, data and metadata. Based on the storage mode, the Cube Partitions are divided as

  • Multi Dimensional Online Analysis Processing (MOLAP): Stores Data, Aggregations and Metadata in Cube.
  • Relational Online Analysis Processing (ROLAP): Stores Data and Aggregations in Relational Database and metadata in Cube.
  • Hybrid Online Analysis Processing (HOLAP): This storage mode is hybrid of MOLAP and ROLAP. In this mode Aggregations are stored in Cube, whereas Data and Metadata is stored in Relational Database.

6. Aggregations

Aggregations are pre-calculated summaries of data from leaf cells. Aggregations improve query response performance by preparing the answers before the questions are even asked. Dimensions and their hierarchies of attributes reflect the queries that can be asked of the cube. Aggregations are stored in the multidimensional structure in cells at coordinates specified by the dimensions.

 

SQL SERVER 2016 – ISJSON Function

In my first post, How to load data into SQL Server using JSON functionality, I discussed one of the key functions of SQL Server 2016 JSON support, which is OPENJSON.

Today we will see another built in function of JSON, which is ISJSON.

ISJSON - This function is used to test whether a string contains valid JSON or not. It returns 1 if the string is valid JSON. If not, it returns 0. It returns null if the expression is null. We will check all of these return values.

MORE

Employee Spotlight: Shawna Proske

 

Many people ask us, “What is the best part of working at Exsilio?”.. The answer is simple, our people! Every month we spotlight one of our awesome employees. These spotlights will highlight their experiences, accomplishments at Exsilio, and fun facts about what makes them great! This month, we're featuring Shawna!
MORE

Employee Spotlight – Sylvia Cortez

Many people ask us, “What is the best part of working at Exsilio?”.. The answer is simple, our people! Every month we spotlight one of our awesome employees. These spotlights will highlight their experiences, accomplishments at Exsilio, and fun facts about what makes them great! This month, we're featuring Sylvia Cortez! MORE

Switching Tables in a SQL Server Database to a New Filegroup

Sometimes you may find your SQL Server database is performing at suboptimal levels. After a lot of use, especially in a production environment, your database will have endured a high number of transactions, object creation and drops, and these can lead to fragmentation of the data at the disk level. You may also find that your database is quite large, but it has some empty space. However, when you attempt to shrink the database, you cannot do it. In this case, a common culprit is fragmentation.

MORE

Employee Spotlight – Jameson Balingit

Many people ask us, “What is the best part of working at Exsilio?”.. The answer is simple, our people! Every month we will be spotlighting one of our awesome employees. These spotlights will highlight their experiences, accomplishments at Exsilio, and fun facts about what makes them great!

MORE

Got a Niche?

The most important decision to make before creating an advertising campaign is determining the audience you would like to reach - or finding your niche market.

MORE

Entrepreneurship in the New Generation

Recently, I had the honor of participating in Startup Weekend GIRLS (#swgirls) at the Lake Washington Girls Middle School. Together with our Marketing Coordinator, Shawna Proske, I acted as a coach; to help the new generation of entrepreneurs to brainstorm, plan, develop, and pitch their new business ideas for the competition.

MORE

Business Intelligence: How Much Do I Need?

Many business leaders find themselves asking, “How much Business Intelligence do I need” when faced with the task of determining where they stand in the market, where they can improve, and/or how they can cut costs. Of course, if that business had unlimited resources, then the answer is “give me everything you’ve got”. Unfortunately, most of us don’t have those kind of resources, so the question becomes: how much business intelligence (BI) do I need.
MORE

Email Marketing and Social Media Saturation

Recently, noted writer and public speaker, Tim Ferris announced he would be moving more of his marketing to email from major social channels like Facebook and Twitter. The reason he gave is that today's world is a “battlefield of noise” and that with social media saturation, his blog posts were getting lost in the fray.

MORE

4 Behaviors for Project Management Success that you can Implement Today

There are many resources for aspiring or practicing project managers. A cursory internet search turns up a multitude of tools, theories, and certifications. While the inherent rigidity of project management is important, soft skills often play a huge role in determining the outcome of a project. The behaviors outlined below reflect learnings that I’ve accumulated over the years to improve my projects and provide the most amount of value I can to any project owner.

MORE

Thanksgiving at Exsilio

Thanksgiving is a time to rejoice and be grateful for all the good that has happened to us over the past year. At Exsilio it is also a time for employees to gather and stuff our faces with delicious food! We get excited every year to show off our cooking (or buying for us non cooks) skills during the Thanksgiving potluck. Both our Redmond and Irvine offices took part in what is quickly becoming a yearly tradition. Employees battled in the morning to get our work done as the aromas of everyone’s dishes filled the air. MORE

Big Data & Marketing

As a business intelligence professional, I am always keeping an eye on developing trends in the industry and recently I came across an article on the "Top 8 Big Data Trends That Marketers Should Care About." Being a part of the Redmond office, I have the opportunity to see our marketing team in action and appreciate the work that they do. This article naturally piqued my interest as I see the team work diligently to help clients develop sound marketing strategies to grow their businesses and streamline their day-to-day business operations. MORE

Javascript Best Practices: Modules

As modern web applications become increasingly client heavy, it’s important to properly organize your client side code. In small applications, organization may not seem necessary, however, as your application grows and more people are involved with the project, proper organization becomes key to keeping the application maintainable. This is where Modules come in.

MORE

Google Search versus Google Display

 

 

 

 

Last year, Exsilio’s resident Search Engine Marketing expert Chris Meade released an article making the case for Bing Ads. For this article, we are taking a different approach and focusing on the maximization of the ROI on your ad campaigns by choosing the network that best helps you achieve your end-goal, whether that is more leads for your business, greater awareness of your products and services, or building brand loyalty.

MORE

Parallel Optimization in SSIS 2012

Being able to run multiple stored procedures in parallel can provide a major performance boost when loading a data warehouse. In this post, I’ll describe a process which will take full advantage of server resources by running multiple procedures in parallel.

MORE

Software Testing Trends

In the past years, the trending of mobile technology, applications and the cloud has grown exponentially. It continues to grow today. Therefore, the demand for testers is growing as well. Security, performance, and usability testing are on top of their list to have a successful product.

The popularity of mobile technology and apps are insane. Along with it, the demand for mobile testing continue to trend exponentially every year. Based on the study published by Capgemini, Sogeti and HP in 2013 -2014 around global testing trends, there is an average of a 70% increase in mobile testing practices across industries. MORE

Work Around For ShellRunas With Office on Windows 8

At Exsilio we do a lot of consulting work.  As a result we often need to use client provided credentials on our Exsilio work stations.  This can be difficult if the credentials provided are active directory based, since most of us are running on an internal active directory domain.  To get around this we use a cool little Explorer extension called ShellRunas which is part of the Sysinternals Suite (a free collection of useful system utilities for Windows). MORE

Maps aren’t just for directions!

There’s a good chance that you looked at a map recently. Maybe it was the GPS in your car or on your phone. Or it may have been on an app to help you find a place to eat or fuel up at a gas station. Every time you use one of these maps you are utilizing a Geographic Information System. Until about 5 years ago, if someone said ‘GIS’ they were probably talking about ESRI’s ArcMap. Today, the term GIS is more ambiguous and can be applied to a lot of different solutions that involve a map. MORE

Best Practices for Social Media Engagement

With new emerging technology and the internet at our fingertips, Social Media is changing the marketing world today and offering a unique way for marketers to engage customers. Not only does it promote news or updates in real time, it also allows interaction between the organization and their customers via comments, Likes, feedback or sharing. MORE

Practicing Clichés

You can't just execute an idea that worked for someone else and expect it to perform better for you. This is how best practices get people in trouble; an idea, concept, campaign, etc, turns into a practice, diminishing opportunities for innovation and creativity.

MORE

Mysterious “File Access Denied” in Windows 8

Often times in the past, I try to delete a file or folder only to be presented with a message kindly reminding me that I can’t delete because it has been locked by another application, So I find the program that has the file open and close it. That’s fine with me because I understand the kernel needs to protect the file to maintain stability.

MORE

The Most Efficient Conversion

Conversions are the most sought after end-goal for most online advertisers; it declares that their efforts have worked. These often come at a cost, and require hours of effort and optimization to ensure maximum efficiency.

MORE

How to Talk to Strangers

I’ve always felt very comfortable walking up to strangers and starting a conversation. Since I was very little, my CEO father took me to business meetings with him and forced me to walk up to his friends and say hello – most often, I had never met these people before.

MORE

Coming Full Circle

Well, here I am again. I find myself back at Exsilio after a 3 year hiatus to see what else is out there in the professional world of software development. I’m lucky enough to live in Irvine, CA.

MORE

Exsilio Out and About

In our latest venture outside the office and the land of code and Expression Studio, we have teamed with the Irvine Chamber of Commerce to bring "Legends and Leaders" to the Orange County business community. In its second year, Legends and Leaders is an extradordinary event showcasing standouts in corporate Orange County.

MORE

Loading more content...