Ella Maschiach's BI Blog : Report Model

Model Item Security and Identifying Attributes in Report Builder

Ella Maschiach — Fri, 07 Oct 2011 03:35:00 GMT

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).

BI Semantic Model – the new solution for SSAS and PowerPivot

Ella Maschiach — Sun, 29 May 2011 00:50:00 GMT

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!

Problem updating Report Builder 1.0 Model in SQL Server 2008 R2

Ella Maschiach — Fri, 07 Jan 2011 01:15:00 GMT

So, as you may have gathered from the previous post, we installed SQL Server 2008 R2 in the municipality not too long ago. And yes, that led to all sort of problems...

One of the main problems that all of us encountered, was that we couldn't update our report models. Yes, we could start a new Report Builder 1.0 Project. We could define a DSV and autogenerate a Report Model. But then, if we added a table to the DSV or new columns were added to an existing table, and then we would run an Autogenerate on the Report Model again, then... well it would fail. We would get an error message saying the COM Object could not be recognized and either get a new entity in the Report Model without any attributes in it, or we would get blank new attributes in existing entities. We would end up adding new attributes manually to the model, binding them to the relevant field and giving them the proper name. needless to say, that was pretty frustrating.

We searched the internet for the error message or something about problems updating Report Builder 1.0 in SQL Server 2008 R2, but nothing came up. After a few mails with Microsoft support Israel, the answer was found...

So what's the solution? Cumulative Update 5 for SQL Server 2008 R2!
SQL Server 2008 R2 CU 5 includes a lot of fixes, and not just on the client side, but also on the server side.

I would like to thank Idit Izak from Microsoft support Israel that helped us reach a quick solution.
Please give Cumulative Update 5 a look and consider installing it on your PC and server.

Error Message When Using a New Feature in SSRS 2008 R2

Ella Maschiach — Wed, 05 Jan 2011 03:46:00 GMT

We installed SQL Server 2008 R2 about 2 months ago, and I recently tried to use a new feature in Reporting Services 2008 R2. I used the Business Intelligence Development Studio (BIDS) to open a new Report Server Project. After creating a data source I also defined a shared dataset (a new feature to SSRS 2008 R2). I put some of the fields into a table and tried to run the report (still inside the BIDS). But I failed with an error:

The report preview failed because the report could not be built. Read the errors, warnings and messages in the Error List window for specific build features.

The data set, DataSet1, is a shared dataset. SQL Server 2008 Reporting Services does not support shared data sets.

I couldn't get it. I'm using BIDS from SQL Server 2008 R2 - what do you mean it's not supported? :S
Help was on the way quite quickly from Microsoft support Israel. Apparently, though I am using the BIDS from SQL Server 2008 R2, I still need to define my Target Server Version to SQL Server 2008 R2. The default setting is (for some reason) set to SQL Server 2008 (hence the error message).

Guess that just goes to show me, it's all about the little things (as always ;)).

Report Builder 1.0 vs. Report Builder 3.0

Ella Maschiach — Tue, 04 May 2010 22:36:00 GMT

The municipality has been planning to move to SQL Server 2008 R2. Now obviously I'm happy about it, but it also made me wonder about our Report Builder 1.0 models. I know Report Builder 3.0 doesn't have all the functionality that Report Builder 1.0 has. In some ways Report Builder 3.0 outdoes version 1.0 and in other ways it's lacking.

I was deliberating on how the move of upgrading to SQL Server 2008 R2 may affect us in the municipality. I mean, does that make our Report Models from 1.0 obsolete? Maybe there's no point in continuing to develop Report Models, as the next Report Builder versions (both 2.0 and 3.0) are not necessarily model driven as version 1.0 was.

So what is a girl left to do, but put up a question in the MSDN SQL Server Reporting Services Forum? Which is exactly what I did :)
I wanted to get a better insight into the Microsoft strategy for Report Builder in general. So, I understood from the answers that support for Report Builder 1.0 will not stop and that both Report Builder 2.0 and 3.0 support models. After you open a report with Report Builder 3.0 (or 2.0) you will not be able to modify it with Report Builder 1.0 again, as there is no backward compatibility.
Not too long ago, I saw that in his post regarding SQL Server 2008 R2, Robert Bruckner had put up a Report Builder 3.0 FAQ document. There are quite a few points there regarding Report Builder 3.0 and how it stands in comparison to Report Builder 1.0, so I guess I'm not the only one with doubts and hesitations... I really recommend you go over that document for a more in-depth and detailed look on the matter. Again, the message coming out of the document is - don't worry about it!
So I guess development will continue as usual here.

In the meanwhile, I'm looking forward to a standalone release of Report Builder 3.0 so I can give it a go with the SQL Server 2008 R2 Express version I already installed.

Can't see all the rows in your drillthrough report in report builder?

Ella Maschiach — Mon, 14 Dec 2009 01:36:00 GMT

