Introduction to DAX basics

So you have all the resources you need to start learning DAX – now make those 30 minutes to actually do it :)

SQL Server 2012 Denali Release Date

I was in the BI User Group meeting today (and if you're in Israel, why didn't you come too?) and heard something quite exciting. The speaker was Yossi Elkayam from Microsoft Israel and what he mentioned twice was that the release date for SQL Server 2012 Denali is March 2012. He was saying that while stating next month's meeting will preview the new release scheduled for the month after that. He also mentioned that PowerPivot version 2 will be released in proximity to the release of SQL Server 2012 Denali. This was some really exciting news for me because I remember hearing in one of the BI User Group's meeting that the release is scheduled for the first quarter of 2012, but that was put as a hopeful date rather than a closed one.

So upon hearing that the release is planned for March 2012 my first thought was that it would probably be on March 8th. That's because I was planning on going on vacation then. Somehow it made perfect sense in my mind that Microsoft would release the new version of SQL Server while I'm away and unable to read all the news and blog about it! Seems I wasn't too far away from the truth…

Microsoft is organizing a very big online event to launch SQL Server 2012 on the 7th of March! Both Ted Kummert Corporate Vice President, Business Platform Division and Quentin Clark Corporate Vice President, Database Systems Group are going to give keynote speeches. And if they're going to be there, I suggest you enlist as well ;)

So hurry up, the clock is ticking on the release of SQL Server 2012 Denali and Power Pivot v2

Using a filter on a calculated field in Report Builder

A short and sweet tip on using Report Builder 1.0

So, my users are still using Report Builder 1.0. I recently discovered that using a filter on a automatically calculated field there doesn't work. I tried running a report where I ran a filter on a year generated filed of a certain date. In essence, I filtered on Year creation date = 2011. The report took ages to run so I took a look at it on the profiler to see the query that was run. Apparently, it wasn't even running the filter correctly.
Changing the filter to creation date on or after 01/01/2011 ran fast and more importantly - the query was correct :)

#SQLFamily – putting a little love in your heart

I don't know if you've noticed it, but I don't blog all that much lately (I know blog, I know - but that doesn't mean I don't love you!). So as you may gather that if I do blog, it must be something that seems important to me.

The SQL Server Team has recently blogged about an initiative they're doing with the Pragmatic Works Foundation. Pragmatic Works Foundation is a non-profit that provides free technical training to veterans, the jobless, and underemployed. In 2012 they're doing a course for returning veterans. The SQL Server team will donate $50 for each story published about how the #SQLFamily has helped you (please visit the link for more details). Please send yours to sqlfamilysubmission@live.com.

So I can talk the talk, but can I walk the walk?
The #SQLFamily for me is a lot of pieces forming a very big picture. It's articles about various topics in Simple-Talk which I read to better know SSIS and SSRS. It's people in the forums helping me find a solution to a problem I'm stuck with, or learn how to use a pre - released version of SQL Server or have a place to ask questions about the integration of SharePoint and BI. It's also a chance for me to meet up and talk with my peers at the BI User Group Meetings and see what great uses there can be for the technology (and for that thank you to Itay Braun and Ronen Chenn for organizing the meetings). It's where I also got to give a presentation about the Parent Child dimension and hear from other developers what they did with it in their work.
#SQLFamily for me is just like a family in the sense that it lets you learn and grow, supporting you all the way.

The news nowadays is filled with talks about a coming recession and economical crisis - no better time than now to give back.

SQL Server 2012 Licensing Reviews and Views

Microsoft has recently announced the SQL Server 2012 Licensing. The main news is the new BI edition released with SQL Server 2012 which is positioned between the Standard edition and the Enterprise edition. You can read an SQL Server 2012 Licensing review on the Adatis blog which lists the pros and cons of the new edition and payment method. Amongst other things, they point out that:

"The Business Intelligence edition strips away
- Advanced Security (Advanced auditing, transparent data encryption)
- Data Warehousing (ColumnStore, compression, partitioning)
and provides a cut-down, basic (as opposed to advanced) level of High Availability (AlwaysOn)"

