The trouble with data…

When using the IE Plugin to browse documents in Access Point, users can set their user preferences from the browser. These preferences include being able to export the “current selections” acting upon the object being exported – a very common requirement.

In the Ajax environment, no such browser based ability to adjust these settings exist, but you can achieve the same result by doing the following:

• Stop the service “QlikView Server”
• Open the file settings.ini (located in C:\ProgramData\QlikTech\QlikViewServer – Win2008+ I believe Server OS)
• Add the parameter SelectionStampInBIFFExport=1 under the section labeled [Settings 7]

.

• Save the file

• Restart the service “QlikView Server”

You should be now all sorted. Hopefully in future versions this will be easier to change.

Have been playing around a little more making my time dimension creation and population a little more efficient

Let’s say we have a dimension table created like so


CREATE TABLE [dbo].[CALENDAR](
[CAL_DATE] [datetime] NULL,
[CAL_DAY] [varchar](50) NULL,
[CAL_DAY_IN_WEEK] [int] NULL,
[CAL_DAY_IN_MONTH] [int] NULL,
[CAL_DAY_OF_YEAR] [int] NULL,
[CAL_WEEK] [int] NULL,
[CAL_MONTH] [varchar](50) NULL,
[CAL_SHORT_MONTH] [varchar](50) NULL,
[CAL_MONTH_IN_YEAR] [int] NULL,
[CAL_QUARTER] [int] NULL,
[CAL_HALF_YEAR] [int] NULL,
[CAL_YEAR] [int] NULL,
[IS_WEEKEND] [int] NULL,
[WORKING_DAY_NO] [int] NULL
) ON [PRIMARY]
GO

We can then use a CTE to generate a list of data for this table efficiently (well, more efficiently than a loop)

Example: (generates data for 1 Jan 2010 to 31 Dec 2012).


WITH mycte as
(
select cast('2010-01-01' as datetime) DateValue,
case when datepart(dw, cast('2010-01-01' as datetime) -1) < 6 then 0 else 1 end IS_WEEKEND

union all
select DateValue + 1,
case when datepart(dw, DateValue) < 6 then 0 else 1 end IS_WEEKEND
from mycte
where DateValue + 1 < '2012-12-31'
)
select DateValue as CAL_DATE
, datename(dw, DateValue) CAL_DAY
, datepart(dw, DateValue-1) CAL_DAY_IN_WEEK
, datepart(dd, DateValue) CAL_DAY_IN_MONTH
, datepart(dy, DateValue) CAL_DAY_OF_YEAR
, datepart(ww, DateValue) CAL_WEEK
, datename(mm, DateValue) CAL_MONTH
, substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH
, datepart(mm, DateValue) CAL_MONTH_IN_YEAR
, datepart(qq, DateValue) CAL_QUARTER
, case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR
, datepart(yy, DateValue) CAL_YEAR
, IS_WEEKEND
, (SELECT sum(IS_WEEKEND) FROM mycte
WHERE
DateValue and datepart(yy, DateValue) = datepart(yy, m.DateValue)
and datename(mm, DateValue) = datename(mm, m.DateValue)
) AS WORKING_DAY_NO
FROM mycte m
OPTION (MAXRECURSION 0)

So, to make this more efficient, how about wrapping this up in a stored procedure so we can just pass the start and end dates we need.


-- ==================================================================================
-- Author: Will Riley (www.wills-blog.com | Twitter @troublewithdata)
-- Create date: 2012-03-27
-- Description: Populate a date dimension table using a CTE based stored procedure
--
-- Note: Use it like so:-
--
-- INSERT INTO [dbo].[CALENDAR]
-- execute MyDateGenerator '2010-01-01','2011-12-31'
--
-- You also need to create the calendar table
-- ==================================================================================

USE [TestDW]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N’[dbo].[MyDateGenerator]‘) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
BEGIN
DROP PROCEDURE [dbo].[MyDateGenerator]
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MyDateGenerator]
@FromDate datetime, @ToDate datetime

AS BEGIN

SET NOCOUNT ON;

WITH mycte as
(
select cast(@FromDate as datetime) DateValue,
case when datepart(dw, cast(@FromDate as datetime) -1) < 6 then 0 else 1 end IS_WEEKEND
union all
select DateValue + 1,
case when datepart(dw, DateValue) < 6 then 0 else 1 end IS_WEEKEND
from mycte
where DateValue + 1 < @ToDate
)
select DateValue as CAL_DATE
, datename(dw, DateValue) CAL_DAY
, datepart(dw, DateValue-1) CAL_DAY_IN_WEEK
, datepart(dd, DateValue) CAL_DAY_IN_MONTH
, datepart(dy, DateValue) CAL_DAY_OF_YEAR
, datepart(ww, DateValue) CAL_WEEK
, datename(mm, DateValue) CAL_MONTH
, substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH
, datepart(mm, DateValue) CAL_MONTH_IN_YEAR
, datepart(qq, DateValue) CAL_QUARTER
, case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR
, datepart(yy, DateValue) CAL_YEAR
, IS_WEEKEND
, (SELECT sum(IS_WEEKEND) FROM mycte
WHERE
DateValue and datepart(yy, DateValue) = datepart(yy, m.DateValue)
and datename(mm, DateValue) = datename(mm, m.DateValue)
) AS WORKING_DAY_NO

