Business Intelligence Archives

First impressions of Microsoft Data Explorer for Excel

Mar 2, 2013 19:37 ⋅ Leave a Comment ⋅ Ivan Donev

Today, while I was going over my RSS feeds, I noticed that Microsoft released their Data Explorer as official Preview version. You can read the announcement here. There you can also find and a very slick video showing off the functionality of Data Explorer.

Download the tool from here.

I decided to play with it by myself and it was real fun, I have to say. So in short I installed the tool (it is embedded in Excel) and once you open MS Excel (I am running Office 2013) there is a new ribbon:

The sources you can use are basically the same as what you can use with Excel and PowerPivot, with one enormous exception – there is a driver for connecting to HDFS (Hadoop File System) – well that is now something you can explore Smile

Anyway, at that moment I do not have a “big data” setup I can work with, so I will just show you how quickly I managed to gather information on country population and alcohol consumption using public web data (I did not search about alcohol consumption, but for population density, however this data set popped up, so I decided it will be fun). So once you open the Data Explorer ribbon and hit Online Search (the leftmost button on the ribbon), you got a simple window where you just type what are you searching for and you get dozens of suggested datasets which you can use:

When you hover the mouse over the provided dataset, you get a preview of the data on the left of your screen and a little button “USE” just below the dataset description. If you hit it, the data will be embedded in a new sheet in Excel and you can do lots of things with it. What I want to show you is how I used two datasets and combined them to a new set, joining them on the fly and ultimately producing dataset for my particular reasons (without needing to VLOOKUP, import/export and so on).

So once you have the two sets of data in two different sheets (remember, the connection to the source of data is life), you can go to the Data Explorer Window and press the Merge button – it will show you the following window:

You have a two-part window – on the top you select the source dataset and on the bottom – the one that you wish to join to the source one. You just mark the columns using the mouse (use Ctrl for multiple selection) on both datasets (top and bottom). Internally it is building an LEFT OUTER JOIN and if your data does not match completely (just as mine) you will not get values from the bottom dataset. You can also see the statistics of how many of the rows you can have matched, which will further help you cleanse the data further. So here is what I had as final report:

Of course this is solely is not the entire functionality of Data Explorer, but it will get you to start testing and playing with by yourself.

Enjoy Winking smile

Posted in: Business Intelligence ⋅ Tagged: Data Explorer

Enable additional logging for SQL Server management data warehouse collection sets

Nov 16, 2012 22:45 ⋅ Leave a Comment ⋅ Ivan Donev

Today I finally found some spare time to dig into some SQL Server management data warehouse issues we were experiencing. The collection jobs failed for no visible reason and with (believe me) no meaningful error in the logs. So I wanted to enable some additional logging and while searching, I found this (I lost the link, so I cannot reference it :\ … )

If you want to enable additional logging for any of the collection sets you are running, you can use the below statement.

update msdb.dbo.syscollector_collection_sets
set logging level = 2
where collection_set_id = <set id>

If you are wondering what set_id to use, the default ones are:

Server Activity – 1
Disk Activity – 2
Query Activity – 3
Utility information – 4

The rest can be discovered using the msdb.dbo.syscollector_collection_sets view.

The difference in logging is that actually if you enable advanced logging, you will be able to see the output of each single component of the SSIS package, used to run the upload jobs for example.

BEFORE:

AFTER:

Posted in: Administration, SQL Server 2008/R2, SQL Server 2012, Troubleshooting

Analysis Services 2000 – a ton of discoveries over a weekend

Jun 24, 2012 16:20 ⋅ Leave a Comment ⋅ Ivan Donev

This weekend I had quite a lot of activities related to a migration of Analysis Services 2000 from one machine to another. With the limited capabilities of AS 2000 and the approach we chose – backup/restore, I thought it will be a quite straight forward process. Well…it wasn’t. I had to discover so many different things that I even surprised myself. In this post I will try to summarize them, despite the fact that they might not ever get handy for you.

Number 1: Backup and restore for

The archival process of AS 2000 is quite easy and straight forward – you have the button within the context menu and the only thing you should configure is the path and filename of the backup file and the actual data location (the folder structure of the AS databasesm which is usually configured on instance level). Here we have several “gotchas”:

Remote backup – you can initiate a remote archival process (from a remote machine, having Analysis Manager installed). But within the data folder path you must be careful and enter the accessible network path of the AS 2000 instance, being source for the backup. Note that if you do not point it correctly you will not include the data in the backup.

Backup speed – it is just awful! First of all, all archives are compressed into a CAB files…and you cannot change that at all…you are just stuck with the archive. But this is not the problem…the problem is that the archival process is using only one logical CPU. Lame, uh? This whole stuff made the backup I was running to run for 5GBs/hour…what a speed!!!

