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
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...
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...
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...
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...
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...
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...
All the collateral from my presentation at SQL Saturday sits here:
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...
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...
(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...
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 ...
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...
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 ...
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...
Sometimes, when installing PowerPivot Services, you may get the message "sharepoint availability check for existing farm" FAIL Or "user account requirement for farm administrator" Even though your user is in fact a Farm Admin, and a local admin. As it turns out, the user needs sysadmin to the SQL machine as well, Shutdown the Sharepoint services, and follow the technique below to make the user a SQL admin if it is not already http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-wh...
Question that comes up fairly often is about connecting to Oracle. Here is a wiki entry we wrote about doing it in a 32 bit/64bit environment Here it is verbatim: Best Article found so far http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11 Possible quick solution Post done by SRIRAM RAJAMANURI Pasted from < http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f > Or according to this version 11 sorts it al...
I posted the following connect item , please vote for it! https://connect.microsoft.com/SQLServer/feedback/details/585399/dataset-report-part-calculated-fields When creating a dataset, a new calculated field can be created using an expression, especially useful for such techniques as dynamic measure selection, or dynamic graining of charts (i.e. letting the user select the X-Axis /Y-Axis from a drop-down) using a switch. These techniques are only necessary when using MDX queries These calculated...
So when exporting scorecards to PowerPoint or Excel, I'd get the error below: An unexpected error occurred. Error 46645. Exception details: System.ServiceModel.FaultException: The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the clien...
In Performance Point, we can create a report that points to an SSRS report, and embed this in our dashboard. The advantage of doing this is the richness of the interaction with other PPS parts, so for instance, being able to click on a scorecard, and have the measure in the report update dynamically. Very useful, and I'll post an article on how to do this soon. One gotcha is when testing security in Sharepoint. Sharepoint allows you to change your userID to test as another user. Unfortunately, a...
Last night I presented an introduction to StreamInsight to the Jhb SQL user group, to a mixed reaction. A lot of the people there still don't see how they'd use it in their jobs, and I don't blame them. Some interest from resident high volume developer @CraigN, hopefully leading to more discussions. Also, confimation from Gary Hope, MS South Africa's Data Platfrom Technical Specialiast that StreamInsight is a platform for partners to build apps : and a nice in-depth talk about how the architectu...
StreamInsight comes in 2 versions, Standard (on web, Standard and Enterprise editions) , and Premium (on DataCenter edition) Microsoft's documentation says Std is good for up to 5000 events per second, and a latency of more than 5 seconds, whereas Premium will run more than 5000 events per second and a latency of less than 5 seconds. Depending on hardware of course. So what's the difference? As it turns out (and undocumented....), the core query engine in Std is essentially single-threaded, and ...
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 clearcut. First, a list of the technologies Technology Use BizTalk SOA Integration .NET/WCF Custom SOA Integration. Not an integr...
As a consultant doing a BI project, for a client that remains nameless, I am doing a dashboarding solution. The majority of the sales data for this client was already in the data warehouse, however we also needed to integrate a couple of extra data sources, one of which is the GL (General Ledger) - the general ledger is based on a JD Edwards system, and taken into Essbase. Our first query was about whether data is changed in Essbase, being that if so, we had to get the data out of there. Thereby...