Since Visual Studio 2008 came out yesterday, I installed it. One gotcha – if you already have SQL 2005 installed, check custom on the install steps, because VS2008 will install SQL Express 2005 and start up an instance of that, I had to then uninstall SQL 2005 Express. PITA 🙂
Category: SQLServerPedia Syndication
Ok, add this one to the annoying bin. If you are writing SSRS (SQL Server Reporting Services) reports of a SSAS (SQL Server Analysis Services) cube, and there is a time dim, you need to format the date parameters in the SSRS report to match the SSAS date format, as a string.
I started out doing this a while ago, like so:
=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd(“D”,-1,DateTime.Now())),”0″&Month(DateAdd(“D”,-1,DateTime.Now()))) &”-“& IIF(Len(Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) < 2,”0″ & Day(DateAdd(“D”,-1,DateTime.Now())).ToString(),Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) &”T00:00:00]”
Wow.. Ugly, hard to read, hard to debug. Just ran into issues, etc.
I decided to finally just write a function to do the same thing and make sure it is correct once and for all..
Function GetSSASDate(ByVal DateToRun As DateTime) As String
Dim result As String = “[Time].[Calendar Date].&[”
result = result & Year(DateToRun).ToString() & “-”
If Month(DateToRun).ToString().Length < 2 Then
result = result & “0”
End If
result = result & Month(DateToRun).ToString() & “-”
If Day(DateToRun).ToString().Length < 2 Then
result = result & “0”
End If
result = result & Day(DateToRun).ToString() & “T00:00:00]”
Return result
End Function
then, I go to my report properties, Custom Code and add that function, then in my parameter I call it like so:
=Code.GetSSASDate(DateAdd(DateInterval.Day,-1,DateTime.Now()))
Much cleaner, easier and just all around good. If I wanted to take this a step further, I would actually just make an assembly with some useful functions and deploy to my SSRS server, but I will save that for a later time.
Technorati tags: SSRS, SSAS, SQL Server Reporting Services, SQL Server Analysis Services, SSAS Dates, Dates, VB.NET, Code
I have ran across this a couple times now, I actually had a blog post draft written up for this but never got around to finishing it, well this week I ran into the issue again, so here it is.
Importing data from Excel (using whatever – C#, VB.NET, SSIS, Access, etc) – you have data scattered throughout, and when you go to import it, some of the values are not getting read, they are just missing – empty, or not correct. Something goofy is happening and you cannot figure it out.
Excel tries to figure out the data type of a column, or if there is any data at all in a column by looking at the Top 8 Rows. It will ignore the header row. But it looks at these 8 rows and tries to be smart about it, and this can really screw you up if you are importing data.
Here is my example.
Year | Value1 | Value2
—————————–
1998 | | 20
1999 | |
2000 | | 15
2001 | | 20
2002 | |
2003 | | 15
2005 | | 20
2006 | |
2007 | 55 | 15
Now this might not work if you actually try it, but just bear with me for the examples sake. If you write a function in .NET to import this XLS sheet, the 55 in Value1 column will never get read. Why? Because the first 8 rows for Value1 are empty, and it thinks it is an empty column, just wacked. If you put a 0 in the 1998 row for Value1, then the 55 will get read. Since Value2 column has values in the first rows, it works just fine.
How can you get around this? Well, if you somehow read your XLS file without using the JET engine, then you are probably OK, also, you can tweak a registry setting to make excel look at more rows, say, 1000, which will usually get you by.
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet<version of jet>EnginesExcel
Where <version of jet> is usually 4.0
Change the TypeGuessRows value to something larger than 8 and then you should be good to go.
Just another Microsoft head scratchier…had me stumped for about an hour! 🙂
The other day, working on something crazy, I came across a use for the new UNPIVOT keyword in SQL Server 2005. Now, I figured I would use PIVOT before UNPIVOT, but so it goes.
Basically I had a temp table I populated that had some columns, then columns numbered 1-10 with different values. But I needed them to be row based, not column based. I could write some crazy union’s or something, but I figured, it was set up like a pivot table, so why not try UNPIVOT.
SELECT
MyId,
MyName AS ‘Name’,
tblPivot.Property AS ‘MyProperty’,
tblPivot.Value AS ‘MyValue’
INTO #tmp_values
FROM
(SELECT MyId, MyName,
CONVERT(sql_variant,[1]) AS [1],
CONVERT(sql_variant,[2]) AS [2],
CONVERT(sql_variant,[3]) AS [3],
CONVERT(sql_variant,[4]) AS [4],
CONVERT(sql_variant,[5]) AS [5],
CONVERT(sql_variant,[6]) AS [6],
CONVERT(sql_variant,[7]) AS [7],
CONVERT(sql_variant,[8]) AS [8],
CONVERT(sql_variant,[9]) AS [9],
CONVERT(sql_variant,[10]) AS [10]
FROM dbo.MyTable
UNPIVOT (
Value For Property In (
[1],[2],[3],[4],[5],
[6],[7],[8],[9],[10])
) as tblPivot
you can see, you have to make sure all of the fields are the same data type, basically you can get them from being columns to being back to rows with columns Property (would be like 1-10) and then the Value would be the value in the field. Pretty nice and slick!
So, you have a SSAS 2005 (SQL Server Analysis Services) Cube, and its not performing how you want it to. What do you do?
The first thing to look at is your attribute relationships (http://technet.microsoft.com/en-us/library/ms174557.aspx). Make sure you don’t have redundant relationships (AS should warn you as well) By default when you create a cube, and go through the wizard, only the default attribute relationships are set up. What this means is that all attributes are related to the key attribute by default. If you have related attributes, you want to set up those relationships!! This will help in a couple of ways. First, when creating hierarchies, it won’t warn you that you don’t have relationships set up. Second, your aggregations will actually have a better design, Analysis Services will be able to take into account the relationships and your aggregations should hopefully rock!
What’s next? Look at your reports and queries off the cube. Can you redesign them? Are you running some crazy query? Here is a good tip for using sp_create_plan_guide to influence query plans.
My suggestion is to run SQL Profiler and see how your cube is being accessed. Are the aggregations being used? Are things being read from cache? Use the BIDS Helper to take a look at your aggregations and see the row counts and make sure everything is working as you have planned.
Ok, what else? Cache Warming.(http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry) After running Profiler, or even setting up something automated to capture the queries being ran into another table, you can use this info to “warm your cache”. Basically when you process your cube, run the most frequently ran queries automatically so then the next time users run them, it will pull from cache.
Balancing your cache, aggregations and storage design modes is something you seriously want to dig into. Should you be purely MOLAP? Or MOLAP with Proactive Caching? Or do you want to go purely ROLAP or all the different designs in between the two? You want to minimize data latency.
Some of these things often get overlooked when first designing a cube, which then could make it tough to do once you have things deployed to end users. Also, SSAS 2005 isn’t the best at letting you tweak some of this stuff, I watched a presentation on SSAS 2008 by Donald Farmer and things look A LOT better in SSAS 2008, which I cannot wait for!
But that is a post for another day 🙂
Technorati tags: SSAS, Sql Server Analysis Services, SSAS 2005, SSAS 2008, Attribute Relationships, Aggregations, sp_create_plan_guid, SQL Profiler, Cache Warming, MOLAP, ROLAP, Query Plans, Performance
A reader sent me an email this weekend:
I wonder if I could as your advice as a BI / Data warehousing specialist.I have been studying Analysis Services recently having read a couple of books Which step me through the process of building the cubes etc but as I don’t come From a DB background one thing is not clear to me is how does one determine that They need to use BI / Analysis Services etc in the real world? As you, I am a .NET developer with a background of building thick client apps and am Familiar with creating layered architectures etc building on frameworks like NHibernate Etc to abstract out the DB stuff into my more familiar object world. My question how does one Generally interface with this Data Warehousing / Analysis Services stuff in the real world? I am looking for answers from people who have used these technologies in anger and not Like me from canned text book examples scenarios etc. Thanks for your time it is would be appreciated.
And I wrote an response, but after reading the response I figured I could post it up here as it is pretty general.
Basically what I have seen is this. You make your application (either web or windows) that saves transactional data, or logs, or something like that. End users usually want reports off of that data. At first, developers will report directly off that data (OLTP database). It becomes slow, and unusable after tons of data is there, so then developers tweak timeouts, things like that. Then the next step is custom summarizations into other tables, and then archiving off transactional data. Usually, because developers don’t know about data warehousing/BI stuff, all this stuff is custom up to this point.
Eventually, someone realizes that, “hey, there is a way to do this better”, which involves a structured ETL either using stored procs or SSIS or something similar. Also, instead of just ad-hoc or custom summarization tables, a well designed data warehouse (OLAP) database is key.
Now, from there, you can write your reports off your OLAP database, which is OK because the data is summarized, scrubbed, etc. But you really give it an adrenaline boost when you create a cube off that data warehouse OLAP db, it takes care of the summarization, the relationships, all that. You put the reporting in the hands of your end users (excel 2007 for example) – let them pivot and slice and dice the data, its all ready and set for them to do it, with really nothing you have to do on your end except make sure the cube is processed regularly off the DW.You are basically abstracting your OLTP data up two levels… In all reality you should be able to query your OLTP data for a time frame and get say revenue, and then query the OLAP and the CUBE and get the same results. Now, with your cube, you can still access data from it in your .NET apps using AMO, which is cool as well, or you can write Reporting Services reports directly off the cube as well, makes it a lot easier than writing custom .NET reports.
So, interfacing with your Data Warehouse, the best options to get data in is usually SSIS packages or stored procedures, no .NET coding really. To get data out, you would probably want to use Reporting Services, or you can query it with SqlClient in .NET apps like you would a regular OLTP database.
The cube, you can get data out using AMO objects in .NET, you can query using MDX, XMLA, etc in management studio, or you can write reporting services reports, but the best client is Excel 2007, its built to be tied to SSAS 2005, so it really works out well.One thing about all the technologies (SSIS, SSRS, SSAS, AMO, etc) is that there really isn’t a lot online as far as examples and documentation, or it is scattered. It is getting better though. Most of the stuff I do I just have to figure out on my own, through trial and error, but it is OK.
Maybe not the cover all response but it kind of covers what I have seen in the past, from my own experiences, and others.
Technorati tags: Business Intelligence, Analysis Services, SSRS, SSAS 2005, SSAS, SSIS, SQL Server 2005 Analysis Services, SQL Server 2005 Integration Services, SQL Server 2005 Reporting Services, BI, DW, ETL, OLTP, OLAP, Cube, AMO, Excel 2007, MDX, XMLA
So, today I decided to try out Microsoft’s site: CodePlex. I recently created a utility app to process dimensions and partitions in SSAS (SQL Server Analysis Services) because I didn’t like the way it worked in SSMS (SQL Server Management Studio). I don’t know because I never used it, but I guess in pre-SP2 you could process multiple objects at the same time, just hit process, move on to the next one. In SP2, the process dialog is modal so you can get around it, you have to wait for the object to process – yuck!
Also, I just wanted to try out CodePlex and see how it works, what I like and don’t like. Since becoming a contractor doing Data Warehousing and BI stuff, I miss coding in .NET (and any other language), so I want to keep that up in my free time. This is a result of that. Hopefully once I get used to CodePlex I will contribute to some other Open Source .NET Projects, sounds like fun to me!
Here is the link to the project: http://www.codeplex.com/SSASUtils
Some of the glue code for connecting and processing I gleamed from other examples and projects I have seen around the web, some in VB.net , some that didn’t even work, etc. Hopefully more SSAS Utils can be added to this project and it can become way more useful that it is now (although I do use it pretty much every day right now).
CodePlex: I like it so far. There are few things that could be done better, but I am using the Team Explorer source control which fits nicely into VS2005, really cool. I will probably post up more on CodePlex later. If I get the urge, I might create project for some other stuff I have lingering around my hard drive (FFMC, PocketBlogger, other random utils). I am anxious to see what the community participation is like, I tired GotDotNet back in the day and it was weak, we will see with CodePlex.
Technorati tags: SSAS 2005, SSAS, SQL Server 2005 Analysis Services, CodePlex, SSASUtils, Data Warehouse, BI, Business Intelligence, SSMS, SQL Server Management Studio, Microsoft, Open Source, .NET, C#, Programming
The other day, I was tasked with moving all objects from one SQL Server Reporting Services instance to another. I know you can move the database itself, but then you run into issues with encryption keys etc. I just wanted to move the objects. I know that pretty much everything in SSRS is extensible, so I knew I could write something to do it, but before I went out and coded it, I Googled first to see if someone else had something. Turns out there is an awesome utility: Reporting Services Scripter. Works like a charm!
Technorati tags: Reporting Services, SQL Server 2005 Reporting Services, Reporting Services Scripter, SSRS
As a short follow up to a post I did a little while ago on SELECT’ing the instance and machine names from SQL Server, here is how you get the Current Database Name
SELECT db_name()
Funny because I was looking for that online, and my Google search led me to my past blog post, so I figured I would actually blog about this specific function as well.
Technorati tags: SQL Server, SQL Server 2005, Current Database Name, Database Name, db_name(), SELECT
If you want to create a calculated measure in SSAS (SQL Server Analysis Services) that will always get the value for yesterday, this is what I have come up with:
CREATE MEMBER CURRENTCUBE.[MEASURES].[My Yesterday Measure]
AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),
FORMAT_STRING = “$#,##0.000”,
VISIBLE = 1 ;
What is really cool is you can test this before you even make the measure, using an MDX query:
WITH
MEMBER Measures.[Yesterday Measure Test1]
AS ([Time].[Calendar Date].&[2007-07-09T00:00:00],[Measures].[My Cool Value])
MEMBER Measures.[Yesterday Measure Test2]
AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),FORMAT_STRING = “$#,##0.000”
SELECT {
Measures.[Yesterday Measure Test1]
,Measures.[Yesterday Measure Test2]
} ON COLUMNS
FROM [My Cube]
and now you can take that calculated measure and tweak for any days back (30, etc)
Cool! 🙂
Technorati tags: SSAS, SQL Server Analysis Services, Calculated Measures, MDX Expression, MDX Query, MDX, StrToMember, VBA, Cube, Measures, Calculated Members
Steve Novoselac's Blog 