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
Azure Machine Learning
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
Query Across the Mersey
SQL Bits 9 , Adelphi Hotel Liverpool 29th September to 1 October 2011, what else do you need to know? Well possibly a few things if you are now reading the second line of this post or you haven’t been to them before.. It’s the biggest and best SQL event in Europe, I know this because we get people coming...
10 Aug 2011
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
I am in the middle of SQL relay this week, but I just realised that you might like to have a copy of my Denali slide deck, which I have now posted to SkyDrive . You are welcome to have a look at it even if you couldn’t make one of the four venues I am presenting at this week, but there isn’t a lot of...
5 Oct 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
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
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
the BI Sematic Model in SQL Server Denali ctp3
Some sort of semantic model is needed in every BI solution but what is it and why do I need one? It’s a view of the data store(s) you want to work on for business intelligence which adds additional information that can be stored in the individual tables themselves. For example how are the...
25 Jul 2011
Mental Models - Attributes in Analysis Services
I had many happy debates with a business analyst on one of my recent projects because he was bought up an an early olap product called HOLOS and he couldn't see how the dimensional model I was presenting was ever going to work. If we had been using HOLOS, and pretty well any other OLAP engine including...
3 Sep 2007
Microsoft Business Intelligence without SharePoint
This might be a strange topic given that the release of SharePoint 2010 is just around the corner, but I wanted to respond to a couple of questions and debates I have been involved with in my temporary role in the partner team. I would like to start by going back to the early days of Microsoft BI, analysis...
12 Apr 2010
PowerPivot is Analysis Services, sort of
PowerPivot is essentially a way of making an analysis services cube using excel as the design tool. When you use the PowerPivot for Excel add-in, then there no backend dependency, but if you want to save a PowerPivot to SharePoint 2010, there has to be a special installation of SQL Server 2008 R2 analysis...
21 Jan 2010
SQL Server 2008 BI, Perception & Reality
Following all the acquisition mania in the BI marketplace last year, it's interesting to see that the Microsoft approach of growing your own BI platform seems to be paying off according to this article by Gartner in Information Week. It has taken a while though - I well remember my first foray into olap...
8 Feb 2008
A worked example of using spatial and olap data in Reporting Services
In my last post I went through a possible approach for showing spatial and olap data that the RSPB are planning to evaluate, now I want to show you how this might look in an example. First of all an apology, I am not going to pay the £1500 to license UK postcodes as this is just a demo, rather I am going...
20 Sep 2010
Data Mining in SQL Server 2005, 2008
Data Mining like every other term in business intelligence seams to mean different things to different people. For me the key things that data mining does are: Assist in discovering relationships in data sets that were previously unknown. An example would be to get an insight on how age, and gender...
7 Jan 2008
OLAP, Cubes and Multidimensional Analysis -
Hugh McLeod is quite correct, On-Line Analytical Processing (OLAP) is actually about business, it just sounds like a science project. Basically OLAP is an awful name, Nigel Pendse , author of the OLAP report calls the same thing FASMI, which I think is a far better term : Fast - 90% of queries back in...
22 Aug 2007
ROLAP dead but not forgotten
With a catchy name like Relational On Line Analytical Processing, is it any wonder that this technology hasn’t really entered mainstream use. Firstly what is it? OLAP (On Line Analytical Processing) is a data store organised for ad hoc query and analysis, a good way to think about this is as a...
24 Aug 2010
Application Platform Knowledge Hub
Albert Einstein once remarked that you don’t need to remember everything, you just needed to remember where to look. When it comes to learning about Microsoft products this can sometimes be harder than it ought to be and what resources there are can be fragmented so you do have to remember where...
7 Feb 2012
Cast and Convert
In SQL server the Cast and Convert functions change one type of data to another. A pod cast is the art of converting knowledge into sounds so that's the tenuous link for this post. Pod casting is not something I have ever tried, but the security expert on our team, Steve Lamb sees himself as the next...
11 Sep 2007
Lean and Mean
When you build a cube in analysis services it is very easy to expose every attribute in every dimension to the user. You can also add-in every measure and if you don't feel that's enough then you can create your own calculated members to add to the users' fun. Once you have given the users every thing...
18 Sep 2007
Grain - The devil is in the detail
Technorati Tags: BI , Business Intelligence , OLAP , SQL server 2008 Business Intelligence is just like digital photography, the more detail you want the more space you are going to use and in both cases we talk about grain and granularity. If you have ever worked with RAW files on a 'Prosumer' (an IT...
23 Aug 2007
Big Dimensions – Just because you can …
One of the limitations of Analysis Services in SQL Server 2000 (AS 2000) was that a member in a dimension could only have 64,000 children, which was removed in SQL Server 2005 (SSAS 2005). So now you can create a customer hierarchy for your 1 million customers which has [all] at the top with each...
28 Apr 2008
© 2014 Microsoft Corporation.
Privacy & Cookies