I felt like I read mostly good feedback from different bloggers on the matter, coming from Chris Webb and also from Teo Lachev. So that made it stand out for me when I saw that in Facebook Donald Farmer wrote in his status:

"The new licensing for SQL Server BI Edition feels like an early Christmas present from MSFT to #qlikview - thanks!" (With a link to the Adatis post on the matter).

So, just in case you're new to the field of BI, then you should know that up till January of 2011 Mr. Farmer was greatly perceived as the face of Microsoft BI. But on January 2011 he chose to leave Microsoft for PowerPivot's biggest competition - Qliktech. I value Mr. Farmer a very great deal and so, I was more than intrigued to hear his full opinion on the matter. He wrote me that:

"I think I understand what MSFT are doing, but I think it is a mistake for them. Standard edition was a great way for companies to start out on the BI journey. Now BI Edition is good, but more expensive (although still competitive). But it does not include SharePoint which is crazy - collaboration is critical to modern BI, and the latest MSFT tools like PowerPivot & Power View need it. Enterprise SharePoint is a big additional cost.
Of course I believe they are getting ready for a cloud offering at a lower cost. But cloud is a different mode of working and we at QLIK see quite slow demand for cloud BI because not enough data lives in the cloud yet."

(Please note the added links are put in by me and Mr. Farmer had only given me basic text).
The remark about the absence of SharePoint in the licensing offer was also expressed in a comment left in Mr. Chris Webb's blog by Mr. James Snape. In SQL Server 2012 there is a growing connection between SQL Server and SharePoint. Microsoft doesn't just enable you to integrate SQL Server with SharePoint, but also develops SQL Server 2012 features to work only on the SharePoint Platform.

I can't help but wonder what Amir Netz, who is chief architect for Microsoft's BI offering, is thinking about the new Licensing options for SQL Server 2012...

Model Item Security and Identifying Attributes in Report Builder

I found myself needing to define Model Item Security on a Report Builder 1.0 Model I did some time ago. I followed the guidelines detailed in the Model Item Security page (you can read more also about Securing Models). First of all, I would like to call to your attention that Model Item Security works only if your user isn't a Content Manager or Publisher (as noted in this article about Security in Report Builder 1.0. Please note that it describes the process in the way it worked in SQL Server 2005, which has changed for SQL Server 2008 R2).  Also, as stated in the same article, you cannot create a subscription to a report which has Model Item Security in it (also mentioned in a post about Report Subscriptions by Bob Meyers).

Still, I found I had a problem with implementing the item security. I needed to show my users the total of files, but not the file ID. File ID is one of the Identifying Attributes of the entity.  The user interface in the Report Manager would only let me hide File ID (and subsequently all its derivatives) without stating that Total file ID should be shown. So, I thought that maybe if I make the automatically generated Total of files as an attribute all of its own (and not as a variation of file ID) then that should work. Not so. After I turned the property of VariationOf from "File ID" to "None" for the Total files I tried to deploy the Model and got:

The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version.

Now the server was fine, but apparently disconnecting an automate aggregate from its source attribute can cause such an error message. I made it a variation of file ID again.
I also found that hiding an identifying attribute of an entity hides the entire entity(!) from the relevant user.

So as you may gather from the post, this caused me some woe... so the solution? As offered to me by our wonderful DBA Boris, I set the property of Hidden = True for the file Id and the Total of files. I then created two independent and unconnected expressions: one equal to file ID and one equal to Total of files. I could then hide each one independently, without causing me to hide the entire entity.

Now, I don't know if you've noticed but this is me writing in 2011 about Report Builder 1.0 which was released around 2006. Report Builder 1.0 isn't really supported nowadays by Microsoft as far as I know. But I keep working with it, and by blog statistics, you're still reading about it quite a bit here!

So, it's not my Birthday, but I still wanted to make a wish... if Microsoft is reading this (and I'm under the impression you are), then I would very much appreciate it if you could start developing us a wizard. You made a very simple wizard that converted SSAS cubes from 2005 to 2008 or 2008 R2. I would REALLY appreciate such a wizard from Report Builder 1.0 to a PowerPivot Model (or a Report Builder 4.0 if planned).

