Tag | Analysis Services Posts

We needed to swap out the DB box. Our DB box runs SQL Server, SSRS and SSAS. The AT is on its own box and SharePoint is on its own box.NOTE: I will update the blog post per resolution of the issue :) The Microsoft procedure I used was: Restore Data to a Different Server or Instance Setup new DB box with WS08 R2 with all updates applied. Installed SQL SQL 2008 R2 on new DB box (same version as on the old DB box). Took TFS offline. Disabled the transaction backup job from existing backup plan (runs ...

Our TFS instance had been humming along for some time when I noticed that the full and incremental cube refresh jobs started failing. Using Grant Holiday’s “Team Foundation Server 2010 Administrative Report Pack” I was able to better visualize and diagnose the problem. The Problem The full error message was - OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found ...

Once the installer for the AdventureWorks Community Sample Database has finished, we then need to complete the process (not mandatory, but nice to do to play with the OLAP content). The first thing we need to do is make sure we note the location of the script files and samples. The path if you are working with SQL Server 2008 R2 is as follows: “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\Ad... 2008R2 Analysis Services Project\enterprise” I am working with the developer edition, ...

For a while now, if we have been around SQL Server we got used to the different sample databases that were provided. From Pubs to Northwind to AdventureWorks. In order to get the AdventureWorks samples we need to go to CodePlex and download the one that corresponds to the version of SQL Server we are working with. The link to the download site here : http://msftdbprodsamples.co... Now, on to the installation. Once you download the appropriate file, double click and launch ...

Last week, at the PASS (Professional Association for SQL Server) Summit in Seattle, Microsoft held a coming out party, not only for SQL Server 2012 (formerly “Denali”), but also for the company’s “Big Data” initiative. Microsoft’s banner headline announcement: it is developing of a version of Apache Hadoop that will run on Windows Server and Windows Azure. Hadoop is the open source implementation of Google’s proprietary MapReduce parallel computation engine and environment, and it's used (quite widely ...

I cut my DBA teeth on IBM's DB2 UDB EEE (enough acronyms) back in the 90's. I have always appreciated the scalability that is available with a MPP architecture. So a couple of years ago I was excited to hear that SQL Server was going to have a MPP architecture available. Now I am at SQLPass Summit 2011 and seeing the PDW (Parallel Data Warehouse) and the hardware offerings by Dell and HP to scale that up and out. However, I was disappointed to learn that this technology does not extend to Analysis ...

I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/dela... ...

Full Disclosure: I was one of the technical reviewers on this book. I think my friend Tomislav did a great job on this book and it would make a valuable addition to the bookshelf of anyone that is working with MDX. I really enjoyed reading this and there were even a couple of interesting techniques that I have added to my toolkit. As far as I know there are not any other MDX books on the market quite like this one. It's more aimed at the intermediate level of MDX user and assumes that you have some ...

A number of people have spotted this announcement on the SQLCAT blog already: http://sqlcat.com/sqlcat/b/... but if you have not seen it yet, I’m very proud to say that I’ve been made an Analysis Services Maestro! If you have not heard about the Maestro program you can find out more details about it here; as Chris has said, it’s basically something like an MCM for Analysis Services. The course itself was a fair bit of ...

In my current job as a Senior ALM Consultant, part of what I do is help organizations get TFS set up and configured. While a single server installation is pretty easy (and using the basic configuration to install TFS on Windows 7 is even easier), there are a few pitfalls and points of frustration when setting TFS up in a two-server configuration. For the purposes of this discussion, I am assuming the following setup: - A database server (Data Tier) with SQL 2008 R2 to house the TFS Databases, Reporting ...

It has been quite a while since I signed up for this blog site and high time that something was posted. I have a list of topics that I will be working through and posting. Some I am sure will have been posted by others, but I will be sticking to the technical problems and challenges that I’ve recently faced, and the solutions that worked for me. My motto when learning something new has always been “My kingdom for an example!”, and I plan on delivering useful examples here so others can learn from ...

I just noticed that Jeffrey Wang from the Analysis Services team has started blogging. He has put up a great first post on “Execution Plans and Plan Hints for MDX IIF Function and CASE Statement”. Check it out here http://mdxdax.blogspot.com. If you want to subscribe you can get an RSS feed at http://mdxdax.blogspot.com/rss.xml (I don't know why Blogspot does not make the rss easier to find)

Problem Like most organizations, we are planning to upgrade our database server from SQL Server 2005 to SQL Server 2008. I would like to know is there an easy way to know in advance what kind of issues one may encounter when upgrading to a newer version of SQL Server? One way of doing this is to use the Microsoft SQL Server 2008 Upgrade Advisor to plan for upgrades from SQL Server 2000 or SQL Server 2005. In this tip we will take a look at how one can use the SQL Server 2008 Upgrade Advisor to identify ...

/* EDIT - This problem has been fixed in the latest SQL Server 2008 R2 Cumulative Update package. It was identified that changing the aggregation design without reprocessing the aggregations prior to a ProcessUpdate on the dimensions causes this problem and it is a bug */ Many implementations require the use of ProcessUpdate to support Type 1 slowly changing dimensions. ProcessUpdate drops all of the affected indexes and aggregations in partitions affected by data that changes in the Dimension on ...

SharePoint Installation in Windows 7 or Vista is not same as Windows Server 2008. You might have difficulties if you do not follow the proper steps. Here i have been tried to explain how to install SharePoint 2010 on Windows 7 or Vista and configure it. Assume, Installed OS Windows 7 x64 (as you may already know SP only support 64-bit OS) and Windows update Install SQL Server 2008 Install SharePoint 2010 You can install SharePoint using the following steps A. Install software prerequisites 1. Install ...

Readers of my blog and/or column know that I am a big fan of PowerPivot. And because of that, I was excited to see, both at June’s Microsoft BI Conference (part of Tech Ed) and this week’s PASS Summit, that the PowerPivot technology will become more entrenched. What Microsoft showed in June was that the full-fledged SQL Server Analysis Services (SSAS) product would support the in-memory BI (IMBI) engine, known within Microsoft as VertiPaq. This cool columnar, in-memory technology will not be limited ...

Turns out you can't just move a TFS warehouse/cube from SQL Server 2005 to 2008... Once performed we got the following error on the TFS app server tier - when we tried to update/process the cube (both manually and via the TFS scheduler service): Detailed Message: Failed to load adapter Microsoft.TeamFoundation.Wa... Exception Info: \n Microsoft.TeamFoundation.Se... Error encountered when creating connection to Analysis Services. Contact ...

Analysis Services has a useful feature called Usage-Based Optimization. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio (BIDS). Information on how to configure (or disable) the query logging required for this is fairly hard to find. The Analysis Services Properties identified by "Log \ QueryLog" in the Analysis Services Properties window are used to configure the query logging used by Usage-Based Optimization: The definition ...

One of the key performance concepts in Analysis Services is the design and use of good Aggregations to support user queries. However, Analysis Services (Both 2005 and 2008) by default is not configured very well to take advantage of multi-processor environments for parallel processing of these indexes, particularly for large MOLAP partitions. Keep in mind, using a large portion of available processors for aggregation building is only desirable in environments where a processing window is allocated ...

For those who haven’t come across it, PowerPivot is Microsoft’s Excel- and SharePoint-based self-service BI tool. Essentially, it allows power users to build their own SQL Server Analysis Services cubes, except that they don’t need to be familiar with cube concepts and they won’t even notice that SSAS is involved. The other exception from the Microsoft BI norm is that these cubes use a new columnar, in-memory storage engine, called VertiPaq, rather than Analysis Services’ traditional MOLAP (Multidimensional ...

If you’re left scratching your head over SAP’s intention to acquire Sybase for almost $6 billion, you’re not alone. Despite Sybase’s 1990s reign as the supreme database standard in certain sectors (including Wall Street), the company’s flagship product has certainly fallen from grace. Why would SAP pay a greater than 50% premium over Sybase’s closing price on the day of the announcement just to acquire a relational database which is firmly stuck in maintenance mode? Well there’s more to Sybase than ...

At my previous employer, when developing for BizTalk Server 2004 using Visual Studio 2003, we made use of separate development and deployment environments; developing in Visual Studio on our client PCs and then deploying to a seperate shared BizTalk 2004 Server from there. This server was part of a multi-server Standard BizTalk environment comprising of separate BizTalk Server 2004 and SQL Server 2000 servers. This environment was implemented a number of years ago by an outside consulting company, ...

During the last year, we (Tellago) have been involved in various business intelligence initiatives that leverage some emerging BI techniques such as self-service BI or complex event processing (CEP). Specifically, in the last few months, we have partnered with Microsoft to deliver a series of events across the country where we present the different technologies of the SQL Server 2008 R2 BI stack such as PowerPivot, StreamInsight, Ad-Hoc Reporting and Master Data Services. As part of those events, ...

Office 2010 has released to manufacturing. The bits have left the (product team’s) building. Will you upgrade? This version of Office is officially numbered 14, a designation that correlates with the various releases, through the years, of Microsoft Word. There were six major versions of Word for DOS, during whose release cycles came three 16-bit Windows versions. Then, starting with Word 95 and counting through Word 2007, there have been six more versions – all for the 32-bit Windows platform. Skip ...

Dear Friends, Here is my first post on geekswithblogs. I am happy that I have got a separate space here to blog. I am an MCTS certified Professional in .Net 2.0 Web applications, working as a Senior Software Engineer. Willing to share my knowledge on all topics whatever I know. I am also an active presenter / speaker in Microsoft Developer User Group HyderabadTechies. And I have presented many online sessions there. I keep myself updated on the latest technologies in Microsoft. You can see my posts ...

When deploying the Microsoft.BizTalk.ESB.BAM.E... BAM activity (as part of the Core ESB Toolkit install) we ran into this error: --- OLE DB error: OLE DB or ODBC error: DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied : 08001 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bamExcByApplication’ --- The issue was that we used a SQL Server Alias while configuring ...

BAM Operations, a brief summary Installing the BAM infrastructure BAM infrastructure composed on the base of the SQL Analysis Services (SASS) and the SQL Integration Services (SISS). The SQL Notification Service is an optional element. 1. Before installing BAM make sure the SQL Analysis Services and the SQL Integration Services are installed. If not, install them. 2. Create the BAMAnalysis and the BAMStarSchema databases. It can be done in separate step or while the BizTalk configuration. 2.1. Start ...

Over the past three plus years that I have been working with SharePoint, I have never had the pleasure of giving an Excel Services presentation to a client. Well, thanks to our awesome sales team this past week I was able to do that very thing. I have been a part of some Excel Services implementations, but they were really focused on trying to create a “poor man’s” business intelligence solution with use Excel Services and Excel on top of an Analysis Services OLAP. Of course, this was prior to the ...

Hyper-V is getting more exposure now that SharePoint 2010 is nearing it's launch as only x64 platforms will be supported. Previously in MOSS/WSS 3.0 developers can use Virtual PC or XP Mode to virtualize their SharePoint server. Hyper-V provides greater network functionality than Virtual PC, allowing you to create Virtual Internal and External network adapters via the Virtual Network Manager. It's also possible to add Legacy Network Adapter. Often you need internet access as well as access to your ...

Just saw a presentation on SharePoint 2010 and SQL Server 2008 R2 BI Integration. There are some cool stuff that will be out soon. The presentation focused on PowerPivot and Performance Point server. A end-business user can access her/his datasource, pull in some external data (like weather condition), and create a spreadsheet using PowerPivot. This spreadsheet, in addition to being hosted on SharePoint, can be incorporated through Performance Point by a SharePoint developer, and build a Cube in ...

In the new film “Up in the Air,” George Clooney’s character, Ryan Bingham, poses a question relevant to the premise of BI as a cloud computing offering. Bingham is a career transition specialist -- i.e. someone who fires people as an outsourced service – and he insists that his services must be delivered on-premise (if you will), despite his firm’s new initiative to start doing so via Web conference technology. That initiative is being pushed by his colleague, Natalie Keener (played by Anna Kendrick), ...

I had an issue recently with my SSAS 2008 performance counters - they were all showing up as 0. I could see them listed in Performance Monitor, but none of them worked. The counters for my SSAS 2005 instance worked fine, but the 2008 ones did not. I had observed this behaviour on my old Vista x86 machine and now it was happening on a fresh install of Windows 7 x64. I tried numerous steps to troubleshoot this issue including going through this detailed blog post from the CSS SQL Server Engineers blog, ...

PowerPivot is the newly announced name for Microsoft’s (not yet released) self-service analytics product, formerly code-named “Gemini.” PowerPivot brings the power of OLAP analytics to end-users, by allowing them to create their own data models, drawing from conventional data warehouses as well as flat files, spreadsheets, and even data feeds and reports. It then allows for sophisticated drill-down analysis in Excel 2010 itself which will feature a new “Slicer” element in its user interface, essentially ...

I just finished my talk at the 2009 SQL Downunder Code Camp on Analysis Services data. As promised, attached below are the scripts and Powerpoint slides that I used in the presentation. There is not that much information in the slides, they were really just introduction and conclusion - all of the content is in the scripts. And the scripts probably only make sense if you at the session. In addition to the scripts, below are the links to all of the free tools that I was using: •MDX Studio http://ssas-info.com/forum/... ...

Full Disclosure: I was lucky enough to be sent a free review copy by the publisher But... if I had not been sent a review copy I would have gone out and bought a copy of this book anyway. Why is that? Because I had purchased the previous edition and I knew that it would be the most in depth book on SSAS 2008 available. It has been written by members of the product team and contains a wealth of information that just could not come from any other source. I'm sure most of you have heard that Attribute ...

What is Kerberos Authentication? Kerberos (or Cerberus) was a three-headed dog in Greek Mythology which guarded the gates of Haides (King of underworld God of Death). Kerberos was responsible to prevent ghosts of the dead from leaving the underworld. The Kerberos Protocol was created by MIT as a solution to network security problems like: 1) Insecure unencrypted password over the internet 2) Firewalls, which assumes that the bad guys are outside the network, what about the Bad Guys within the network. ...

