background preloader


Facebook Twitter

MDX – Tomislav Piasevoli – BI Blog. Chris Webb recently blogged about a strange phenomenon regarding sets in where clause and auto-exists.

MDX – Tomislav Piasevoli – BI Blog

Today it occured to me that I had experienced a similar issue while doing some testing a year from now. A quick search on the right words (Tomislav Darren set slicer) and the exact thread popped up first in SSAS forum search engine. After I’ve read it, I was very much intrigued by Chris’ post, so I’ve read it and tested it too, and then decided to spend few hours in order to find out what’s going on in the examples he provided, hoping to discover a general rule of this “weird” behavior and, most of all, the answer “why”.

Whether I succeeded or not, I’ll leave up to you. The explaination follows. First, let’s make the summary of facts. Here is the list of queries, how the MDX looks like, what do they return (not values, we focus on members), as well as short description of behavior/mechanism behind. select [Measures]. select [Measures]. SSAS 2016 Locking Improvements – Chris Webb's BI Blog. I first became aware of the server-wide lock taken out by SSAS when processing finishes – and the issues that this can cause – from this blog post by Andrew Calvett back in 2009.

SSAS 2016 Locking Improvements – Chris Webb's BI Blog

Azure Analysis Services Backup and Restore. This post is authored by Bret Grinslade, Principal Program Manager and Josh Caplan, Senior Program Manager, Azure Analysis Services.

Azure Analysis Services Backup and Restore

We have gotten good feedback from customers and partners starting to adopt Azure Analysis Services in production. Based on this feedback, this week we are releasing improvements around pricing options, support for backup and restore, and improved Azure Active Directory support. Please try them out and let us know how they work for you. New Basic Tier The new Basic Tier is designed to support smaller workloads with simpler refresh and processing needs. Backup & Restore We have added backup and restore. Improved Azure Active Directory integration We have also done some work to improve the way Azure Analysis Services works with Azure Active Directory. DAX Time Intelligence Explained - Excelerator BI. Using Process Add in tabular models - SQLBI. Where Azure Analysis Services Fits Into BI & Analytics Architecture (Part 3) — SQL Chick.

This is part 3 of a discussion about a new service in Microsoft Azure: Azure Analysis Services.

Where Azure Analysis Services Fits Into BI & Analytics Architecture (Part 3) — SQL Chick

Azure AS is a Platform-as-a-Service (PaaS) offering which is in public preview mode (as of December 2016). Part 1: Why a Semantic Layer Like Azure Analysis Services is Relevant Part 2: Use Cases for Azure Analysis Services Part 3: Where Azure Analysis Services Fits Into BI & Analytics Architecture {you are here} Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX. A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model.

Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext.

But, until then a workaround would be entering data to a CSV file then load it to the model. Showing December to December to see opening values for a year in SSAS/MDX – Purple Frog Systems. I came across an interesting MDX challenge this week; within a cube’s Date dimension, how to show December twice, once where it should be and again as the opening value for the following year. i.e. for each year I need to show Dec (prev yr), Jan, …, Nov, Dec.

Showing December to December to see opening values for a year in SSAS/MDX – Purple Frog Systems

Why? Well if you consider the following pivot chart, you can clearly see growth from Jan to Feb, Feb to Mar, etc., but it’s very difficult to see the growth between Dec and Jan. Week-Based Time Intelligence in DAX - SQLBI. The DAX language provides several Time Intelligence functions that simplify writing calculations such as year-to-date (YTD), year-over-year (YOY) and so on.

Week-Based Time Intelligence in DAX - SQLBI

However, if you have special calendar structure such as 4-4-5 weeks’ calendar, you need to write your custom time intelligence calculation. In this article, you will learn how to write the required DAX formulas. The Time Intelligence functions in DAX (such as TOTALYTD, SAMEPERIODLASTYEAR and many others) assume that every day in a month belongs to the same quarter regardless of the year.

This assumption is not valid in a week-based calendar: each quarter and each year might contain days that are not “naturally” related. For example, January 1st and January 2nd of 2011 belongs to week 52 of year 2010, and the first week of 2011 starts on January 3rd. Consult a cubemember with another cubemember filter. I think I understand - if one value is chosen in a slicer but there are other properties to that value, you'd like to display that property.This is poorly documented and I stumbled upon this solution by accident, but it works for me.

Consult a cubemember with another cubemember filter

If there's a shorter way I hope someone else can provide it. Assume the table is "Supplier Info" and the slicer name is Slicer_OC_Value... the property name from the OC table you want to retrieve is "Supplier Name" e.g. "abc"If the slicer is showing a value (e.g. Dynamic security made easy with SSAS 2016 and Power BI – Kasper de Jonge Microsoft BI Blog. Let’s see how row level security (RLS) plays together with the new bi directional relationships.

