“The Microsoft SQL Server SQOOP Connector for Hadoop is now part of Apache SQOOP 1.4 and we are not providing a separate download anymore. Please note that Microsoft’s HDInsight service includes the connector as well.”

So I continue my search and indeed see that SQOOP does offer a download. Even found a detailed post about how to use SSRS against Hadoop. But apparently it’s all in vain as my colleague actually found from Cloudera a solution in form of an ODBC Connector, which he told me worked great for him. So I guess this means there are various ways to connect from SSRS R2 to Hadoop…

Report Model has been one of a set of deprecated features on SQL Server 2012 (and obviously also on SQL Server 2014). SQL Server has released PowerView in SQL Server 2012 in aim to use it as a replacement to the abilities Report Builder 1.0 offered before. Obviously, the fact that PowerPivot existed even before, should have signaled the end of use for the SMDL. So now for deprecated features you can clearly see that “SQL Server 2014 Reporting Services does not include tools for creating or updating report models”.

The way we worked with our projects with Team System in BIDS is that we had one solution for all the projects linked to that subject. That means that the SSAS and SSRS and Report Model projects I had for Financing were all linked under the same solution file in Team System in our Visual Studio. Looking at the breaking changes in SQL Server Reporting Services in SQL Server 2014 I understood that will no longer be an option. As the article states: “SQL Server Data Tools (SSDT) no longer supports report model projects”, which means that:

1. We’ll need to have installed BIDS for SQL Server 2008 R2 in parallel to SSDT for SQL Server 2014
2. We’ll need to have two solutions for the same subject: one will be for all the projects we developed so far which have a Report Builder 1.0 project attached to them (almost all of them). The second solution for the same project will be for all the new developments in SQL Server 2014 for that same subject.
   Opening a 2008 R2 solution in SSDT prompts you to upgrade it to 2014. The automatic upgrade breaks the RB project attached to that solution and means that you can no longer open that solution again in BIDS 2008 R2. Hence the need for two solutions.
3. We’ll need to change the data source for our RB project. Our databases are moving to SQL Server 2014 as well. Obviously, in SQL Server 2008 R2 we used a data source connection based on a Native OLE DB Provider of SQL Server Native Client 10.0

But that wouldn’t do if we need to connect from BIDS 2008 R2 to SQL Server 2014. So I changed the connection of the data source to be of type .Net Providers of SQLClient Data Provider and that worked out fine.

So, it seems that using Report Builder in SQL Server 2014 will require us to stretch quite a bit (as I’m sure is the case for anyone using it in SQL Server 2012), but I guess we’ll prove ourselves limber till we move our projects to a more updated environment.

Up till now, we would first have meetings with the customer and the operational system developer to learn what the client wants and what are the corresponding tables in the system.  We would then sit and write two documents – one for the customer detailing what we’re about to give him, and one for the BI developer detailing which tables should be extracted and how they should be manipulated. The client would have to wait two months (at least) after the document was approved, before he could see any report samples or visuals on his data.

For the sanitation department I did something different as it started out as a POC. The customer sent me and excel file containing all the data he had for a certain year. I imported the file into the database. I then created a simple pivot on the data with slicers and conditional formatting. I went back to the customer with it and he loved it. We defined together a few reports that would be of special interest based on that pivot. Only after he approved the POC I’m looking into receiving the data from the operational system and defining the import and manipulation processes for the tables in the DWH. After the development finishes, I’ll be able to show the customer the full report we defined together.

What we’re trying to do now is to recreate those same phases in other systems in development. We’re first trying to import sample data, show a report in Excel with a pivot on the sample data, and only after the customer approves the demo and defines sample reports we start with development.

As I always love to learn from other people’s experience I would really appreciate it if you could share with me in the comments below what are the different different steps in BI projects in your team (please!!)

Microsoft has been offering Data Mining in various ways: through numerous versions of the Data Mining add in for Excel, through Data Mining Models in Analysis Services and also through third party tools like Predixion Software. Well, apparently there’s more coming from the Microsoft Labs. What the Microsoft consultant showed us was the ability to enter Excel and look at a graph showing the data as a trend. Through dragging the trend line forward he could see the future demand over time and even choose to see effects of seasonality. Through another drag of the data he could limit the data for the forecast and see how that affects the new demand line. There was also a way to choose the acceptable range of values (the standard deviation). As it’s all still in part of the Microsoft Labs, I didn’t find a lot of documentation about it and most links were actually on a twitter post about Project Passau.

