Two more video's of Excel Gemini

Two more video's of Excel Gemini. It looks cool. The key is the enormous fast calculations and orderings. This is done using ”in memory cubes”. It is impressive, but keep in mind that other competitors like Cognos, already have this feature. Not for excel do.

Wrox Professional Microsoft Sharepoint 2007 reporting with SQL sever 2008 reporting services review

I'am currently working on a Microsoft Sharepoint business intelligence project.

To prepare for the project I read the book Wrox Professional Microsoft Sharepoint 2007 reporting with SQL sever 2008 reporting services.

What a disappointment that was! Do not read or buy the book, if you are a tiny bit familiar with reporting and deploying. I really thought it would go in-depth, combining Microsoft Sharepoint and business intelligence. Talking about structure, function, enc. But that’s not the case.

The first 9 chapters is about creating reports and deploying. Chapter 10 is about some overrated and complex AJAX solution to deploy and display your reports. Chapter 11 is about gauges. Very short and obvious.

Off course everybody needs a basic understanding and every organizational needs are different, but there is an certain essential that is missed.

Deployment is very imported, but not worth 3 chapters. Especially not when your pretty new to this, and that’s the books audience. You want to know how to create a fast, efficient and good solution for the end-users.

Summarizing:
If you want to know more about deployment and care less about how the reports are shown and interact, this book is for you (although is pretty expensive for what you get). If not, please find another book. There are much better books out there.

http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SharePoint-2007-Reporting-with-SQL-Server-2008-Reporting-Services.productCd-0470481897.html



Professional Microsoft SharePoint 2007 Reporting with SQL Server 2008 Reporting Services
Coskun Cavusoglu, Jacob J. Sanford, Reza Alirezaei
ISBN: 978-0-470-48189-9
Paperback
480 pages
September 2009

Import SAP data to MS Gemini in under 1 minute for self-service business intelligence

This is just cool and fast:

Microsoft Gemini preview video's

Some preview video's about Microsoft Excel Gemini. It looks very promising:

Also read:

http://business-intelligence.kdejonge.net/working-with-gemini-and-excel-2010-to-make-a-pivot-table

http://blogs.msdn.com/bi/archive/2009/05/04/preview-of-project-gemini.aspx

Setting target audience in Sharepoint using Active directory

I'am currently working on a Microsoft Sharepoint BI project. It's a rough road, but we are getting there. The base foundation for Microsoft Sharepoint users and security is Active directory.

What I tried to accomplish is a portal page with three main parts:

- Dynamic reports, showing only those reports relevant, depending on the usertype;

- A main KPI about the topic;

- Key figures about the topic.

As said, I want to create the reports dynamically. This is for two reasons:

1. For more effective transaction and interaction with the enduser;

2. For security reasons.

i.e I have 2 types of users:

Managers;

Operational employes.

Both usertypes need to see different reports. The employe wants to know if the have enough supplies left and the type of orders.

The manager on the other hand wants to know if his department is meeting the organizational goals. He want information about salaries, costs, enc. The manager does not need to know the order details, cause it isn't relevant to his job and the employe doesn't need to see the main salaries for security reasons.

So I created these two active directory groups and added the users. I created the same group in Microsoft Sharepoint.

The next step is adding a report to a webpart and set the target audience to the appropriate group.

But there things went wrong. Altho it sounds doable, Microsoft Sharepoint doesn’t allow Active directory groups in a Sharepoint group to be assigned to a target audience. It could cause performance issues.

There is a second but: I’am not happy having to create an Active directory group and then the SAME Sharepoint group for management reasons.

...but there is hope. For my first concern, there is a hotfix available:

SYMPTOMS

You add Active Directory user accounts and Active Directory group accounts to a group in Microsoft Office SharePoint Server 2007. When you designate the SharePoint group as the target audience for a specific Web Part, some user accounts in the SharePoint group cannot access the Web Part.

CAUSE

This behavior occurs because only individual user accounts that are assigned to the SharePoint group are recognized as the target audience. Only those individual user accounts can access the Web Part. However, when you add an Active Directory group account, the users in the group account are not recognized as part of the target audience. Therefore, user accounts that reside in a group account that has been added to the SharePoint group cannot access the Web Part.

RESOLUTION

To resolve this issue, apply the hotfix package that is described in the following Microsoft Knowledge Base article:

942819 (http://support.microsoft.com/kb/942819/ ) Description of the SharePoint Server 2007 post-Service Pack 1 hotfix package: January 31, 2008

Back to the top

http://blog.tomaselfving.com/2007/12/moss-audiences-with-sharepoint-groups.html

http://support.microsoft.com/default.aspx?scid=kb;en-us;948681

Hotfix

http://support.microsoft.com/kb/942819/

QlikView on Your iPhone

One of the most fun tools i came a cross to work with is Qlikview for sure. It is very innovative and playful. Now the brought business intelligence to the iphone.

I will write a full review after i finnish with wabit!

http://www.qlikview.com/

QlikView for iPhone delivers the first, truly interactive, mobile BI app built specifically for the iPhone and iPod Touch. It fully leverages iPhone’s native multitouch and GPS features, resulting in a groundbreaking app that puts the power of QlikView’s sophisticated real-time business answers in the hands of iPhone users.

First industry reviews

First looks of QlikView for iPhone by independent reviewers have been, to say the least, very favorable: “I found it to be very quick and easy… All in all, this looks like a great tool for those who want real-time access to their business intelligence data and want to use their iPhone to get to it,” reports Daniel Green on Macworld.com. Another review on Axis Group BI Commonsnoted how QlikView for iPhone “is considerably faster and with better functionality than navigating the AJAX versions of QVWs in the Safari mobile browser.”

Download free from Apple iTunes Store

QlikView for iPhone can be downloaded for free from Apple’s Mobile App Store on iTunes. If you’re not a QlikView user and haven’t access to a QlikView server, you can use the default to QlikTech’s public demo server to experience QlikView on your iPhone with one of our demo apps.

Test with your own data

If you’re not a QlikView user and haven’t access to a QlikView server, but want to try QlikView for iPhone with your own documents:

1. Install IIS on your machine
2. Download your Personal Edition of QlikView (Version 8.5 + or higher including the Zero Footprint Client)
3. Download and install the QlikView for iPhone app via iTunes.
4. Start the app, and choose "Edit" on the first screen.
5. Add details for accessing your QlikView Server.

If you are a QlikView user and have access to a QlikView server, just follow steps 3 to 5 above.

FAQs

1. I don’t want my data/user credentials sent across the web unencrypted. Is encryption/security supported?
   Yes. Just set up a VPN to access your server. (see Apple Knowledgebase article on setting up a VPN on your iPhone or iPod Touch)
2. What license arrangements do I need in order for multiple users to take advantage of QlikView for iPhone with our own server?
   The iPhone client uses a standard user CAL (Client Access License).

Importing and exporting in Informatica Powercenter

When you work with Informatica Powercenter you probably have a development and production repository. This means you will have to transfer workflows form one repository to the other. You can use the copy function, but that doesn't work very well.

Instead you can use the import and export function. This works pretty good, but be sure to use the import and export function in the repository manager. The XML created by the repository manager is far more extended then the one in the workflowmanager.

But its not all milk and cookies.I often noticed that after importing my connections where gone. Second, the folders for the sources in the development repository are different then the folders in the production repository (i.e. for management reasons). I my case, we had less folders in the production repository so we had to fit some sources to different folders. This was a bit tricky.

I used the following trick to speedup the process:

• Edit the XML using a good editor.
• Find the connectionstring and alter it to the correct production stringname.
• Find the foldernames and alter those to the correct production foldername.
• Safe the XML and start your import again.

i.e.

Bronnen" DESCRIPTION ="" NAME ="Products" OBJECTVERSION ="1" OWNERNAME ="rd" VERSIONNUMBER ="1">

Bronnen" description="" name="Products" objectversion="1" ownername="rd" versionnumber="1">

Keeping track of table structures, its all in the name!

When developing table structures for datawarhousing, datamarts or any other purpose where you need an relational data model, it is often that we lose track of the combinations that are made. Especially when the number of tables in our database grows. To manage this problem we must pay attention naming the tables, views, cubes, etc.

First give your table the name of the project you are working on. For example: I am working on a product database project where I manage my production. I start with prefix prd_ or even better, I create a scheme called "Production". Then I describe the table purpose and the data it contains. In the productcase i'm loading data about products to my datawarehouse (DWH), so I use the following name: "Production.Product".

My attributes get similar names using short prefix which identifies the attribute.

Last I identify my foreign key and give it the name of the foreign table and then the name, so when I’m in a visual program working with the data, I still know which relations there are. Also it improves the management of the database and its applications. See the following example:

CREATE TABLE [Production].[DWH_Product](

[pk_Product_ID] [int] IDENTITY(1,1) NOT NULL,

[Product_Name] [dbo].[Name] NOT NULL,

[Product_Number] [nvarchar](25) NOT NULL,

[Product_SubcategoryID] [int] NULL,

[Product_ModifiedDate] [datetime] NOT NULL

)

CREATE TABLE [Production].[DHW_Productcategory](

[pk_ProductSubcategory_ID] [int] IDENTITY(1,1) NOT NULL,

[fk_DWH_Product_ProductID] [int] NOT NULL,

[Productcategory_Name] [dbo].[Name] NOT NULL,

[Productcategory_ModifiedDate] [datetime] NOT NULL

)

Wabit: The Ultimate BI Reporting Tool for Mac

After seeing the Microsoft video earlier to day, and knowing that it Microsoft has some pretty good BI-products, I was wondering if there where any Apple based BI-products. Maybe only as reporting, query or ETL based. So i went online and found my first toy: Wabit. I looks fun, and I’am going to test it tomorrow. I will post a review later this week about

Wabit

Wabit is an elegant and easy to use Business Intelligence Reporting tool. It offers database and business users the ability to perform Ad-Hoc Queries and generate Standard Reports.

The Wabit is designed to be the most intuitive and simplest BI Reporting tool on the market. It includes creative features such as a drag-&-drop playpen, live result-set updates, global searching, and WYSIWYG report formatting.

The Wabit also makes Standard Reporting easy. With a single mouse click, query results are instantly transformed into elegant reports that can be further customized with fonts, colours, sections, breaks, headers, footers, logos, and more.

It offers technical and business users the ability to:

• Drag-&-Drop source database tables into a playpen
• Use the database definitions to automatically formulate required joins
• Intuitively refine your queries with an “evolving” result set that updates instantly and continuously
• Many more features

http://www.macupdate.com/info.php/id/30052/wabit

http://www.sqlpower.ca/page/wabit

Steve Ballmer at Nova

I found this video on the web. It contains an interview with Steve Ballmer, the followup of Bill Gates at Microsoft. It is a very interesting video. Not really business intelligence related, but interesting, so i wanted to post it on my blog.

The weird thing is that i don't understand why leadership like this works? I like the enthusiasm, but i feel that it is a bit overacted, maybe even scary at some point. But it seems that Steve has some kind of charism and gets everybody in the audience interested.

See for your self:

The video is in english with dutch subtitles.

Maybe i'm the cold dutch-guy!

btw: the question about Bing versus google is very interesting and the comments of Steve Jobs about Microsoft.

SQL: Getting just the datepart from a datetime

A nice little trick:

If you have a datetime field which has a date and a time in it, and you ony need the time, you can use the following code to cast it to a date only value:

select cast(floor(cast(getdate() as float)) as datetime)

First the code casts the datetime into a float. Then it rounds off the float leaving just the date. Then it casts the float back to datetime.

This trick is useful for SQL 2005. SQL 2008 has a function for this issue.

SSRS: Alternate colors for grouped rows issue

I came across a bit of a problem. I want to use alternate colors for rows grouped by department. I found three methods to realize this:

1. Using a piece of code determining if the row is even or not and based on that piece of code setting the background color. (http://olsv.blogspot.com/2009/08/alternating-background-colors-for-odd.html)

2. The second one is using the following expression in the background properties: =iif(RowNumber(Nothing) Mod 2, "Green", "White"). (http://msdn.microsoft.com/en-us/library/ms157328.aspx)

3. The last one is the same as the previous one, only the expression is different: =IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2). (http://weblogs.sqlteam.com/joew/archive/2008/08/20/60691.aspx)

All methods work fine and the rows even and odd get alternate colors grouped by department....but… here is the problem: The report has an calculationfield. I sort my tablix on that field. The ordering is correct, but it colors get mixed up. They are set on the non-ordered set.

So I understand that SSRS first sets de background colors and then sorts the tablix, which leaves me with a bit of a problem.

I will post the fix when I find the solution.