Restore speed – as any other SQL, once you initiate restore, the process first must allocate the space before starts “unpacking” the data. And if your service account does not have the “Perform volume maintenance tasks” privilege enabled, you will have to wait quite a lot of time and for the restore. (Info on “Perform volume maintenance tasks” can be found here).

The backup does not start – well, yeah…I configure the backup, files, paths, the whole bunch of settings required, but at the end it is just not starting…I was checking the registry, installation versions, tried the command line, etc….it was just not starting….Then I went to check the settings of the AS instance. It seemed that the instance did not have default DATA and TEMP dir:

AS2000 lets you save such configuration. And does not really have an issue with it being empty. But if you run a backup, it is just not starting if you are running the archive locally on the server. For remote backup – it is just fine, because there you can point the UNC path to the DATA folder.

Error Log free AS 2000 – this part bugs me the most! It is just not very pleasant to have something not working/starting/failing, and not to have any detailed log to dig into and find the root cause. With AS2000 you just have to look around and test all kinds of possible solutions. I suppose you understand how risky is that. Good thing is that the engine does not really have that much options to play with :D

Well, for two days in a row we managed to migrate(move) en entire instance from one machine to another, but the 200GBs of data took us forever to backup and restore. And imagine, that some of the archives were corrupted – it was just a pain in the a** to have to start the archival process once again.

Oh, I forgot to mention one more thing – once the app team started testing, the processing of one of the databases was causing the entire AS instance to stop with the following error:

“The MSSQLServerOLAPService service terminated unexpectedly” and “There was a fatal error during transaction commit in the database <DB NAME>. Server will be restarted to preserve the data consistency”

I found some articles saying that a restore might help, tried it, failed once again…it seemed that the backup was corrupted. It was a 50+ GB database and if I have to start the archiving again it was another 10 hours of waiting and getting close to the deadline. So I took another approach – copied directly the files from the source server and overwritten the ones on the destination. The AS databases are holding all their structures and data within a single folder so it was a straight forward copy/paste job. And it was fine as the database was already restored and part of the AS metadata databases. Luckily it was also fine with the app team who managed to process the database.

Quite a lot of fun with archeological (:D) AS instances where I had to learn and observer so many things during a weekend. The good thing is that it almost made it up for the lack of sleep and frustration I had last two days :)

Posted in: Administration, Business Intelligence, SQL Server, SQL Server Analysis Services, Troubleshooting

How to set PreAllocate parameter in SQL Server Analysis Services and something more

Oct 29, 2011 00:29 ⋅ Leave a Comment ⋅ Ivan Donev

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:

<PreAllocate>0</PreAllocate>

If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

- SQL Server 2008 White Paper: Analysis Services Performance Guide

- SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.

2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

Posted in: Administration, SQL Server Analysis Services

SQL Server AS failed to start with message “SQL Server Analysis Services () service on Local Computer started and then stopped. Some services stop automatically if they are not used by other services or programs”

Mar 3, 2011 21:50 ⋅ 2 Comments ⋅ Ivan Donev

This is a very strange issue I came across last week. We have a consolidation server with several 2005 SP3 instances, including couple of SSASs. Everything was going well until one reboot of the server, when neither of the analysis services was able to start. The symptoms were the following: when I tried to start the SSAS service from Services snap-in there was a message “SQL Server Analysis Services (<instance>) service on Local Computer started and then stopped. Some services stop automatically if they are not used by other services or programs” and the service remains stopped. I checked the SSAS logs, OS logs, but unfortunately the service did not even reach the point where it starts writing the log.

I tried to clear the event log of the operating system (System and Application) as it was suggested here. It did not work.

I wondered what would be my next move before calling MS Support so as last option I tried to install a brand new AS instance to see if it’s something with the current ones wrong, or there is a global OS issue. The new installation was successful, the instance was working AND MY OLD INSTANCES ALSO STARTED! Imagine my surprise! I had no idea what happened during the install that fixed my issue! Anyway, as it was working, I cleaned up my test AS instance (uninstalled it) and tried to restart the SSAS just to be sure. Guess what – it did not start at all (AGAIN!). So it was something that the uninstall did which was causing the AS not to start. So I dug deeper – during the uninstallation of SSAS besides Support files and Backward Compatibility tools, I noticed that setup is messing with OCW11 and MSXML6. I figured it would be something connected to those two thingies. I downloaded MSXML6 from here, installed it and tried again to start the services…this time it was successful.

Of course I asked around and it turned out that several days before the issue some SSAS instances were uninstalled. And as the SSAS services were running fine and not restarted until the server reboot the issue never came across.

So bottom-line is – be careful with uninstallation of SQL Server and if possible always reboot the server after uninstallation is done just to check if anything else has not been impacted.