Dynamic security made easy with SSAS 2016 and Power BI – Kasper de Jonge Microsoft BI Blog

More information on row level security you can find in its own whitepaper here. Both RLS and bi directional relationships work by filtering data. Use Task Scheduler and PowerShell to process your SSAS database – Kasper de Jonge Microsoft BI Blog. Most of the times you will use SSIS or a SQL Agent job to process a SSAS model, but sometimes you don’t have either one at your disposal.

Use Task Scheduler and PowerShell to process your SSAS database – Kasper de Jonge Microsoft BI Blog

Today we will look at using the regular Windows Task Scheduler and PowerShell to process a partition. The first thing you have to make sure of is that the SQL Server PowerShell commandlets are installed, more on this in this help document. What is Azure Analysis Services. Built on the proven analytics engine in Microsoft SQL Server Analysis Services, Azure Analysis Services provides enterprise-grade data modeling in the cloud. #DAX – Joining to a Slowly Changing Dimension. The following is one of the scenarios that I showed during my “Drop your DAX” talk at SQL Saturday #296 in Melbourne. Currently SSAS Tabular and PowerPivot models can only have a relationship based on a single column. So what do you do when you need to join based on multiple columns? Ideally you would solve this during your ETL. With a type 2 slowly changing dimension you typically want to insert the surrogate key for the dimension into the fact table.

As you may know, “type 2” dimensions can have one or more records for a given business key. This is pretty easy to do in SQL, but what do you do if your two tables are not only on separate servers, but also on different types of servers. Parameter Table – DAX Patterns. The Parameter Table pattern is useful when you want to add a slicer to a PivotTable and make it modify the result of some calculation, injecting parameters into DAX expressions. To use it, you must define a table that has no relationships with any other table.

Its only scope is to offer a list of possible choices to the end user in order to alter the calculations performed by one or more existing measures. The DAX expression in measures checks the value selected and calculates the result accordingly. In most cases, users can select only one value, and multiple selections cannot perform a meaningful calculation. For example, the Parameter Table can choose the number of customers to show in a PivotTable sorted by sales amount, similar to a dynamic “top N customers” calculation, where a slicer selects the Nth value. Basic Pattern Example Figure 1 The Scale table does not have relationships with other tables. You define the Sales Amount measure as follows: Creating a Dynamic, Parameterized Measure in DAX.

As I continue to explore the many facets of DAX functions, I’m finding more creative ways to add business value and simplify user experiences. In this post, I will demonstrate how to use slicers to pass values into a calculated measure in an SSAS Tabular model to dynamically change the results, parameterizing the date interval, number of periods and the type of aggregation used in the measure. The DAX language can be both remarkably easy and deceptively complicated to solve business problems.

Choosing the Right Instance in Perfmon « There’s been quite a bit posted on this blog regarding the collection of perfmon counters to monitor Analysis Services instances and troubleshoot performance problems. And some of the recommended perform counters (e.g. Process: % Processor Time) are “instance”-specific… meaning you need to select the name of the process for which you want to collect counter values. I’m referring to the “Instances of selected object” box in the perfmon dialogue (shown below). After select the counter(s), you then need to select the “instance” for which you want to trace the selected counter.

DAX as a Language - Microsoft Power BI Community. Creating a Dynamic, Parameterized Measure in DAX. Patterns – DAX Patterns. Implementing Column Security with #SSAS Tabular and #DAX. Out of the box Analysis Services (both Tabular and Multi-dimensional) has great support for horizontal or row based security. An example of this is where you would give User1 access to all data where the Country is “Australia” and give User2 access to all data where the country = “United States”. This covers a large percentage of the security requirements that most people have. But neither technology has great support for vertical or column based security. Microsoft SQL Server Analysis Services 2005/2008 front end client tools, components-SSAS. Handling Data Integrity Issues in Analysis Services 2005. T.K. William E. Pearson, III.

CPA, CITP, CMA, CIA, MCSE, MCDBA Microsoft SQL Server MVP Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged.

At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. SQL Server Analysis Services Interview Questions on Dimensions, Hierarchies and Properties. Problem. Measures and Measure Groups in Microsoft Analysis Services: Part 2. Measures and Measure Groups in Microsoft Analysis Services: Part 1. Exclusive offer: get 50% off this eBook here. MDX functions: SCOPE, THIS and FREEZE. MDX also offers several new constructs specific to calculations embedded within MDX scripts – they cannot be used for queries.