See all products »
Curah! curation service
Microsoft Tech Companion App
Microsoft Technical Communities
Microsoft Virtual Academy
Server and Tools Blogs
TechNet Flash Newsletter
Cloud and Datacenter
Windows Server 2012 R2
System Center 2012 R2
Microsoft SQL Server 2012 SP1
Windows 8.1 Enterprise
See all trials »
Microsoft Download Center
TechNet Evaluation Center
Microsoft Virtual Academy
Free Windows Server 2012 courses
Free Windows 8 courses
SQL Server training
MCSA: Windows 8
Windows Server Certification (MCSE)
Private Cloud Certification (MCSE)
SQL Server Certification (MCSE)
Second shot for certification
Born To Learn blog
Find technical communities in your area
For small and midsize businesses
For IT professionals
For technical support
For home users
Microsoft Premier Online
Microsoft Fix It Center
Security Bulletins & Advisories
International support solutions
Log a support ticket
Not an IT pro?
Microsoft Customer Support
Microsoft Community Forums
Insufficient data from Andrew Fryer
The place where I page to when my brain is full up of stuff about the Microsoft platform
Application Lifecycle Management
Data Protection Manager
Essential Business Server
Full Text Search
Microsoft Security Essentials
Performance Point Server
Read Only Domain Controller
Remote Desktop Services
Slowly Changing Dimensions
Software + Services
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server Express
TechNet Road Show
Windows Phone 7
Windows Server 2008
Windows Server 2008 R2
Windows Server 2012
Windows Server 2012R2
Browse by Tags
Insufficient data from Andrew Fryer
Unnatural Hierarchies in Analysis Services 2008
Continuing on from my last post, I explained what Natural Hierarchies were but I thought it would also be worth mentioning what the opposite was and the issues around that. Unnatural hierarchies can be created between any two attributes for example in the world of human resources (HR) it is becoming...
5 Sep 2007
Many to Many Dimensions
One of the many cool things that's already in Analysis Services in SQL Server 2005 (SSAS) is the concept of many to many dimensions. Suppose I have 3 bank accounts and one of those I share with my wife, and on another I am co-signatory for a local charity. This means there is a many to many relationship...
22 Jan 2008
Spatial and OLAP data in Reporting Services
I spent a very interesting afternoon last week with the IT team at the RSPB to explore what they could do to combine their mapping data with the olap cubes they have in analysis services. The only out of the box solution in SQL Server for spatial data is the map control in reporting Services (in SQL...
17 Sep 2010
Excel add ins for Analysis Services
Excel and analysis services are pretty open technologies so it’s very easier for anyone to grow their own stuff in for a richer experience, and even Microsoft provide things like the data mining add-ins which follow this principle. These add-ins are not confined to Microsoft and they are not always free...
30 Jun 2009
SQL Server Advent Calendar 8 – Analysis Services Named Sets
Day 8 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Analysis Services allows you to create a set of things you are interested in and persist this in the cube. MDX is used to define the set e.g. CREATE SET CURRENTCUBE.[Top 10 Resellers] AS TopCount([Reseller].[Reseller]...
8 Dec 2008
Making Hierarchies Natural
I though it might be worth another post on hierarchies in Analysis Services 2008 CTP4 to show some more attribute relationship. If we look at the attribute relationships in the date dimension of adventure works below: the relationships match the hierarchies and so the design is well optimised and there...
6 Sep 2007
Hierarchies avoid arguments
Just when you thought you had heard the last of hierarchies there's another post. This one is a little different - it's about managing confrontation and arguments, not with the code in the SQL server CLR but with other humans! In a hierarchy you navigate upwards to see information at a summary level...
10 Sep 2007
Whatever happened to the Virtual Cube?
I got asked a very odd question today - "How do you use virtual cubes in Analaysis Services 2005?". I was momentarily speechless (a rare thing for those that know me!). This got me to thinking that there must be quite a few installations of the previous version analysis services 2000 (AS2000) out there...
17 Sep 2007
Open Business Intelligence, spoilt for choice
Let me start with a little history of Microsoft BI. Microsoft acquired an OLAP tool from a company called Panorama , project name Plato, which was bundled in with SQL Server 7.0 and from this grew the present incarnation of Analysis Services which IMHO is the crown jewel in the company's BI armory, it...
21 May 2008
Deprecated Features in SQL Server 2008
My first ever post was about the features in SQL Server 2000 that were deprecated in SQL Server 2005 i.e. they wouldn’t be available in the next release (SQL Server 2008). In the same way there are SQL Server 2005 features that will be deprecated in SQL Server 2008, they will still work and be supported...
27 May 2008
Using TinyInt as keys in Analysis Services
Fact tables should be narrow and deep. If you are recording every debit card transaction for every customer across the fifteen retail banks you own, you end up with 15 million rows a day, so to save space they need to be as thin as possible. If one of your dimensions can only ever have a...
30 May 2008
Installing Denali ctp3–how hard can it be
l forget sometimes that installing SQL Server isn’t that straightforward, in part because it is now so much more than the database engine, that was SQL Server 7 when I started. I also think its important that you understand the choices you are making when installing it although many of these can...
19 Aug 2011
The Unified Dimensional Model (UDM)
It's coming up to London Fashion Week so I thought a post on models for all you dedicated followers would be good. The models in this post are rarely size zero ! So what is one of these? Technorati isn't much help but if you look hard you will find some posts e.g. by one of my predecessors Mat Stephen...
15 Sep 2007
Licensing and Scaling up SQL Server BI
Both Standard and Enterprise editions of SQL Server come with a lot more than just a database engine, there’s also a suite of BI tools: Integration Services (SSIS) Reporting Services (SSRS) Analysis Services (SSAS) which you are also licensed to use on the same server as the database engine. That...
3 Feb 2009
Analysis Services and Excel Pivot Tables
Excel is the number one business intelligence tool; It fronts Hyperion, Cognos Financial Planning, and of course SQL Server Analysis Services and Performance Point Server Planning. In Office 2007 there is now excel services so that information workers can park their work on SharePoint where it...
12 Feb 2008
Virtualising Analysis Services
Microsoft reckon that 7% of workloads are virtualised, so there’s still a lot of consolidation work to be done, but how high up the list should SQL Server Analysis Services (SSAS) be? In order to answer this it’s important to understand what SSAS is there to do, which is to provide a high performance...
16 Mar 2009
I saw a discussion the other having a go about the fact that it is possible to create KPIs in a number of Microsoft tools and how confusing that can be and I thought it would be good to understand why there is a choice at all. Firstly the term KPI mans key performance indicator, I mention this...
1 Apr 2011
Hierarchies in Analysis Services
Looking at that product dimension for my last post, I noticed that there were five hierarchies defined for the product dimension. A hierarchy is a very important part of any OLAP engine and allows users to drill down from summary levels down to detail levels much as you might want to zoom in on a virtual...
4 Sep 2007
MetaData in the Microsoft BI stack
Metadata is “data about data” and in the BI world this means two things: Definitions of terms, particularly calculations so that business users can understand what they are looking whether on screen or on paper. Lineage to understand where and how the data in a report was derived. There are a number...
20 Apr 2009
Using Oracle data with Microsoft Business Intelligence
Before I joined Microsoft, I was working on a large project to build SQL Server 2005 analysis services cubes from data in Oracle 10g. The fun we had trying to get the data out and setup the servers! Getting the OLEDB drivers and the Oracle client tools working in a 64-bit environment is at...
15 Apr 2008
Making MDX interesting
Chris Webb MVP can, to quote a friend of mine can, “Make MDX sound interesting”. I have to say when I first when on a course on MDX the tutor went for the “Make MDX confusing and boring” approach. Chris has recently coauthored a book on Expert Cube Development with Microsoft SQL Server 2008 Analysis...
7 Aug 2009
Excel OLAP add-in
Excel already has always had excellent support for analysis services, but to get the best form it you need to use a version of office later than than the version of SQL Server you are connecting to. So for analysis services 2000 excel 2003 is OK but if you connect to analysis services 2005 then...
10 Mar 2008
SQL Server Databases and Cubes on one Server
I have put Business Intelligence into a number of small businesses and these guys typically have one server set a side for BI, so how do you cram the database and the cubes into one box to get the best performance? SQL Server databases and Analysis Services (SSAS) both love memory, and even windows server...
22 Aug 2008
Analysis Services cubed in SQL Server Denali ctp3
I have now managed to install analysis services (SSAS) in SQL Server Denali ctp3 three times to reflect the three installation options that now exist .. The top instance is the table based installation that’s completely new for SQL Server Denali Then I have olap based SSAS instance that I have deliberately...
21 Jul 2011
Upgrading SQL Server Business Intelligence
Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence components of SQL Server, so here’s a simple grid that I have adapted from Scalability Experts for use in the upgrade workshops and presentations I give: Along the top is the upgrade...
28 Mar 2008
© 2014 Microsoft Corporation.
Privacy & Cookies