Who is online?  0 guests and 0 members
Home  »  Blogs  »  MarkGStacey

Communifire Blogs

Blogs RSS Feed

MarkGStacey : Most Recent postings

MarkGStacey

Persisting DAX queries

3/6/2013 by MarkGStacey  -  Comments: 0  -  Views: [2597]

This blog has moved to : http://markgstacey.net/2013/02/11/persisting-dax-queries/ In this post, I’m going to show you how to connect a tabular mode query to the cube it is sourced from – the question that I got asked by Marco Rus when I worked this out was : Why? Why would you do that? Well, there are a couple of reasons. If we think about SQL, we have the ability to persist a query that we have written, and then query it as if it was a table – our goal is to persist a DAX que...

Read More

MarkGStacey

Microsoft Integration Stack

2/14/2013 by MarkGStacey  -  Comments: 3  -  Views: [11189]

Note: This blog post has moved to : http://markgstacey.net/2013/02/12/microsoft-integration-stack/ Microsoft Integration Stack I am often asked the question about WHICH Microsoft technology to use when integrating systems, and it’s a very relevant question, as there are so many different technologies. The good news is that each does have its’ place – there is some overlap, and most of these can be shoehorned to do the other roles, but for the most part, it’s fairly clearc...

Read More

MarkGStacey

Visual Intelligence

2/12/2013 by MarkGStacey  -  Comments: 0  -  Views: [703]

Cross post from : http://markgstacey.net/2013/02/12/visual-intelligence-almost-complete/ Today (minutes ago really) I finished up the author review, and the final thorny piece of code on my new book – we have one chapter from one of my co-authors to go and we’re in the final stretch. It has been a long hard slog, with some seriously late nights to get it out, but damn it feels good to be done. The book is called Visual Intelligence, and is a 3 part book – firstly, some basics o...

Read More

MarkGStacey

MarkGStacey.NET

2/12/2013 by MarkGStacey  -  Comments: 0  -  Views: [709]

After a steller year of brand building in 2012, I'm focusing on building my personal brand in 2013: as part of this, I've launched http://markgstacey.net , where I am crossposting my BIDN entries. With especial references to my Visio shapes files, I think this will be a user friendly home. I will still be active on the BIDN forums.

Read More

MarkGStacey

Visio Shapes for SQL 2012 - SSAS (OLAP & Tabular), Master Data Services, and Data Quality Services Servers

2/12/2013 by MarkGStacey  -  Comments: 1  -  Views: [18280]