Why are rows (still) missing from a SSAS cube?

Panorama Necto

We're currently using Panorama Nova View 6.2 in the municipality and we had a presentation of the upcoming version - Panorama Necto. So what's new?

Well, first of all - now there's just one entry point to Panorama, both for the user and the developer. So that's one entry point instead of one for the flash, java, desktop and dashboard. We're still using the desktop environment in Panorama 6.2 and we've come across more than once that things work in the desktop but for some reason don't show on the web. This should put an end to that problem.

Pages in the book and dashboard are now replaced by workboards.

On entry, you can choose one of the next views:
1. My recent workboards - the last workboards you worked on.
2. My favorite workboards - the workboards you liked (by clicking on the relevant button).
3. My relevant workboards - Panorama recommends you look at other workboards according to your likes and the likes people in your team gave to different workboards. 
4. My recent discussions - a discussion between different workers of the same team on a certain workboard.
5. My recent subscriptions - in case you subscribed to a certain workboard.

Under "My recent workboards", "My favorite workboards" and "My relevant workboards" you can review workboards in a gallery quite similar to PowerPivot Gallery in SharePoint 2010. Other similarities to SharePoint 2010 also include a "like" button and a commenting option you have for each workboard.

"My recent subscriptions" gives me direct access to private workboards of other users (in case they chose to share those workboards with me and I have permission to the data). No need to send a page to be saved. Instead, you can give your co - worker a direct link and discuss online a workboard you created.

Components can be moved and resized directly on the viewing screen. There's no need to constantly move between a design area and a viewing area to do that. You can maximize and minimize each component on the workboard to either concentrate on it or give other things more space.

Graphs can also be changed on the fly.

Smart Report (the print out version of the view) got redesigned.

You can leave a comment per member in a report or per data cell. (A comment is left only per a specific workboard).

One click insight - a new automatic exception that marks a difference between the current period and the one before it. We can choose the size of the difference we want to check with a slider.

There is also a button to filter just those cells that have a marked exception.

Wherever we see an exception marked from the one click insight we can also click the little red triangle marking it for "cause and effect". That gives four matrixes with 4 suggested causes Panorama suggests as an explanation for that exception. 

The social bar - Panorama is bringing the social feel into analysis. You can either choose to look at all the users which are allowed to watch that workboard or just a list of friends. You can then organize a sort of "meeting" online by picking a few people and connecting them to a certain member or cell in the workboard.

The superboard - according to Panorama offers you a screen that shows a "users who viewed this workboard also viewed" with recommended workboards for you and a "similar workboards". A bit like Amazon offers you other products according to the product you just chose.

Navigation control - before, you would have to code to develop a button that would enable you to create a filter for the entire page. Now, panorama offers you a chance to create it with just a few clicks!

A search box enables you to search a specific member without even opening the relevant attribute or dimension.

