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...
Read More
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...
BI Projects 1 Introduction A BI project is a BI project right? Well, in my experience, no, and trying to blend aspects of different types of BI into one project are doomed to failure, as they don’t receive the focus they deserve. There are other types of projects, such as Master Data Management, or Enterprise Information Integration, that I’m going to skip for the purposes of this post, that ALSO need their own focus, so keep that in mind whenever you’re scoping a project. Also, these types of p...
I encountered a bizarre bug in the Sharepoint 2010 and SSRS Report Viewer combo today. When adding the Report Viewer to webpage, and leaving the height and width to auto, the report renders 100% fine in IE7, but the content disappears completely in IE8. When setting the height and width in the webpart properties, the report content reappears. Subtle and annoying. Especially as reports width and height may very well change, and are not static. The versions I'm using is Sharepoint 2010 r elease, a...
When running an SSAS Profiler while Performance Point Services is running, you will notice that while the data queries are MDX, the queries to fill the filters is XMLA And in addition, that copying and pasting those queries into SSMS won't work. What you need to do is use this query as a template: Discover xmlns = " urn:schemas-microsoft-com:xml-analysis " > < RequestType > MDSCHEMA_MEMBERS </ RequestType > < Restrictions > <!--Paste the RestrictionsList XML here --> &...
2005 Business Scorecard Manager Released Nice-ish tool, pure scorecarding 2006 Microsoft buys Proclarity, one of the premier BI analysis tools for SSAS 2007 Performance Point is released . The product is a horrible mishmash of 3 products. Proclarity , unchanged, with it's own installer. PerformancePoint Planning . Very much a version one, this product is MS's first and last attempt to actually attack true (financial) performance management PerformancePoint Monitoring : BSM, plus new analytic cha...
Just a quick post today: When setting up an action that points to an SSRS report in SSAS, make sure that your parameters are named exactly the same as the parameters in RS, WITHOUT the caption. Also, when using the member values, check whether to use [Dimension].[Hierarchy].CURRENT_MEMBER.MemberValue rather than [Dimension].[Hierarchy].CURRENT_MEMBER : it will depend on how your report is set up, but the former will typically work for a report against the database, and the second against a cube....
In the BI environment , an incremental load of a Fact table (also called a delta) is a process for which Microsoft has not provided us with a prebuilt tool. I'm going to be writing an article comparing several methods for doing these incremental loads, what I've listed below are 4 example techniques, as well as a comparison to a 3rd party tool from Pragmatic works. OUT of scope are the Change Data Capture techniques that only draw changed data from the source system. The assumption here is that ...
Hi I've just posted a connect item, please go vote https://connect.microsoft.com/SQLServer/feedback/details/553088/dataflow-unused-columns-should-be-highlighed-in-bids
Chart analysis. So, BI in the terms of something that is known to us all: browser wars ! OK, so the stats themselves are fairly interesting (Chrome eats Opera at launch), lets have a look at the chart itself: Firstly, choice of chart: We’re tracking various metrics over time : in this scenario, line graphs are great, as they show the progression over time. In addition, markers have been placed at the data points, allowing one to easily check an individual data point. X-Axis : The X-Axis has been...
http://www.bidn.com//Assets/Uploaded-CMS-Files/2180394a-380c-419f-a0a6-2570eb8c76beAlteringForeignKeys_1_PrepTables.zip http://www.bidn.com//Assets/Uploaded-CMS-Files/631a6a0e-8cf1-494e-b609-094c7c08e9deAlteringForeignKeys_2_CreateAlter.zip This is going to be a very rough post, because it's friday night, and I'm almost done for the day :-) A little bit of background: I have a staging environment used for a project built on MS Sync Framework, and the tables currently have Foreign Keys in exactly...
Hmmmm. Just a quick anecdote about SQL's backup compression. I backed up a DB, tiny one, no compression, resulted in a size of 5636 KB. I was mailing it out, so I quickly 7Zipped it, and got a size of 434KB. Pretty impressive ratio actually, but I've always liked 7Zips native format. Later on, I made a foreign key reference change, and one stored proc change. MINOR changes. Backed up the DB again, but with backup compression. Results in a size of 909KB. Nice improvement on 5636, but nowhere near...
Well, this isn't going to be a long blog post. One of the tasks facing many of us is how to clear tables with foreign keys : we want to truncate those without any foreign keys referencing them, and delete the rest in the correct. Here's a script I've used as a basis to do this just that. Not 100% sure it's perfect yet, if I find issues I'll post them here. You pass the SP a schema, and it gives you a SQL statement to run. Some of the @temp table stuff could have been done with CTEs, but I saved ...
A preface: this wont' always replace partitioning. But in those in between cases, where performance needs a small boost, filtered indexes may well be enough of an advantage to NOT need to partition So here's an example CREATE NONCLUSTERED INDEX PartitionIndex On dbo.TransactionTable (PartitionDate, TrxDate) Where(PartitionDate is null ) update dbo.TransactionTable SET PartitionDate = GETDATE() WHERE TrxDate < DateAdd(M, -1, Getdate()) This will automatically update the indexes, you do a rebui...
For a quick divergence today, I've decided to, as part of the non-technical aspects of this blog, delve a little bit into who th different vendors and partners are in South Africa First off, a disclaimer: I work for a company called Intervate at the moment, and they are a player in the BI space. As I'm from the BI side, I'm going to comment only on their sharepoint capabilities (i.e. things I don't do personally) There are a few vendors in the space, Microsoft, Microstrategy, IBM/Cognos, SAP/Bus...
So here's the scenario: Your cube is up, it's in production, it's been running succesfully. And today it fails. You investigate, manually reprocess the cube, and get the enormously helpful screen below: Well, from here, we need to identify why we now have a duplicate attribute key : do yourself a favor, and just look at the warning. The errors above can be vaguely misleading……. Next step then is to see what Mine Name is related to : So looking at this, we can immediately see that Mine Code is re...
Today I needed to script out portions of a database - to be more precise, only the objects in a single schema. For tables and stored procedures, this is easy : simply press F7, Object Explorer Details, and select the objects you need. For indexes, this is not quite as simple. I started off by searching the net, and found a basic script to do this : http://www.sqlservercentral.com/scripts/Miscellaneous/31893/ This script does create all the indexes, but it does not filter by schema, and it also d...
MY first blog post on BIDN :-) Well, let me start off with a brief introduction to the problem : My client, a large coal mining concern, has a warehouse concerned engineering availabilities - i.e. what the machines are doing at any given point. The fact table, FactAvailabilityHours is related to various dimensions (time et al), and has a measure called Duration - in hours oddly enough. One of those dimensions is a structure representing what a machine was doing - this Dimension is called DimAvai...