A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular ...

I had a question a little while ago via my blog about possibly using a rowset action to execute a SQL query against a specified table. Although a rowset action will allow you to enter a SQL query, such a query is still executed against the current cube and only the subset of SQL supported by SSAS can be used. Basically the rowset action just returns a flattened result set. However what would be possible would be to write a .Net stored procedure and use that to execute your SQL query. The code itself ...

R2 ! As you may know I’m abit of a SQL Server nut so I was particularly interested in the announcement at the recent TechEd of SQL Server 2008 R2. R2 is the combination of the Kilimanjaro, Madison and Gemini projects. Kilimanjaro is adding support for up to 256 processors to the existing code base. Whilst Madison follows on from the purchase of DataAllegro last year moving data to handling petabytes of data. Lastly Gemini is about taking on Qlikview and providing Analysis Services using column-oriented ...

This question came a while ago now in this SSAS forum thread: What is wrong in my query and I thought it was something that may interest other people. Basically it boiled down to trying to find a T-SQL equivalent to the following MDX which is querying a dimension with a many-to-many relationship to the measure. So given the following simple MDX query, what would be the equivalent in SQL? select measures.[Internet Sales Amount] on 0 , [Sales Reason].[Sales Reasons].[Reason Type].Members on 1 FROM ...

*Moved to: Get Analysis Services last processed dateI need a little bit of code to get the last processed date for the cube that my site connects to: Public Function GetCubeLastProcessedDates(B... AnalysisServer As String) As Collection(Of CubeInfo) Dim result As Collection(Of CubeInfo) Dim identity As WindowsIdentity = WindowsIdentity.GetCurrent() Dim eCode As Integer = CommonUtility.RevertToSelf() Dim oServer As New Server Try result = New Collection(Of CubeInfo) oServer.Connect(String.Form... ...

Registration link: http://www.nhmn.com/Courses... Date: April 23 Time: 9-11:30 Course Overview This session will explore the various Business Intelligence options available from Microsoft and how they integrate with Microsoft Office SharePoint Server 2007. This session will include a number of demonstrations and tips on how to get started using SharePoint for business intelligence. This seminar is available live on the web using VoIP (PC audio). Telephone audio is not ...

After a long long struggle finally me and my team was able to run the Analysis Services reports on SQL Server 2008. We had a big trouble while installing the Analysis Extensions of MS Dynamics 2009 on SQL Server 2008. Moreover, 64-bit added to more troubles. While installing Analysis Extensions on a machine running SQL Server 2008, as soon as you check the checkbox for Analysis Extensions you would encounter an error SQL Server 2005 Analysis SP2 or higher required to continue the install. There are ...

Next week I am giving my talk talk on "How to build your own Super Model" to the Melbourne SQL User Group. This is the same one that I presented to the Adelaide User Group last month. It is an introductory look at dimensional modeling for Analysis Services. Where we will talk about what it is, how it’s done and look at the features that Analysis Services provides to support some of the different modeling techniques. The focus of this session will be around the various types of dimension usage, looking ...

You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ...

How do you stay up on new technologies? How do you approach learning a specific new technology? The first thing you have to accept is that the Microsoft technology stack has gotten so vast that it is simply impossible for a single person to be an expert in everything. This is the most important aspect to remember. This post is simply to describe my personal approach for staying up on new technologies in the hopes that other people will find something valuable in this to add to their toolbox. In terms ...