I'm developing a report model in Report Builder 1.0 at the moment. I felt like it was finished and started testing the model with some sample reports I knew my client needed. And then I got the oddest thing…I would get a report saying something like:

Gender             Marital Status                Amount of Sales
NA                    NA                                865
Male                 Single                            30
Female             Single                            36

NA was what I chose to put in case I was using referential integrity. I was looking at the sales table and the employees table and if I found a sale which didn't have an employee, then I created a fictitious employee for that sale.So, I wanted to see all the 865 sales that had a fictitious employee for them. I clicked on amount of sales and only got 20 records :(… where did it all go?
I asked our trusted DBA to run a trace in the SQL Server Profiler to see what went wrong with the query in Report Builder. He got the query, ran it, and got the 865 rows! All I got was just the first row from all the sales. I couldn't understand what I did wrong… I looked at the Employee entity I had in the report model as the clickthrough report was being run on that entity's DefaultAggregateAttributes. The IdentifyingAttributes collection included Employee ID and Sales ID. The DefaultDetailAttributes collection also included Employee ID and Sales ID so I should have seen my fictitious Employee with all his sales. Alas, that wasn't the case.After a lot of searches on the web (which didn't amount to anything), I finally got an idea from a colleague (thank you Yaniv!) to change my DSV. In my DSV I had only defined Employee ID as the Logical Primary Key for the Employee table. I thought that in Report Builder what matters is just what you define in the model itself (the .smdl file). I thought that the DSV doesn't mean much just like it doesn't in a SSAS project (where what matters is not necessarily the connections between dimension tables and facts in your DSV, but rather the dimension relationships you define in the Dimension Usage tab for the cube).
So I did update the Logical Primary Key for Employee table in the DSV to be Employee ID and Sales ID. I ran Autogenerate on the report model, deployed it and ran the same report. Clicking Amount of Sales this time got me a drillthrough report of 865 records – what a relief!

So, if you're wondering why you're not getting all the records you should be seeing in your clickthrough report, check your DSV – it could prove to be the answer you're looking for.

Flexible grouping on data from your DW in Excel 2007

Ella Maschiach — Fri, 15 May 2009 13:25:00 GMT

I have recently built a data mart containing all the data of budgeting and accounting in SQL Server 2005 and created a Report Model in Report Builder for it. My users were looking for a flexible way to define different grouping on the data, without having to ask for my assistance. Defining new formulas in Report Builder isn't the most comfortable thing to do (specifically if they get complex and long), so we had to find another way.

There was a suggestion of creating a view in the database with their definitions for the grouping straight to SQL, but I admit that's not the solution I'm going to be talking about here. Two months back, Dany Hoter from Microsoft had given us a brilliant lecture about Excel 2007 abilities against a SSAS cube and the new abilities in Excel 2007 in general. It really turned me on to Excel 2007, and I wanted to try and implement a solution with Excel 2007 which we have in the IT department (or even Excel 2003 which my users have).

The main thought was to let the users get all the data they want from a report in Report Builder. They can download the data from a table they constructed in Report Builder down to a CSV file, which is then turned to an Excel file. By the way, I do recommend exporting the data from Report Builder to CSV and not straight to Excel as then you don't have in your file all the extra styling (which is usually unnecessary and takes some resources). The user can then create the grouping in Excel itself.

But I was stuck. Because all I could think of was nested "If" expressions which made the whole thing very hard to read and understand. Boris Kogan, our wonderful DBA came with (his usual) genius idea that solved my problem:

Left(Substitute(Concatenate(

If(x="613", "1801"),

If(x="614","1804")

),"FALSE",""),4)

First of all we do the basic "If" sentences. We concatenate the values we get from the If sentences. If a value didn't fall into any of the conditions met in the If sentence, then it should get "FALSE", which we replace by an empty string. In case a value meets more than one condition in the If sentences, then with the Left we take only the first If sentence it met.
After we got the different code groups, I added a sheet to my Excel which held the names for the code groups. Using the usual VLOOKUP, I gave the different group codes their relevant names. After that, I took all my data (the one from the data mart and the groups I defined on it) and created a pivot table on it.

You can implement the above formula in Excel 2007 or Excel 2003 without a problem. Still, there are a few things in Excel 2007 which I really liked:

1. The formula bar in Excel 2007 can be dragged down almost the entire length of the spreadsheet - how brilliant is that?! I really think that makes all your formulas so much more readable and easy to understand. In Excel 2003 you still need to scroll up and down, having to remember what came before and after the current sentence.

2. Excel 2007 enables you to define a table on the data you created. How is that good for you? Well, you can reference a specific column name in the table. Even if that column (p3 for instance) was in the spreadsheet in column A and then moved to column C, that won't matter for the formula to run properly.
The closest you get to that in Excel 2003 is by creating a list of a column - not the same ease of use as this time you need to define it for each and every column instead of Excel doing it automatically for all the columns of a table.
It also means that when you want to add a column next to the table in Excel 2007, it automatically detects it as a column which belongs to the existing table.