Like the previous version, this one also enables you to connect to the UDC model (Panorama's connection to a relational data layer) and PowerPivot which was deployed to SharePoint. I understood that Panorama is also working on creating all of this against SQL 11's BISM.

So, a few words to summarize:

I feel that quite a few of the new features have already been presented in SharePoint 2010. So if you do have SharePoint 2010 already installed in your organization or you're about to install it - then that's not an advantage for you.
Still, I really like the fact that components can be moved and resized directly on the viewing screen and the navigation control, making a filter just a matter of a few clicks. Most importantly, I really appreciated that now it's just one environment, for the user and the developer (no more desktop vs. web).

I think the main forte for Panorama is still its OLAP views. If you're using them as a cube viewer then you will most likely also enjoy incorporating them in your Dashboard. Even in SharePoint 2010, you still get pretty static reports and scorecards in PerformancePoint 2010 (though visually beautiful). But if you want the user the ability to play with the data, you're still going to use Panorama.

In a recent convention I went to, most of the attendants were using Panorama Dashboard (the current version) and were pretty satisfied with it. And it's not because they didn't have SharePoint. The new Panorama has a lot to offer and you can choose what of it you'd like to leverage. Though I don't think we'll use in our organization all the capabilities through Panorama and not through SharePoint, I still think we'll be thrilled to use Panorama Necto, so go check out the demos for yourself.

Query Performance and Partition Scans in SSAS

This blog is SUPER SUPER excited, because for the first time, after four years, we have a guest! I met Mr. Kobi Deutsch in the SQL Explore Convention held about two weeks ago. During a session we both took there, he mentioned something I wasn't aware and I thought maybe I'd bring it to you as well. So, I'm leaving the stage for Mr. Deutsch:

Sometimes, when we check the MDX requests in the Profiler, we see that there is a partition that was scanned and loaded into memory although it is not suppose to be touched at all.
How can we know why this happened?

We have to remember that for every partition in the cube there is a slice that defines its boundaries. These boundaries must be composed of one member or a range of members only. When we run a process, the boundaries of the partitions are set as the lowest member and the highest one.The values of these boundaries are saved in the internal values of that specific partition. If we want to see what these values are, we have to look in the folder for the problematic partition.  
For exapmle in adventure works, the "FactFinance" partition

When we look inside of the INFO.XML file we get something like this

The values in the:

M_MININDEX and  M_MAXINDEX

Are the parameters that we are looking for.
We can find out the original member value by using the function DATAID (MEMBER) in the MDX WINDOW in the Management Studio.
For example:

with member CurrencyId as dataid([Dim Currency].[Dim Currency].currentmember)
select  [Dim Currency].[Dim Currency].members  on 1
,CurrencyId on 0
from [Adventure Works DW]

The DATAID value and the member value are not same and when we give some range of members to the partitions it's not always the same in the DATAID members.
Also sometimes we have the "unknown" member that his DATAID value is always 1.  
It's enough that we have even one record with an "unknown" member to break the boundaries of the partition and create a larger scan than what we expected.

For more details, please check a post by Mosha Pasumansky on Optimizing Partition Slices and an article by Chris Webb on Query Performance Tuning in SSAS.

On a related note, I would like to say the session both me and Mr. Deutsch attended was "Take Analysis Services to the Extreme" given by Mr. Itay Braun. I highly recommend you check Mr. Braun's slides and blog for further information on better understanding and tuning your cube.

Error Processing a Cube: Key cannot be null. Parameter name: key

I recently opened a new project in SSAS 2008 R2. After finishing developing the cube, I deployed and processed it on the server. I got a really weird error message that meant, unfortunately enough, nothing to me:

"Key cannot be null. Parameter name: key"

So what's a girl to do? Well actually, I remember that every so often when I was developing a cube, I would have a problem with the data connection. The default I had defined for me when I opened a new connection was .Net Provider\SqlClient Data Provider.

And somehow the default definition would always create an error in the process of the cube...
So I decided to try that first. Changing the data connection to Native OLE DB\SQL Server Native Client 10.0.

Deploying and processing it worked. I love me a good short fix! :)

Update July 14^th, 2011:
Another member in my team found this solution applicable to his problem. He had a connection string that was defined as: Native OLE DB\Microsoft OLE DB Provider for SQL Server. What he got was a process that gave him all the labels for all his members as "???" (question marks). That's even though right clicking the table in the DSV and chosing Explore data showed the Hebrew correctly. Locale Identifier was already set to 1037. I wondered if it was a matter of collation... no, it was redefining the data connection to Native OLE DB\SQL Server Native Client 10.0. I'm hoping for better error messages. One day...

SQL Server 2008 R2 SP1 download released

Yes, you read it right, Service Pack 1 to SQL Server 2008 R2 was just released. This service pack (as ever) is a bundle of all the cumulative updates thus far for SQL Server 2008 R2. It includes, amongst others, the following things:

And while you're looking into the SQL Server 2008 R2 SP1 download, you can also check out the Denali CTP3 download as well (they've been busy as hell in that team lately ;))

Four More Years! (And then some more please)

Me and the blog, we're celebrating 4 years together. It's been quite a hectic year for me personally, but the blog was there all along. It was being it's old supportive, giving and stable self all along. And I love it for it.