FROM mycte m

OPTION (MAXRECURSION 0)
END
GO

Now all we need to do is to run it like so:


INSERT INTO [dbo].[CALENDAR]
execute MyDateGenerator '2010-01-01','2011-12-31'


I could imagine this being useful within an incremental SSIS package to add a new “chunk” of data to your time dimension…

I know that Microsoft have promised that they will be playing catch up later this year in terms of Mobile BI, and for sure they need to. Offerings available right now from the likes of Cognos, QlikView, Business Objects and Microstrategy (current personal favourite) far outstrip what you can do with the current Microsoft stack. But what does work?

Well, from what I can see, anything Sliverlight based is out, so that rules out PowerView and Decomp Trees in PPS. It seems that most of the other things work though, so there’s much you can still do.

Having managed to blag a company iPad in my new role as Reporting & Analytics lead, I figured I’d hook it up to the MSFT 2008R2 demo server I built that currently hosts some of Logica’s Spark Centre demos. Having installed Junos Pulse, a VPN app that allows me to securely connect to my work network, I found that the Sharepoint “pretend Telco” site renders quite well on the iPad.

Firstly, I checked out SSRS… There’s no right click on iPad so you need to hit the screen over any drop downs and wait a moment for the selections to pop up.

Looks pretty good… Next was PPS – remember, no Silverlight and doesn’t look like drill downs are fully working, but still able to do things like selecting chart items, changing from chart to grid and most impressively, export to Excel and PowerPoint works just fine (providing you have an Office programme installed such as QuickOffice, Docs to Go etc). Click Export to Excel and you get a choice..

And here’s the report in QuickOffice

Last thing to try was Excel services. Here, the Open in Excel function does not work. Apparently, there is no fooling it in to accepting being opened in a cheap substitute but the charts look OK…

So, not perfect, but not all despair, and I’m assured that there are lots of goodies to come later in the year once the SQL2012 launch is out of the way. Still, it will need to be good to match my current favourites… If you get the chance, have a look at the Microstrategy iPad app….

And the nice app from RoamBI

When you are trying to connect to MDS from Excel, if you get the following error

“The Master Data Manager Web Application is Older than the Add-in Version”

while using MDS excel addin, then one possible reason is that the connection for the MDS excel addin does not like the full address i.e.

http://severname/default.aspx

Houston Neal of Software Advice has written a nice article to introduce BI to newcomers to the subject.

If the words “extract transform load” sound like a foreign language, you’re in the right place. Making sense of the business intelligence (BI) software market can be challenging for even the most technical of software buyers. It’s complex stuff.

You can read the full article here

Having been working with QlikView for some time now, I thought it was worth writing a few posts about some of the slightly more interesting things we have been doing with it.

One of the requirements of a recent project was to develop a way of allowing users to:

• reload applications without being Document Administrators (and therefore having limited QEMC access)
• supply external parameters to the reload task (effectively specifying filters to limit data in a database extraction)
• specify security groups that would have access to the loaded application (or reload for “All Authenticated Users”)

The final result was a web application (screenshot below), running in IIS, that allowed user to do just that. It uses the QV Publisher API and was written in ASP.Net 4 using Visual Studio 2010.

What the web application does is to search the root QV documents folder and display those that already have a reload task associated with them. Documents with no tasks will not be displayed in the “documents” grid. It also recognises the User’s Active Directory credentials and searches the relevant LDAP to look for memberships of Security Groups containing the word “QlikView”.

Within our app, there was a requirement to allow the user to enter externally supplied parameters to the reload task, but this is purely optional – the application will work without them being supplied, due to flags we place in our QV script.

Effectively, what the code does in the background is to create a clone of the existing reload task that is attached to the document, replacing any existing supplied values with those supplied from the web form, run the reload task, and then delete the “clone”. It would be better if the code created a task from scratch, but we haven’t quite managed to to that yet with the QV Publisher API.

If people are interested in this application, we may well be able to package it up, make it a little more generic and release the source code.

Having spent the early part of the week at Qonnections in Miami, one of a number of very cool things I saw was this

Qlikview iPad2 App

It seems that everyone was talking mobile BI, and this is one of the better incarnations i have yet seen.