Wise Owl blogs written by Andy Brown (page 1 of 11)

Showing blogs 1-20 (out of 205)

How to crash Analysis Services (Tabular Model)

Posted by Andy Brown on 05 February 2016 | no comments

It's not often you find a place to right-click which is pretty much guaranteed to crash a software package, so I thought I'd share this discovery!

Common Table Expressions are even better than I'd realised, as this blog shows

Posted by Andy Brown on 01 February 2016 | no comments

CTEs (or Common Table Expressions) allow you to divide complicated queries up into two or more simple discrete tasks, as this blog explains.

Tags:   SQL | CTEs, subqueries

A short blog listing many ways to create aliases for columns in SQL

Posted by Andy Brown on 01 February 2016 | no comments

It's not often that this owl learns new things about SQL when giving a course, but when I do, I like to share my new knowledge! This blog shows a new way to create aliases for columns in SQL.

Tags:   SQL | Selecting data

How to create absolute and relative KPIs in SSAS Tabular

Posted by Andy Brown on 28 January 2016 | no comments

You can create key performance indicators (KPIs) within Analysis Services Tabular, but there a couple of foibles that you need to be aware of, as shown by this blog.

Ways to model different DAX calendar date situations

Posted by Andy Brown on 22 January 2016 | no comments

In the real world (or at least, the DAX version of it), calendars and dates aren't quite as simple as one would like. This blog shows how to solve a number of common scenarios, including where different tables have different levels of "granularity", coping with special days like bank holidays, handling non-standard finanical year-end dates and dealing with a table which contains more than one date column.

How to create a calendar in SSAS Tabular to summarise by dates

Posted by Andy Brown on 21 January 2016 | no comments

Analysis Services has lots of wonderful DAX functions like TOTALYTD and SAMEPERIODLASTYEAR which allow you to summarise your results by date, but to get them to work you'll need to create and use a calendar table first. This blog explains how, and why.

How to use the EARLIER function in DAX to sort, group, band and accumulate data

Posted by Andy Brown on 13 January 2016 | no comments

Although the EARLIER function in DAX is complicated, it's also very useful! This blog shows how the function works, and how to use it to create running totals, sort rows, create group statistics and divide data into bands.

How to use the RANKX function in DAX measures to sort data

Posted by Andy Brown on 12 January 2016 | no comments

The RANKX function is one way (probably the best one) to sort data. It's not the world's easiest function to understand, but this blog explains the pitfalls.

How to use the FILTER function in DAX to filter tables

Posted by Andy Brown on 12 January 2016 | no comments

Although the CALCULATE function is by more useful (and often easier to understand), DAX programmers should also learn about the FILTER function. This provides another way to change the query context for any aggregation.

How to use the CALCULATE function in DAX measure formulae

Posted by Andy Brown on 12 January 2016 | no comments

The CALCULATE function is the most important one in DAX. This blog shows you how to use it to replace, remove and amend the query context for a measure (and also explains what this sentence means!).

Using DAX Studio or DAX Editor as add-ins to create measures in SSAS Tabular

Posted by Andy Brown on 11 January 2016 | no comments

The built-in DAX formula editor in SSAS Tabular leaves a lot to be desired. This blog shows how to install and use two of the most useful third-party add-ins: DAX Editor and DAX Studio.

A must-read DAX primer on how to create measures (including query context)

Posted by Andy Brown on 11 January 2016 | no comments

To create formulae in SSAS Tabular you need to learn DAX, the language used for creating measures. This blog shows the basic syntax of DAX, and explains how SSAS Tabular uses query context in a pivot table to aggregate data correctly.

How to create dynamic connection strings in Integration Services packages

Posted by Andy Brown on 08 January 2016 | no comments

It's often useful to make connections point to different Excel workbooks or SQL Server databases, depending on the value you set for variables or parameters. This blog shows the underlying principle - it's up to you then to apply this in your workplace!

Tags:   SSIS | Data flow    |    SSIS | General SSIS

How to run SSIS packages in 32-bit mode to avoid Excel and Access errors

Posted by Andy Brown on 08 January 2016 | no comments

If you're using SSIS to import from or export to Excel workbooks or Access tables - as you surely will be - you'll need to run your packages in 32-bit mode. This blog explains why and how to do this.

Tags:   SSIS | Deployment    |    SSIS | General SSIS

Using the RELATED function to link tables, and how to test for blanks in DAX

Posted by Andy Brown on 05 January 2016 | no comments

You can make pivot tables much easier to use by combining all of the aggregator columns into a single table, using the RELATED function. This blog also shows you how to work with blanks, including using the ISBLANK function to test whether matching values exist in linked tables.

Learn how to create calculated columns in a tabular model using DAX

Posted by Andy Brown on 05 January 2016 | no comments

Creating calculated columns in tabular models is deceptively similar to creating formulae in Excel. Deceptively, because the underlying language (DAX) is completely different, and you can make use of functions like SWITCH which have no Excel equivalent. This blog shows what calculated columns are, and how to create them.

A tutorial showing how to import data from Excel, Access and many other sources

Posted by Andy Brown on 04 January 2016 | no comments

You can import data into tabular models from virtually any data source, as shown by this tutorial.

Referring to a range using square brackets

Posted by Andy Brown on 17 December 2015 | no comments

This short blog shows that there is a third way to refer to a range, other than using the RANGE or CELLS keywords.

This blog explains the lesser known but better Excel VBA InputBox function

Posted by Andy Brown on 17 December 2015 | no comments

There are two ways you can display an input box using Excel macros: the standard VBA InputBox function, or the much better (but lesser known, at least by this blog's author) Application.InputBox function.

A tutorial on how to create Power View reports based on SSAS tabular models

Posted by Andy Brown on 08 December 2015 | no comments

Power View is an add-in included within Excel 2013 and later versions. This tutorial shows how to create Power View reports based on Analysis Services tabular models.