Thing is, the municipality isn’t really going to go into the cloud so quickly on account of governmental restrictions so I don’t see most of those capabilities applied here any time soon :(. Still, Project Passau is supposed to go public in a few weeks and I recommend you check it out.

Above you can see the report from the recycling center. When I looked at the horizontal and vertical slicers and the color scheme (obviously, shown behind the black bars in the picture :)) for the pivot table, I though to myself – that looks very similar to Excel 2010 BI capabilities! Now I knew Microsoft may have also used the idea of in – memory reports just as QlickView did, but now I understood I could use the Excel styling to make it look like a QlickView report as well..
The horizontal slicers in the reports are of: year, quarter, month, day of month.
The vertical slicers are of: City, contractor, driver, type of trash, type of vehicle, day of week, hour of entrance.
The pivot is hour of entrance and month.
I got from my power user data of about four months to play with and I made him this in Excel 2010:

The horizontal slicer is of day of week. The vertical slicer is of contractor (putting the relevant slicers is easy through the pivot table filed list).
The slicers are also connected out of the box and so when I choose a day of week I would only get the contractors working on that day.
The pivot is hour of entrance and month. I also applied the color scheme of conditional formatting in Excel.
The demo I did was based on a PowerPivot cube and so could not enable us a drill through, but I read that if I would base it on a table in my DB, then drill through would not be a problem. I would also like to add that drilling on a particular aggregate cell was not shown to us to be an option on the QlickView report we saw. instead the user was given an additional tab which showed the underlying data of the entire pivot table (meaning the operational data with the same filters as the aggregated data). So a drill through action from a specific cell was a functionality I could offer in Excel 2010 that was not given in QlickView.
I showed it to the customer and he was willing to drop his request we do it in QlickView. So I guess you can use Excel to imitate QlickView.

The security definitions for a view that’s based on a PowerPivot cube in Panorama are managed both through an empty SSAS cube and the Panorama Dashboard security definitions. We tried for starters to migrate the new user definitions to the server:

Unfortunately enough, that didn’t help. We looked at the empty cube to see if a user was missing, checked the Manage Roles for the book, tried synchronizing the roles… Nothing helped. So obviously, we went on to the next stage which was us clutching at straw.. We went into the folder on the Panorama server that held the definitions of the book. Apart from containing the definitions of the different views, it also had in it two files that manage the book, the Properties.xml file and the Schema.xml file:

I opened both files in notepad just to see inside of both of them that:

Which basically means that the book is connected to the original Excel file with the original PowerPivot file. And that’s even though I’ve just added to it views based on a new PowerPivot file. That didn’t make any sense to me. I went into the Panorama NovaView designer to look at the book and the views. Right clicking on the views I could see that they were indeed connected to their relevant PowerPivot cube. But right clicking on on the book properties showed me yet again that the data for it was connected just to the original cube. So, knowing that each view was already connected to the relevant PowerPivot cube, I decided to delete the definition for the book which was restricting it just to on cube:

I “ok” – ed the warning message I got for the delete. I tried re-entering as my user and … Voila! problem solved! (Sigh of relief)

So if your users are having a hard time seeing a new view in your book which is based on a new cube for the Panorama book, check if your book isn’t also restricted in it’s data to the first cube it was connected with.

Exception calling “Process” with “1” argument(s): “File system error: A FileStore error from WriteFile occurred. Physical file: . Logical file: . .

Errors in the OLAP storage engine: An error occurred while the ‘RN’ attribute of the ‘YYYYY’ dimension from the ‘XXXXX’ database was being processed.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Internal error: The operation terminated unsuccessfully.

Errors in the back-end database access module. The read operation was cancelled due to an earlier error.

Errors in the OLAP storage engine: An error occurred while the ‘RN’ attribute of the ‘YYYYY’ dimension from the ‘XXXXX’ database was being processed.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Server: The operation has been cancelled

The RN attribute was a running number that was the identifier for the fact – dimension. My colleague asked me if I had a thought what to do. My first offer was to set the AttributeHierarchyEnabled property to false, though I told him I wasn’t sure if you could do that for an attribute that is the key for the dimension:

Apparently you can’t. he tried to change that property and got a red squiggly line under the attribute and a notification stating that:

Attribute hierarchy must be enabled if attribute relationships are used. The key attribute requires attribute hierarchy enabled.

So I offered him to define in the DSV that the Logical Primary Key for the fact – dimension will be a combination of attributes that will identify each row:

He did that and apparently the key for the dimension hadn’t changed and stayed the RN attribute. The fact – dimension was still connected to itself through the RN attribute.

He tried the process again and this time it worked. Still, I would add that in a later look into the DSV I also saw that the table in question was a named query with calculations in it for RN. I would also suggest turning it into a pre – defined table.

So, in short, if you’re having a problem processing a large dimension, consider changing the combination of attributes defining its keys. For additional data, please refer to the Analysis Services Performance Guide chapter 4 Tuning Processing Performance.

I needed to predefine for our DBA the tables we needed to build based on those SharePoint lists. You can add a data flow destination to that SharePoint list source and double click on the Data Flow Path Editor:

Click on the Metadata tab and hit the “Copy to Clipboard” button and paste it to an Excel file so you can see the name of the columns in that SharePoint list and their SQL data type for your future DWH tables. For any further data on using SharePoint as a source or a destination to update, I really recommend you read the very comprehensive document on  extracting and loading SharePoint data in SQL Server Integration Services.

Basically each and every part of your report can be referenced by another part. Each cell you have in your report has a name. The names are usually automatically generated and are on the line of “Textbox 6” (or any other number). You can also give the cell a specific name of your liking in its properties pane under its Name attribute:

After having an item with a name, you can reference its value for its own styling. For instance you can define the color of the text in the textbox will be red if the value for the calculation is under zero or black if its zero or above:

=IIF(Me.Value > 0,”Black”,”Red”)

The same can be done for the background color of the textbox etc..

You can also reference the value for that textbox in the calculation of another textbox. For instance, I used the total of the talk time and number of calls for a certain day, to calculate the average talk time for that same day:

You can use the values in your report header and footer, and not just in other cells of that same tablix.

So, just in case it’s new to you as well, I’m suggesting that you look up the ReportItems Collection and see how else you can reference it in different parts of your report. It’s always important to use the work you did in the past to work less in the future  

In the past few months, we’ve been hearing in the BI team about Smart Cities. We can define a Smart City (according to Wikipedia) as efficiency based on the intelligent management and integrated ICTs (information and communication technology), and active citizen participation.

The term was initially presented to us through a company offering us a POC of a dashboard in SharePoint. They talked with us on what another city in Israel had monitored and talked with us on what was being done, in a very larger scale in New York:

• New York Open Data, APIs and Digital Roadmap
• NYC Mayor’s Management Report
• NYC Agency Performance Report

We grew curious.

We saw an example of open municipal data in Florence which invites the public to learn about the environment in the city, mobility in the city and security and more. Opening up the data in an approachable way was also done for the Israeli Budget  by a group of citizens trying to make the budget the more understandable for all its citizens through charts. Last but not least, we also liked the Performance Management Dashboard Boston was doing – a scorecard on the city responsibilities varying from education to public health.

Most of these Smart City initiatives were giving the public access to data and inviting them to learn and judge what was being done around them. We started looking in a site specifically devoted to Smart Cities and saw that some of the cities were taking it to being pro-active about what can be done or changed in their city to elevate the living standard.

We tried to think what we could do for the residents of Tel Aviv Jaffa based on the data already stored in the BI DWH, and came up with this:

1. We could also show the budget of the municipality in charts with a filter and a drill (up and down).
2. We offered a scenario of focusing on all the streets where we knew we had a handicapped parking spot assigned. We could then check how many parking tickets were given on that street for parking in a handicapped place, and when they were given. Thus, hoping to help give a better and quicker service for the handicapped residents of the city, (not having to wait for a car illegally parking in their spot to be towed away).
3. We offered a scenario of looking at businesses running in the city and seeing which of them were cutting back on store space (assuming this would be because business was running low). We then would cross reference that with stores that haven’t got a signpost (which each store needs to pay for to the municipality). If we would find a store cutting back without a signpost, as a matter of helping small businesses, we would suggest offering them to put up a sign for free for a few months. We also thought of collaborating between small businesses (a chocolate store and a florist for instance).
4. We offered to study each year in each neighborhood what were the top 5 complaints from residents. Categorizing those complaint into sewage, road condition etc., we could fix the needed infrastructure rather than continually patching things up.

We had all these ideas, but as it goes, not all of them were picked up…

So I’m asking you, dear reader – what would you like to get from your municipality? Do you have an idea on how to get better service from your city? Want to make an offer on how to define a Smart City? If you want to make an impact, please leave your comments in the blog!