Analysis Services
29.3K views | +5 today
Follow
 
Scooped by Irawan Soetomo
onto Analysis Services
Scoop.it!

NON EMPTY BEHAVIOR Property

Addition: measures.[calculation] = measures.[A] + measures.[B]. Non_empty_behavior must be set to both underlying measures. If you have such measures, be sure to read Build in Calculations in Fact Tables. It is often possible to achieve better performance by performing calculations in your fact table instead of using calculated members.

Subtraction: measures.[calculation] = measures.[A] - measures.[B]. Non_empty_behavior must be set to both underlying measures. If you have such measures be sure to read Build in Calculations in Fact Tables. It is often possible to achieve better performance by performing calculations in your fact table instead of using calculated members.

Multiplication: measures.[calculation] = measures.[A] * measures.[B]. Non_empty_behavior can only be defined if you can guarantee that either [A] or [B] is never empty. If so you can define non_empty_behavior to be equal to the other measure (the one which is NOT guaranteed to always have a value).

Division: measures.[calculation] = measures.[A] / measures.[B]. Non_empty_behavior must be equal to the numerator ([A]).

more...
No comment yet.
Analysis Services
My collected tips on SSAS
Curated by Irawan Soetomo
Your new post is loading...
Your new post is loading...
Scooped by Irawan Soetomo
Scoop.it!

Azure Machine Learning - Your first experiment

Azure Machine Learning - Your first experiment | Analysis Services | Scoop.it
In a previous article, we talked about Azure Machine Learning (ML). This time we will create our first ML model from 0 to predict data. The predictions will be obtained from the data stored in an Azure SQL Warehouse. If it is your first time with Azure, Machine Learning and Azure SQL Warehouse, this article is for you. We will start from 0.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Power BI | Interactive Data Visualization BI Tools

Power BI | Interactive Data Visualization BI Tools | Analysis Services | Scoop.it

See your company's data in new ways with interactive data visualization BI tools from Microsoft Power BI.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

MDX with SSAS 2012 Cookbook

MDX with SSAS 2012 Cookbook | Analysis Services | Scoop.it

In this book you’ll find 90 clearly written recipes to help developers advance their skills with the demanding but powerful language MDX and SQL Server Analysis Services. All leading to greatly improved business intelligence solutions.

Irawan Soetomo's insight:
Adventure Works for SQL Server 2012, http://msftdbprodsamples.codeplex.com/releases/view/55330.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Dave Rodabaugh's Analysis Services interview questions

I admit to being a real hammer when conducting an interview. I take an adaptive approach, by starting with the high-level questions about architecture, business problems, and design philosophies. If the candidate fails those questions (and most do) then I begin to drill down into their technical knowledge. Just because a candidate can’t fill the role of architect doesn’t mean they aren’t productive when working under an architect. I’m sad to report that most people who fail the high-level questions also seem to know very little about Analysis Services’ product features. What’s shocking is that some of these people are actually giving clients architectural advice! Why can’t people admit “I don’t know the answer to that question” when peppered in an interview? Why try to bluster and bluff your way through it? Do you think I won’t know what you’re doing?! (Apparently, the pretender detector for most interviewers isn’t very sensitive as many of these people continue to land Analysis Services gigs.)

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Using SSRS Reports with SSAS Cubes

Take your reports to the next dimension! In this session we will discuss how to combine the power of SSRS and SSAS to create cube driven reports. We will talk about using SSAS as a data source, writing MDX queries, using report parameters, passing parameters for drill down reports, performance tuning, and the pro’s and con’s of using a cube as your data source.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

What is the best datatype?

Surrogate keys: tinyint, smallint, int, bigint

 

Date key: int in the format yyyyMMdd

 

Integer measures: tinyint, smallint, int, bigint

 

Numeric measures: smallmoney, money, real, float

(Note that decimal and vardecimal require more CPU power to process than money and float types) 

 

Distinct count columns: tinyint, smallint, int, bigint

(If your count column is char, consider either hashing or replacing with surrogate key)