Still, I thought this would make for a good chance for us to get to know you! I remember seeing (and loving) this post from Adam Machanic and thinking - he hit the nail straight on the head.

Whether you're new here, just popped by for a visit or have been a long time reader, I'd love to get to know you! Tell me your name (first name or full name or even just a nickname if you prefer), where you're from, where do you work, what is it you do, how did you stumble around here, do you have a blog of yourself? Or rather - just say whatever it is you'd like to say about yourself. Me and the blog will be happy to make your acquaintance :)

BI Semantic Model – the new solution for SSAS and PowerPivot

TechEd North America 2011 wrapped up last week with big news about PowerPivot and SSAS. Just in case you haven't read it already somewhere else, I'll give you a very short re-cap. In SQL Server 2005, the UDM was supposed to encapsulate the solution for modeling your project. In SQL Server 11, codename Denali, it's going to be replaced by BISM - the Business Intelligence Semantic Model. All the reporting tools in the Microsoft BI Stack will work against this model. In Denali, you will be able to use BISM both for tabular model in PowerPivot and for cubes in SSAS. Furthermore, cubes developed in earlier editions of SSAS automatically become BISM models. SSAS cubes will still have storage in form of MOLAP or ROLAP and queries running against them in MDX. PowerPivot will have VertiPaq or DirectQuery with DAX queries running against them. Crescent, the new reporting tool, uses only DAX at the moment and so will only work against PowerPivot.
Ok, so that was a very short summery of a very important post made by T.K. Anand. I strongly recommend you read his post to get a fuller view of what to expect in Denali, both for PowerPivot and SSAS. Additional links and data can be found on Teo Lachev's post on BISM.

I guess I would also add the most important point made in Mr. Anand's post:
"Another limitation in the upcoming CTP release is that models built using the multidimensional project will not support DAX queries (and thereby Crescent, which uses DAX to retrieve data from the model). We recognize that removing this restriction is very important for customers with existing Analysis Services solutions to be able to upgrade to SQL Server "Denali" and leverage Crescent. The product team is actively working on it and is committed to making this functionality available to customers." Which hopefully means we'll be able to move easily between tabular projects and SSAS cubes!

Currently, when I open a new project in BIDS, I have to make a choice in advance - do I want a SSAS project or a Report Model 1.0? Granted, I can make that choice as before I start developing I have already decided in my system analysis stage what I need to develop. Will it have a Parent Child Dimension? Many to Many Relationship? A need for a complex calculation? If one of those exists, I will use a cube. But if the solution is simple enough, it's up to me to choose between Report Model 1.0 and a SSAS cube. Hypothetically speaking, I could use the same DSV for both projects (providing the tables and the relation between them are simple enough). But, as I mentioned, I do need to decide early on what it is that I'm developing.

The ability to move from one solution to the other seamlessly would be amazing! So many projects start off simple, and then, as time passes, get more and more complex... the thought that I wouldn't have to begin development in the BIDS from scratch if I need to move from a Report Model 1.0 to a cube (or rather from PowerPivot to a cube) is fantastic. And I admit that that may finally put an end to me developing in Report Builder 1.0 and move to PowerPivot. After all, I remember when Ariel Netz talked to me (in not so many words) about Project Crescent as a mean to move developers from using Report Builder 1.0. Crescent does seem to have the functionality that Report Builder 1.0 has (and Report Builder 2.0 and 3.0 lacked). But it only works at the moment with DAX queries against a PowerPivot model. If it could work against a SSAS cube as well - it would be the ultimate solution for me. (Especially as Report Builder 1.0 against a cube works quite poorly). That would be like the final seal for me to know that I need to move my models from Report Builder 1.0 to PowerPivot.

I really feel like the solution is near. First of all, I feel like Microsoft already sees it that way if the SSAS team blog switched from being called "The SSAS Team blog" to "The SSAS and PowerPivot Team Blog". I don't take those semantics lightly and I'm sure nobody else in Microsoft does either. Secondly, we know that a tabular PowerPivot model, when deployed to SharePoint, can be viewed as a cube (which also enables Panorama NovaView to work against it). So, I am hoping the solution for is close. Maybe even in SQL Server 2011?...  
In the meanwhile - the future is BISM!