And if you defined a calculation in the first cell of the column, it automatically also applies it to all cells in the column.

3. Excel 2007 also has very nice design styles for your use, with a very simple UI for you to choose the report layout and some great conditional formatting which makes it all very easy on the eyes.

Anyways, even if you're not sold on Excel 2007, I think I really wanted to stress another point. If you have a power - user who wants to have flexible grouping on his data, without having to approach a developer from IT, he can get it through Excel. Excel can this way even elevate the usability of the model you developed in Report Builder (the new source of data for your user). No need for him to contact you, no need for you to define or alter anything in the database - it's all down to the tips of his fingers as he can define it all in the formula line in Excel. And I think it's a great way for you to further empower your power users who usually feel pretty good on working on it all by themselves.
So give Excel another look when you develop something in BI.

Filtering on a lookup attribute in Report Model

Ella Maschiach — Wed, 21 Jan 2009 20:53:00 GMT

I've recently created a Report Model with a Lookup Entity in it. I saw to it that I had only one default detail attribute for it, and I gave that attribute a ValueSelection of list. When I used the Report Model, I saw the attribute fine (as if it was inside the main Entity) but when I tried to filter on it I saw the Lookup Entity I was trying to hide and a filtered list. I couldn't understand why that was. I went back to check the attribute. Its ValueSelection was of type list. I went to...(read more)

The Lookup Entity and the Friendly Name in the Report Builder

Ella Maschiach — Tue, 15 Jul 2008 04:11:00 GMT

Did you, just like me, define a Lookup Entity in your Report Model? And did you, just like me, define friendly names in your Report Model's DSV? If you have, then you may have come across a problem similar to mine… So how did we get there? First and foremost, when you click the relevant tables and columns in your Report Model's DSV, you defined a FriendlyName property for them. That way, when you generate a Report Model based on the DSV with the friendly names, you get the Model with the...(read more)

How to avoid breaking reports based on your Report Model

Ella Maschiach — Mon, 09 Jun 2008 01:57:00 GMT

Almost every post I made about Report Builder, be it the pros and cons of creating a report model on a cube or refining a report model based on a cube, brought the same question - what makes reports based on a report model to break and how can you avoid it? Well I didn't know the answer as I admit it never happened to me, (sigh of relief), but the question did bother me enough for me to try and research it a bit more on the internet. And yes, I'm happy to say there is more than enough information...(read more)

Creating a Report Model based on an Analysis Cube – Pros and Cons

Ella Maschiach — Thu, 15 May 2008 23:41:24 GMT

Creating a Report Model based on an Analysis Cube – Pros and Cons If you follow my blog, you probably know by now that I developed a report model based on the Analysis Services cube I already had. I even got around to updating the report model . And then, my users started using it. And you know, whatever we do just seems so wonderful to us, until our users find all sorts of flaws with what we did… so yes, my users did open my eyes to quite a few problems that may occur when you build...(read more)

Inheritance in Report Model

Ella Maschiach — Sat, 03 May 2008 02:22:40 GMT

I recently finished defining and refining a report model based on a relational database. At first, I just set up my DSV and then added a report model with the “Autogenerate” option. Then came the grueling task of refining the report model that was generated… I was using the MSDN pages which were very helpful on that subject, but it’d be fair to say that I something to add to what they suggest for inheritance . See, I was using the inheritance property for my entity and deployed...(read more)

Updating your Report Model

Ella Maschiach — Sat, 09 Feb 2008 19:00:00 GMT

I know I had written of the simplicity and ease of creating a report model based on a cube, but I too would have to admit that even a good thing can be made better. Just in case you hadn't noticed, when you enter the report site you can manage your models. If you can't see the "Models" folder, simply click the "Show details" and you'll see it. Select the folder and click on the name of your model or on the "edit" icon next to it. You now have a chance to...(read more)

Creating a Report Model based on an Analysis Cube

Ella Maschiach — Thu, 22 Nov 2007 01:06:00 GMT

Well I guess congratulations are in order because I finally got around to using a report builder in my project. Turns out that building a report model based on a cube is as easy as 1-2-3. Still I had a problem, as for some unknown reason the translations were showing for my dimension names and my measures, but not for the dimension's attributes... At first I thought maybe this is a problem solved by Service Pack 2 (as my tests were done on a Server with Service Pack 1). Alas, no. So, a further...(read more)

Recreating a Report Model

Ella Maschiach — Wed, 31 Oct 2007 20:55:00 GMT

Remember how I taught you to recreate your Analysis Project ? Well, let's say this time you have a model you deployed to the Reporting Services Server, but you have changed it since then and want the old version back. So how would you recreate the model you have on the server? First of all, create a new Data Source View in your Analysis Project. It doesn't have to include all the relevant tables, as you'll only use it as a shell to build upon. After you've finished defining the DSV...(read more)