Irawan Soetomo's insight:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Building a large scale relational data warehouse is a complex task. This article describes some design techniques that can help in architecting an efficient large scale relational data warehouse with SQL Server. Most large scale data warehouses use table and index partitioning, and therefore, many of the recommendations here involve partitioning. Most of these tips are based on experiences building large data warehouses on SQL Server 2005.

Irawan Soetomo's insight:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/44aa6b9b-d92c-4acb-934a-aee09f225fec/quick-question-in-using-nvarchar-and-varchar

 

Best Practices for Business Intelligence Using the Microsoft Data Warehousing Framework, https://technet.microsoft.com/en-us/library/aa902663(v=sql.80).aspx.


more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Cassandra NoSQL Database: Getting Started

Cassandra NoSQL Database: Getting Started | Analysis Services | Scoop.it

Cassandra is not a relational data store, despite its use of the term “column-oriented.” In fact, it doesn’t really look anything at all like a relational database. Instead of storing a schema, for example, that guarantees the various rows of data in the table are all alike, Cassandra stores “column families” in “keyspaces.” A keyspace is really just an administrative isolation barrier, in much the same way that relational database instances are separated from one another on the same server, but a column family is a completely different beast. Each column family is made up of “rows” identified by a key, but within a row, any number of name/value pairs (columns) can be present, and each row can contain entirely different data elements from the other rows within the column family.

Irawan Soetomo's insight:

http://www.zdnet.com/cassandra-2-0-the-next-generation-of-big-data-7000020237/

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

10 Helpful Excel PivotTable Tips For Quick & Efficient Data Analyis

10 Helpful Excel PivotTable Tips For Quick & Efficient Data Analyis | Analysis Services | Scoop.it

If you’re pretty good with Microsoft Excel (start here if you’re not!), you know that Excel is fantastic for number crunching. But did you know that Excel also comes with a tool that is specifically designed to analyze huge sets of data? That tool is the PivotTable, and it is arguably the most powerful tool in Excel. There’s simply no better way to quickly make sense of a large set of data. And because PivotTables are extremely versatile, you can find opportunities to use them in any business setting. PivotTables are fast, flexible, and extremely accurate. Once you learn how they work, they will change the way you look at data analysis in Excel forever. Thus, learning PivotTables is a great career move, and this collection of tips will help make sure you’re getting the most out of PivotTables as you begin to use them to analyze data in Excel.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

BI Documenter

BI Documenter | Analysis Services | Scoop.it

BI Documenter outputs "MSDN style" SQL Server documentation in either compiled help (CHM) or HTML format. While documenting all of the major SQL Server components, BI Documenter also documents the entire BI stack, including Analysis Services (SSAS), Integration Services (SSIS), and Reporting Services (SSRS).The unique documentation snapshot feature enables archiving of historical SQL Server documentation sets. New documents can be regenerated at the touch of a button. BI Documenter also allows you to customize the look and feel of your documentation with the ability to add your own branding.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Process Incremental - What it is & isn’t

Process Incremental - What it is & isn’t | Analysis Services | Scoop.it

Process Incremental is extremely useful to process facts having journal entries. In a journal fact, updates to existing fact records are balanced by a corresponding negative or positive journal entry. In Process Incremental mode, a SQL query has to be provided that could identify the records inserted into the fact journal since last cube process.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Speed up dimensions using a NULL default cube measure

Speed up dimensions using a NULL default cube measure | Analysis Services | Scoop.it

Recently I faced some problems with the performance of SSAS dimensions. The cube users were using a large dimension with more than 100.000 members that didn't perform well.


They tried to add a leaf dimension member on an Excel 2007 pivot table. When dragging this dimension member onto the rows they had to wait very long before the members returned from SSAS and showed on the screen.

 

After some mailing with Chris Webb he thought this could have something to do with the default cube measure. It seems that when you query dimension members without picking a measure, SSAS takes the first measure from the first measure group as its default measure. So even when you only query a dimension, SSAS is still using a measure!

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Datazen – Mobile BI and Data Analytics for Any Device

Datazen – Mobile BI and Data Analytics for Any Device | Analysis Services | Scoop.it

Business Intelligence dashboards and data analytics for any device. Featuring mobile dashboard designer, native apps for Windows, iOS and Android, and support for any modern browser via HTML5.