(Cross posted to http://markgstacey.net/category/office/visio/ ) I often use Visio diagams for architecture, and not having a shape for a SSAS server was always annoying. I'd built one out previously, but never took the time to polish it (which mostly meant make the text editable....) Now that I needed one for Tabular mode servers as well, I took the 20 minutes to fix the editable text. Thought this might be useful. You can download the Visio Stencil here http://www.bidn.com/Assets/Uploaded-CMS-...

Read More

MarkGStacey

Accessing Sharepoint List data from SSIS Part 1

5/12/2012 by MarkGStacey  -  Comments: 2  -  Views: [8383]

With Sharepoint rapidly becoming the data store of choice for user driven and user built data capture systems, getting this data into your warehouse is becoming more and more important. There are three primary methods of doing this from SSIS. 1. Install 3rd party Sharepoint List Adapters. CodePlex even has a free version at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 2. Write your own adapter. Again, the CodePlex project is a great start 3. Create a script component to query Sha...

Read More

MarkGStacey

Aggregated Lookups in SSRS 2008R2

5/11/2012 by MarkGStacey  -  Comments: 1  -  Views: [14815]

Aggregating over Lookups in SSRS SSRS 2008 R2 has a feature to do a lookup to another dataset, but no way to trivially aggregate the value. It’s a fairly simple solution: Use a small piece of code to do the summing. Now, you may have various LookupSets, so you don’t want to code a function for each, so we store the values in a dictionary lookup. Then when you query at the sum level, you just query the dictionary Dim dValueList As New System.Collections.Generic.Dictionary(Of String, D...

Read More

MarkGStacey

An open letter to Microsoft - BI needs to be a single message across your products

5/2/2012 by MarkGStacey  -  Comments: 5  -  Views: [6294]

An open letter to Microsoft – BI challenges. To preface this letter, let me just say that I have drunk the kool-aid, just like the Merry Pranksters drank Ken Kesey’s koolaid ( http://en.wikipedia.org/wiki/The_Electric_Kool-Aid_Acid_Test ), and evangelise Microsoft products on a daily basis – indeed, on behalf of Microsoft in our Expedition Denali and SQL Server 2012 roadshows. So don’t get me wrong, I *love* this stack, and develop on it as my first choice. I just want it...

Read More

MarkGStacey

Partitioning in Tabular SSAS

4/15/2012 by MarkGStacey  -  Comments: 1  -  Views: [7041]

SQL Scripts: http://www.bidn.com/Assets/Uploaded-CMS-Files/PartitoningInTabular-ed77bbde-367f-4fdc-b1aa-404423f0d05e.zip This blog as a document: http://www.bidn.com/Assets/Uploaded-CMS-Files/Partitioning in Tabular-e2b2799d-a10a-41a2-ae3a-22895fe33b2e.docx Partitioning in Analysis Services has, for most devs, generally been a post hoc effort - build your cube first, then during productionisation, add the partitioning schema (or even just let the DBAs do it….). And this mostly worked, bec...

Read More

MarkGStacey

SQLBits - PowerPivot on NASA data

4/8/2012 by MarkGStacey  -  Comments: 0  -  Views: [1532]

So a lot more attendees at the 8 AM session than I expected. A bit of honest feedback from myself - I geeked out too much on the data we were analyzing, and lost focus on the toolset. Lesson learnt. Discovering a planet live on stage using PowerPivot was uber cool, but the tricks we were using to do it were perhaps a little above the 200 level the PowerPivot session was pitched at. Not bad for the first time I presented this data - some technical issues (PowerPivot being SLOW), and I could have ...

Read More

MarkGStacey

Analysis Services for the DBA

1/5/2012 by MarkGStacey  -  Comments: 2  -  Views: [2064]

To many DBAs, Analysis Services is just a tick box on the SQL install, and the use case is arcane and not worth looking at. In this session, you will discover why the BI developers asked you to install an instance, and, even more, how you yourself could easily make use of it's features. Large datasets, accelerated report creation, central management of calculations ~ you'll cover all of all these, and then, dive into the nuts and bolts of managing an Analysis Services cube. Slide deck

Read More

MarkGStacey

Average Monthly calculations

11/23/2011 by MarkGStacey  -  Comments: 1  -  Views: [2437]

You’ll often be tempted to build a CASE WHEN Level calc to get an average monthly calc, but there’s an better way. You'll notice the error when using CURRENTMember and in the client tool doing a multi-select, it doesn't work because it doesn't have a single member to work against - this solution fixes this See below for an example against Adventure Works: with member Measures.MonthlySales as --CASE --WHEN --(EXISTING [Date].[Calendar].Members).Item(0).Level IS [Date].[Calendar].[Mont...

Read More

MarkGStacey

Road to PASS Summit 2012

10/23/2011 by MarkGStacey  -  Comments: 2  -  Views: [2127]

Over the last few years, I've done more and more public speaking, at user groups, Training the Ts with Pragmatic Works, at SQL Saturdays, at Sharepoint saturdays, and even at the ITWeb BI conference, and in 2011, for the first time, I tried to get a slot at #SQLPASS I didn't get it, so this year I am going to approach the task of getting a speaking slot much more strategically. And the way I'm going to do this is two-fold : firstly, many many more speaking slots, at least one webinar a month, an...

Read More

MarkGStacey

Architecting a BI Solution - it's more than just the hardware

10/23/2011 by MarkGStacey  -  Comments: 0  -  Views: [1594]

BI is complex, and it's difficult to manage, and that's just the way it is. Actually, this isn't the case, IF you've designed your solution correctly. Architecting and building a BI solution is about choosing the right hardware, fitting the hardware you have chosen (or have already) to the solution you need, and designing your processes correctly - ETL, deployment, and even data release are all important. In this session, you will learn about several different architectures for different environ...

Read More

MarkGStacey

Excel and PowerPivot - an introduction

10/23/2011 by MarkGStacey  -  Comments: 0  -  Views: [2012]

PowerPivot, while being an Excel add-in, was built by the SQL team, and is one of the most powerful data integration tools in the Microsoft stack. In this session, you will learn how to combine data from web services, tables and cubes to build a powerful model, and visualise it in Excel. An introduction to Time Intelligence using DAX will lead us on to the new features in version 2, and we will move from the Date table to relationships, perspectives and KPIs. You will also see the difference bet...

Read More

MarkGStacey

Anatomy of an analysis session

5/6/2011 by MarkGStacey  -  Comments: 2  -  Views: [2190]

Anatomy of an analysis session Today, onsite at a client, I made a mistake in an analysis session, and in thinking about that mistake, I thought I’d capture some of my thoughts about the process I go through during these analysis sessions. Very VERY rough and some very amateur psychology, so bear with me, and give any comments you think are worthwhile. These are all things I do from experience rather than had planned out in the manner below. Just to be clear, I’m talking about a project analysis...

Read More

MarkGStacey

SharePoint 2010 BI Links

5/6/2011 by MarkGStacey  -  Comments: 0  -  Views: [2923]

This post serves as a reference point for Sharepoint 2010 BI Links. Chronically disorganised by nature...... My presentation collateral: SQL Saturday Johannesburg: http://www.bidn.com/blogs/MarkGStacey/bidn-blog/1795/sql-saturday-jhb Information Worker: http://www.informationworker.co.za Pragmatic Works Webinars: http://pragmaticworks.com/Resources/webinars/Default.aspx ColorBrewer: Chart color chooser for SSRS : http://colorbrewer2.org/ Performance Point http://nickbarclay.blogspot.com/ Visio D...

Read More

MarkGStacey

SQL Saturday JHB

5/6/2011 by MarkGStacey  -  Comments: 2  -  Views: [1690]

All the collateral from my presentation at SQL Saturday sits here:

Read More

MarkGStacey

Performance Point Relative Date Time Intelligence with current date time

4/20/2011 by MarkGStacey  -  Comments: 5  -  Views: [14614]

In PerformancePoint, sometimes you want to override the date selected for the time intelligence formulae : for instance, you’re doing calculations for a rolling 3 months, and you want to change it to be rolling 3 months as at a certain date. So this is definitely possible, if not entirely intuitive. The key is to override the current date time. NB: This technique can’t be used when overriding hierarchies in rows or columns To start off, lets create a data connection to Adventure Works. Make sure...

Read More

MarkGStacey

When the Decomposition Tree in PerformancePoint is not "cool" - missing dimensions from decomp tree

4/19/2011 by MarkGStacey  -  Comments: 2  -  Views: [7228]

In PerformancePoint, sometimes your users will see that a dimension goes missing from the selectable list when using the decomposition tree - and indeed, from the entire Analytic Chart (or grid) To replicate this behaviour, create a new Analytic chart against the Adventure Works Database, with Internet Sales Amount as the measure, Product Categories as the bottom axis, and Customer Geography as the background. Create a filter based on customer geography, and make it a multi-select tree. Make the...

Read More

MarkGStacey

Excel as an ETL tool - the TRANSPOSE function

4/18/2011 by MarkGStacey  -  Comments: 1  -  Views: [11782]

(Download excel data sheet: http://www.bidn.com/Assets/Uploaded-CMS-Files/6e42b004-cc2c-4e57-8ca1-8bc1a0af872aExcelDataTranspose.xlsx ) (Download entire post : http://www.bidn.com/Assets/Uploaded-CMS-Files/b0b5ef30-ab25-497b-aa78-a40bc54ad2dfPPTraining.zip ) Many times, data is provided in an excel, delimited, or fixed width file format, and the data within this file has multiple measures in columns. Ideally, you would like to be able to slice by the dimension provided as column headers, and oft...

Read More

MarkGStacey

Using Performance Point to combine SSAS actuals and Sharepoint targets

4/17/2011 by MarkGStacey  -  Comments: 0  -  Views: [4495]

So today, I had a client request that we build a scorecard with the actuals in SSAS, and the targets in Sharepoint. This is *possible* but it needs to be done in a very specific way. Firstly, because it is not totally simple, and secondly because there are a couple gotcha’s that can wreck your day. Key point 1 : Remember to use a dimension in combination with a Key column Gotcha : Changing anything on the KPI doesn’t update til you remove it from the scorecard and add it again. Gotcha: Having a ...

Read More

MarkGStacey

Using Performance Points scorecards as filters to solve cascading dropdown and row count issues

4/17/2011 by MarkGStacey  -  Comments: 2  -  Views: [13514]

Performance Point Filters and scorecards One of the criticisms often levelled at Performance is the lack of cascading dropdowns. Not when selecting from a single hierarchy (which PPS does quite well, up to a soft limit of 5000 members), but when attempting to cross filter. For instance, in our example from the Adventure Works database, we may want to initially select bike racks from the product subcategory hierarchy of the Product dimension, and then only have 2007 and 2008 years of the date dim...

Read More

MarkGStacey

IT Web BI Roundup - What is in store in 2011?

12/15/2010 by MarkGStacey  -  Comments: 2  -  Views: [2487]

I've recently started doing the round-up for IT Web's BI newsletter ( http://www.itweb.co.za/index.php?option=com_content&view=article&id=34797&Itemid=219 ), and (with permission), I'm cross-posting the content here for those who don't subcribe: As a tumultuous 2010 comes to a close, this is a good time to take a look at what is expected in business intelligence in 2011. The biggest growth trend, coming on the back of the explosion in tablets, will be in mobile BI. Since the release ...

Read More

MarkGStacey

ITWeb BI Roundup

12/15/2010 by MarkGStacey  -  Comments: 0  -  Views: [3530]

I've recently started doing the round-up for IT Web's BI newsletter, and (with permission), I'm cross-posting the content here for those who don't subcribe: The Business Intelligence community needs to become more aware of real world data, based on sensor feeds. Business Intelligence has traditionally been used on pure IT systems - starting with financial systems, growing through ERP, and expanding into new fields all the time, such as clickstream analysis. Until recently, data sources in BI sys...

Read More