Posted in: SQL Server 2005, SQL Server Analysis Services, Troubleshooting

Minimum requirements for starting SQL Server 2008 analysis services

Oct 12, 2010 22:58 ⋅ Leave a Comment ⋅ Ivan Donev

In this article I will try to explain what are the minimum requirements to start SQL server analysis services instance in case of failure. Analysis Services is very sensitive on folder structure and file access but still basically depends on two components – registry and part of its original folder and file structure. I will start with the registry settings and their specifics (what you should pay attention to) and then proceed with the folder structure and what are the minimum required files and their permissions in order the service to start. So let’s begin.

1. Registry

As it comes to registry, SSAS instance information is kept in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<SSAS10.instance name>

Important sub-hives are:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERCPE

and

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERSetup

In the CPE you should pay attention to ErrorDumpDir registry key – this is the first important folder path, valuable for the instance to start

In the Setup part you should note SQLPath and SQLBinRoot paths as they are pointing towards binaries of the SQL Server installation and the context of which the instance is working. So far we have three registry keys important: ErrorDumpDir,SQLPath and SQLBinRoot. Those keys are one of the first read when the service is started. They also contain folders that should be accessible by the SSAS service account at any times (this means that the account should have at least read permissions on them in order to start)

2. Folder and file structure

Folder-wise SSAS needs the following:

Config folder – it is always <SQLPath>Config
Log folder – HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERCPEErrorDumpDir
Data folder – HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERSetupDataDir

Now SSAS does need the Config folder and the msmdsrv.ini file in it. The important thing here is that there is no need the file contain the actual settings – it can be copied from another (working) instance. It can be even a balnk file. If you leave the file blank, then when the service starts it will fill it with default settings.

Interesting behavior is noticed with the Log and Data folders – if the log folder is not accessible it is not crucial for service startup. Further errors probably will be raised but the service will be working. As for the data folder – if it not accessible or missing, then the instance will create a new one with defaults contents. Of course if the AS account has permissions to do so.

Having this said, when you start troubleshooting SSAS service instance startup failure (Error IDs 1069 and 1042 for example), you should follow these steps:

Extract all folders from the registry and check their availability – if something is missing try to restore it from backup
Check the Config folder if exists and it has necessary permissions set
Check the msmdsrv.ini file if exists and its contents. Delete the contents if necessary.
Check if something is using default SSAS TCP/IP port 2083 (you can use netstat /b in the command prompt)
These four steps should cover most of the cases of SSAS startup failures.

In general there is not much documentation from Microsoft on the matter and the errors when the service fails to start are quite general. It is difficult to troubleshoot because there is also no logging started before the actual service startup. But having the service started once, even with issues it would be easier to troubleshoot as you will have error logging and access to configuration values from SSMS.

Posted in: SQL Server Analysis Services

Creating dual Y-axis chart in SSRS 2008

Mar 18, 2010 19:55 ⋅ 4 Comments ⋅ Ivan Donev

In this post I will show you how to create a simple report with dual Y-axis column chart. It is a very common requirement in reports and to be honest – quite difficult to find out how. I’ve been struggling for a while to get to the desired result. First let me share what kind of data I prepared for my demo and what will be the end result.

1. The data – it as a simple dataset with three columns – one for month and two for measures.

select 'Jan' as Month,10200 as Measure1, 11528 as Measure2
union all
select 'Feb' as Month,6247 as Measure1, 3098 as Measure2
union all
select 'March' as Month,5982 as Measure1,6814 as Measure2
union all
select 'Apr' as Month,5200 as Measure1, 2528 as Measure2
union all
select 'May' as Month,5000 as Measure1, 1000 as Measure2
union all
select 'Jun' as Month,4000 as Measure1, 413 as Measure2

2. The end result:

So how to do it? Of course First you create a new report and enter the dataset.

1) Add a simple column, add Month filed to the Category box and Measure1 and Measure2 – to Data box

2) Right click on the second data series (in my case – the yellow columns). Right-click on the selected series and go to Series Properties –> Axis and Chart area.Change the value axis to Secondary and hit OK. The result should be in showing a second axis on the right of the chart just like below

3) After adding the secondary axis, again select the second series (yellow ones), right-click on it and go to Change Chart Type. Then select some of the line chart templates and hit OK button. I am using the first and the simplest one.

And that is how you can create a dual Y-Axis column chart which can help you in better visualization of certain data. Of course till getting the report done for end-user running, you can add some labels, include Markers and Data Labels for better reading. But it is up to you and your organization’s requirements how functional and “colorful” a report to be.

Posted in: Business Intelligence, SQL Server 2008/R2, SQL Server Reporting Services