Irawan Soetomo's insight:
Share your insight
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Dimensional Modeling: Junk vs Degenerate

Dimensional Modeling: Junk vs Degenerate | Analysis Services | Scoop.it

Two commonly misunderstood dimensional modeling techniques are Junk Dimensions and Degenerate Dimensions. This post is aimed at clearing up the confusion and providing some context and use-cases for each.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Dimension Attribute Relationships

So, let get this straight... we have gone to the trouble of defining our Dimension Hierarchies to ensure that they are nested correctly and display correctly but they still wont work correctly for aggregation?

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Using Excel to interact with a SSAS cube

Using Excel to interact with a SSAS cube | Analysis Services | Scoop.it

Sometimes we need to access and work with a SSAS cube using Excel. In this tip we will show how to: access SSAS with Excel, add Dimensions to a cube, work with SSAS KPIs, add SSAS Named Sets, see the MDX Query that Excel creates, create new calculations using Excel, and find Multidimensional Members

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SQL Server Best Practices Article

The best practices recommendations are grouped into the following sections:

- Data Source Design Best Practices

- Dimension Design Best Practices

- Cube Design Best Practices

- Partition Design Best Practices

- Aggregation Design Best Practices

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Date Dimensions

Date Dimensions | Analysis Services | Scoop.it

In this article we will discuss:

- A date dimension that is used as a reference dimension
- A date dimension that is generated and stored on the SSAS server (no physical table)
- Advantages and disadvantages of using smart date key e.g. YYYYMMDD 
- Enabling users to select a date (or month) to be used in calculated measure using 'from date' and 'to date'
- Other topics about date dimension in SSAS
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Lessons Learned from Poor Data Warehouse Design

Lessons Learned from Poor Data Warehouse Design | Analysis Services | Scoop.it

It’s often said that the best way to learn is from your mistakes, but mistakes made in a data warehouse/business intelligence (BI) environment tend to be very expensive and possibly career-killers. Building a data warehouse isn’t a simple task; it takes a village to build one out of which you can extract viable information.

 

A data warehouse and the operations that build and maintain it are a combination of business considerations and design best practices. It can be a huge challenge to balance these requirements and still be able to deliver valuable content to users at the end of the day. Make no mistake—not everyone is suited to work in a data warehouse environment, but the work is stimulating and the rewards are satisfying. Here are some of the lessons I’ve learned from designing data warehouses and BI environments.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Introduction To PowerPivot for Excel 2013

Basics of PowerPivot: Why PowerPivot?, Add to Data Model, Relationships, Calculated Column, Calculated Field, Build report with PivotTable based on Data Model.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

PDW: The high performance SQL Server Data Warehouse solution

SQL Server is one of the most used and loved data warehouse platform today but did you know there is a specialized version of SQL Server, the SQL Server Parallel Data Warehouse Appliance, specially built for high scale and high performance analytics needs? Watch this video to learn about SQL Server PDW and see why you might want to evolve your SQL Server data warehouse to PDW so you can experience the next level of scale and performance for your SQL Server data warehouse.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Power BI

Power BI | Analysis Services | Scoop.it

Easily discover and connect to data from public and corporate data sources. This includes new data search capabilities, as well as capabilities to easily transform and merge data from multiple data sources so that you can continue to analyze it in Excel.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Microsoft SQL Server 2008 Analysis Services

Irina Gorbach Alexander Berger Edward Melomed ®Microsoft SQL ™
Irawan Soetomo's insight:

Buy the book!

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Dimension Data Type Discrepancy Check

Dimension Data Type Discrepancy Check | Analysis Services | Scoop.it

The main scenario this feature is designed to help with is as follows. If you build a dimension attribute off a varchar(100) column, then you lengthen that column to varchar(200) in the SQL database, then refresh your DSV, that length change doesn't get replicated to the KeyColumn or NameColumn of dimension attributes which still have a DataSize property of 100. If any data in that column is longer than 100, you will receive an error during cube processing that says, "The size specified for a binding was too small, resulting in one or more column values being truncated."

more...
No comment yet.