How to not add another table to your DB (by using Excel!)

The pros and cons of Report Builder 3.0 over Report Builder 1.0

So yes, I'm still thinking about RB 1.0 vs. RB 3.0. We've made the move a few months ago from SQL Server 2005 to SQL Server 2008 R2. Most of our projects in the team, if they're not cubes, are Report Builder 1.0 models.  I was really looking forward to seeing how we can move our users from Report Builder 1.0 to Report Builder 3.0. My conclusion? Except for a few very specific cases - we can't.
We actually still have Report Builder 1.0 as a click once application from our Report Portal of 2008 R2 and not Report Builder 3.0. For the few Report Builder 3.0 customers we do have, we had a local install of the application.

Now I know, Report Builder 3.0 isn't meant to "match" Report Builder 1.0 in its use. Report Builder 1.0 gives your user a semantic layer for him to create ad hoc reports against. In Report Builder 3.0 the main thought is that your power user will write a T-SQL query to pull all the data he needs for an ad - hoc report. In effect, that why Project Crescent was started - so you could get an answer to Report Builder 1.0 which hasn't really changed since it was first released. So the comparison may not be very correct, but still...

I'll go over what I think are the main points of what I liked (and liked less) in each version:

1. Automatic fields - now I know that Report Builder 3.0 is all about you writing the query yourself, but creating a field for year and month for every date I pick is such a drag!! Getting them out of the box in Report builder 1.0 is that much more comfortable. The automatic sum and average you get for measures in RB 1.0 isn't missed though. You get it already when you use the measure inside a table in RB 3.0.
2. Filtering - even if I created the query for my user, I still don't feel like he can use it all on his own. Specifically, unless I pre-define him parameters, he cannot filter easily and effectively by himself. Filtering the tablix in Report Builder 3.0 is equivalent to the filter on a table in Reporting Services. You have to either give an exact value which you know exists or know how to define an expression. The filters are defined with an "and" connecting them. Both of these points are in contrast to what Report Builder 1.0 gives you - a dropdown list of all the possible values for an attribute and a chance to connect the filters on the attributes with "or" and "any".
3. Visualization - obviously, Report Builder 3.0 demolishes Report Builder 1.0 visually speaking. The chance to use graphs and gauges from Dundas and maps really helps you get the point quickly and effectively to your user. I cannot put into words the importance of visualization (pun intended :) )
4. Shared Datasets - Report Builder 3.0 gives you the chance to use shared datasets. That proved itself quite useful when I found myself creating quite a few different report that all used a parameter based on time. Instead of having to write the same dataset for the time parameter in each different report, I could re-use the dataset I created the first time, as I defined it to be shared.
5. Bundling - in Report Builder 1.0 you have entities and attributes are of a certain entity. I think that really helps the user find the relevant fields. If he wants to get an attribute of a Worker, he would go to that entity and pick from it. Also, you have the chance to group a few entities together inside a folder or group attributes inside an entity into a folder. You can create perspectives on your model (like on a cube) so that your user can see only the entities that are in a certain subject, and not see the entire list of entities that exist in the main model.
   In Report Builder 3.0 however, you only see a long list of attributes. You can choose the order of the fields in the list and so it can be either alphabetical or all the attributes from a certain subject, and then from another subject. But it's not terribly useful. Report Builder 3.0 is just for those specific fields, else the user gets lost. Report Builder 1.0 however, is for an entire world of content, and it's still comprehensible and useful.
6. Team System (or Source Safe if you will) - the thing that's most important for me was saved for last. Report Builder 1.0 Project can be added to Team System (or Source Safe). That enables me to keep track of versions, give them labels, go back to the version from a previous date and so on. Problem is - Report Builder 3.0 doesn't have that. That basically means that if I created a query for my user, and he somehow ended up deleting fields from it, then I would have to write it all up again - very worrying!

So, like I said before, we're not moving away from Report Builder 1.0 just yet, even though it does have it setbacks. Here's me crossing fingers for Project Crescent!