Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Browse by Tags

Related Posts
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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]...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: KPI confusion

    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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...