Samexistens mellan QlikView och Qlik Sense med hjälp av Qlik Deployment Framework (QDF)

Publicerades: Ingen kommentar

Vi på egbs consulting har under flera års tid varit förespråkare av det centraliserade ramverket, Qlik Deployment Framework, QDF. Vill du veta mer om varför du ska arbeta med Qlik Deployment Framework kan du läsa mer här. Vid den årliga globala Qlik konferensen Qonnections 2016 var vi på plats i Orlando och höll en session om hur du kan skapa samexistens mellan QlikView och Qlik Sense med hjälp av just Qlik Deployment Framework (QDF).

Den här bloggposten ger dig en kort resume om hur du kan åstadkomma detta.

Namnlös bild

 

I vårt exempel utgår vi från en standard struktur (QDF).

 

Det vi gjort tillägg i denna standard struktur är att vi skapat en ”exekveringskripts” mapp under 3.Include. Denna mapp har vi kallat 99.Application och avser att hålla alla skriptfiler som anses som slutapplikationsskript dvs L steget i en ETL struktur. I vårt exempel anser vi också att antingen QlikView eller Sense eller i kombination sköter och ombesörjer E och T stegen i ETL processen.

 

Vårt exempel skript ser ut så här.

 

CALL LoadContainerGlobalVariables('Example2','Import');
SET vL.ImportData = $(vG.ImportPath);

DummyData:
LOAD 
Date,
Company,
Data_X,
Data_Y
FROM
[$(vL.ImportData)\Data.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

SET vL.Measure.X = 'sum(Data_X)';
 

Vi hämtar de globala importvariablerna för container Example2. Vi laddar data från en CSV fil (sparad i 8.Import). Vi avslutar skriptet med att skapa en lokal variabel för ett uttryck.

Det vi vill åstadkomma med att bryta ut exekveringskriptet för L steget är för att både QlikView och Qlik Sense ska kunna exekvera samma kod. Där får Qlik Deployment Framework en nyckel eftersom vi då kan arbeta med samma struktur som kommer fungera väl i båda produkterna. Vi kommer också döpa skriptfilen till vad QlikView Dokumentet (QVW) heter samt till den titel Qlik Sense applikationen kommer ha. I vårt exempel är detta Report XX vilket ger att skriptfilen döps till Report XX.qvs. I vårt exempel läser vi endast en CSV fil från container Example2:s import mapp (8.Import).

 

Nu har vi ett exekveringskript förberett. Nu behöer vi skapa en QVW och en QVF för att kunna köra detta i respektive produkt. Vi skapar därmed en QVW och döper den till Report XX.qvw samt skapar  en Sense app och sätter titeln till Report XX.

Initiera QDF i QlikView

Vi inleder med QlikView och initiering av QDF ramverket.

// First Base Variable Include ,Use relative path to 1.init.qvs

// Contains base searchpath in this container

LET vG.BasePath=;

// Basic search for 1.Init.qvs

$(Include=..\..\..\..\InitLink.qvs);

$(Include=..\..\..\InitLink.qvs);

$(Include=..\..\InitLink.qvs);

$(Include=..\InitLink.qvs);

EXIT SCRIPT WHEN‘$(vG.BasePath)’= ”;

Därefter ska vi exekvera vårt QVS skript. För att få detta dynamisk och återanvändbart (du kan självklart inkludera detta i ramverket för att kalla detta med hjälp av en SUB) gör vi på detta sätt. (Kom ihåg att vi döpte vårt QVS skript till det tilltänkta QVW filnamnet).

LET vL.DocName = SUBFIELD(DocumentName(), ‘.qvw’,1);

$(Include=$(vG.IncludePath)\99.Application\$(vL.DocName).qvs);

Vi börjar med att ta ut dokumentets namn utan filändelsen. Sedan kallar vi helt enkelt på detta genom vår variabel vL.DocName. Du kan självklart anropa 99.Application mappen med hjälp av en variabel från ramverket men i detta exempel blir det tydligare genom att vi endast lägger på det på vår vG.IncludePath variabel.

Initiera QDF i Qlik Sense

För Qlik Sense skiljer initieringen av ramverket sig mot hur vi gjorde i QlikView. Vi börjar med att skapa en data connection till den container vi önskar samt en till vår delade mapp 99.Shared. Denna koppling döper vi till ”Shared” respektive ”Example2″. (Ramverket behöver denna åtkomst/sökväg för att generera upp variabler för det som är delat/gemensamt.) Vi väljer sedan att sätta vår ”hem container” till vår exempel container dvs Example2.

 

SET vG.HomeContainer=’lib://Example2′;

$(Include=$(vG.HomeContainer)\InitLink.qvs);

 

När vi sedan ska kalla på vårt exekveringskript görs detta på liknande sätt i Qlik Sense som i QlikView. Skillnaden är att vi istället för att använda dokumentets namn via DocumentName() behöver använda DocumentTitle(). Varför det? Jo för DocumentName() ger endast App ID. Därför behöver vi ha titeln och därmed behöver titeln vara det samma som skriptet är döpt till samt vad QlikView dokumentet är döpt till. I vårt fall blir det Report XX.

 

LET vL.DocName = DocumentTitle();

SET vL.IncludeScript = ‘$(vG.IncludePath)\99.Application\$(vL.DocName).qvs';

$(Include=$(vL.IncludeScript));

 

Single point of truth

Med hjälp av Qlik Deployment Framework har vi nu åstadkommit en central yta som båda produkterna kan konsumera information ifrån och därmed uppnågr vi samexistens (co-existance) och single-point-of-truth.

 

Om vi gör förändringar i vårt Report XX.qvs skript kommer vi uppleva förändringarna i båda produkterna. Därför är det också bra att använda variabler för t.ex. uttryck och beräkningar centralt i ramverket för att få en källa och en sanning. I QlikView använder vi tex vL.Measure.X som uttryck. I Qlik Sense kan vi skapa ett mått där vi använder denna variabel. Självklart kan du bygga ut detta för att även hantera kommentarer (metadata) samt rubrik/namn på ditt mått. Då kan du uppdatera denna variabel som sedan slår igenom i hela din miljö, både i QlikView och Qlik Sense. Självklart kanske även vissa measure variabler ska läggas i Shared för att kunna användas globalt och inte som i vårt exempel där variabeln är satt till en lokal.

 

Joachim Boivie: LinkedIn Twitter G+

Börja använda Qlik Sense mashup API

Publicerades: Ingen kommentar

Har du lekt med tanken om hur det skulle vara att kunna blanda in objekt eller data från din Qlik-miljö i en webportal, webapplikation eller intranätsida där du även ser information från andra system? Att skapa en websida med data eller objekt från flera system kallas med ett god svengelska för en ”mashup”.  Att göra mashups är inget nytt koncept men med Qlik Sense har det blivit enklare att göra, antingen genom att gälla in befintliga objekt med iframe eller genom att använda sig av de öppna API:er som följer med Qlik Sense.

I denna blogposten har jag samlat flera bra resurser för att komma igång med web mashup-utveckling.

Jag har delat upp blogposten i tre avsnitt  (klicka för att hamna i rätt del).  Om du är helt ny så rekommenderar jag att du tar de den ordning de kommer.

(mer…)

Vegar Lie Arntsen
QlikView-konsult på egbs consulting ab
LinkedIn Twitter

Qlik REST Connector

Publicerades: Ingen kommentar

Qlik har flera konnektorer och nyligen släpptes en REST-konnektor. Den kan användas för att hämta data från webtjänster så som Google Analytics, Facebook, Twitter och LinkedIn, och fungerar till både Qlik Sense och Qlikview. Hur man ansluter till dessa fyra tjänster finns också beskrivet som exempel i Qliks userguide, http://market.qlik.com/rest-connector.html. Men konnektorn fungerar för alla rest-apier som kan returnera data i XML, CSV, eller JSON format.

Picture1

Då jag jobbar både som BI- och CRM-utvecklare har jag fått i uppdrag att bygga en CRM-applikation, som kopplar upp sig mot CRM-systemet Sugar. Sugars API returnerar JSON som default men stöder också XML, jag väljer JSON eftersom datamängden blir minst då.

Skapa en ny koppling

Koppling

Det första vi ska göra är att skapa en ny koppling. Om konnektorn är installerad syns alternativet Qlik REST Connector i valmenyn. Du får då upp rutan ”Create new connection (REST)” där du kan skapa en ny koppling. Sugar använder Oauth för login, det krävs ett separat POST-anrop för själva inloggningen (Qliks konnektor stöder POST och GET-anrop). Vi skapar alltså först en koppling med ett POST-anrop. Detta anrop returnerar en nyckel, vilken vi behöver i resterande GET-anrop.

Välj data

Välj data

Konnektorn kommer att hjälper dig att skriva load scriptet. Klicka på välj data i konnektorn i panelen till höger. I rutan som kommer upp väljer du vilket data du vill läsa in, utifrån dina val skapas sedan ett script. Konnektorn känner själv av vilken datatyp det är, i detta fall JSON, vilket också kommer synas i det genererade scriptet där det står FROM JSON.

Select Data

LIB CONNECT TO 'Login';
RestConnectorMasterTable:
SQL SELECT
"access_token"
FROM JSON (wrap on) "root";
LET varToken = '"' & Peek('access_token', 0, 'RestConnectorMasterTable') &'"';
DROP TABLE RestConnectorMasterTable;

 

Nästa steg i detta exempel är att läsa in kontakterna från CRM-systemet. Vi skapar ytterligare en koppling men denna gång med ett GET-anrop. JSON-data som returneras kommer denna gång att innnehålla en hierarki. Till exempel kan en kontakt i Sugar ha flera e-postadresser. Så här ser e-postadresserna i JSON-datat ut.

"name":"Testkontakt","email":
[{"email_address":"testkontakt@gmail.com","invalid_email":false,"opt_out":false,"primary_address":true,"reply_to_address":false},
{"email_address":"gusta.lindstrom@egbs.se","invalid_email":false,"opt_out":false,"primary_address":false,"reply_to_address":false}]

Konnektorn hjälper dig att dela upp informationen i olika tabeller när den genererar scriptet. I detta exmpel blir det en underliggande tabell med epostadresser kopplade till tabellen med kontakter.

Picture2

Skräddarsy anropet

Vid laddningen av kontaktinformation kommer jag behöva dynamiska värden i anropen. Det går att ange URL, data-parametrar och headers i scriptet. På så sätt kan jag hantera Sugars inloggning, paginering samt göra laddningen inkrementell, först sköter vi inloggningen.

För att skriva över eller lägga till parametrar eller URL:n i anropet lägger man till WITH CONNECTION i anropet. Vi lägger till variabeln med loginnyckeln.

WITH CONNECTION(
HTTPHEADER "OAuth-Token" $(varToken)
);

Ladd inkrementellt

När man laddar data från en webbtjänst genom ett api är det extra viktigt att inte ladda onödigt data. Prestandan på laddningen är inte i närheten av t ex ODBC- koppling vilket kan göra att det tar lång tid att ladda mycket data. Belastning på miljön man laddar från kan också vara ett problem eller kostnad om man betalar för laddad volym. För att minimera mängden data som laddas laddar vi bara de poster som har modifieras sedan senaste laddningen. Vi styr detta på liknande sätt som inloggning, alltså med variabler i WITH CONNECTION. Se kod nedan, syntaxen för filtret är specifik för Sugar.

QUERY ”filter[0][date_modified][$gt]” $(vLastExecTime),

Paginera laddningen

På samma sätt kan man paginera laddningen, alltså dela upp laddningen i delar istället för att ladda allt på en gång. Qliks REST-konnektor har stöd för paginering men i detta fall, med kopplingen mot Sugar, sköter jag pagineringen själv genom att loopa tills api:et returnerar next offset lika med noll (också specifikt för Sugar).

do while v_next_offset >= 0
QUERY "max_num" "100",
QUERY "offset" $(v_next_offset),
let v_next_offset = peek('next_offset',0,'RestConnector_Contacts');

Lägger vi ihop allt blir det följande tillläg I anropet.

LIB CONNECT TO 'Contacts';
...

FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(
URL "https://egbs.sugaropencloud.eu/rest/v10/Contacts",
QUERY "filter[0][date_modified][$gt]" $(vLastExecTime),
QUERY "max_num" "200",
QUERY "offset" $(v_next_offset),
HTTPHEADER "OAuth-Token" $(varToken));
let v_next_offset = peek('next_offset',0,'RestConnector_Contacts');

Obama-tabellen delat från Qlik Sense Cloud

Publicerades: Ingen kommentar

Under gårdagens presskonferens frågade den amerikanska presidenten Barrack Obama media om att ta fram en tabell som visar antal personer dödade av handvapen i USA och antal amerikaner dödade på grund av terroraktivitet. De siffrorna hittade jag lätt med hjälp av google och läste in de i Qlik Sense Cloud.

I den senaste versionen av Qlik Sense Cloud kan du både utveckla nya applikationer, ändra i befintliga applikationer, dela applikationer med andra i ditt nätverk samt dela Qlik Sense objekt med alla. Obama-grafen nedanför har jag delat genom att högerklicka på objektet inne i Qlik Sense Cloud och valt ”Dela”.  Då lyfts objektet ut till en publikt tillgänglig adress som denna: https://charts.qlikcloud.com/560e51ab5b23284e71cba683/chart.html och kan publiceras på websidor, bloggar, intranet och andra sociala plattformar på lik linje med hur jag har gjort här nedanför.

Du kan se hur man kan dela objekt genom 4m 27sek in i denna youtubefilmen:

Qlik Sense Cloud – Create and Share Updates

 

Vegar Lie Arntsen
QlikView-konsult på egbs consulting ab
LinkedIn Twitter

Self Service Data Prep with Lavastorm Analytics

Publicerades: Ingen kommentar

In this blog series we will be taking a look at the The Lavastorm Analytics Engine. Lavstorm is categorized as Self Service Data Preparation Software and is much more than an out of the box ETL tool. At a first glance it is easy to compare the product to counterparts such as SSIS or SAS Institutes DI Studio. However, when one looks a bit deeper it becomes apparent that Lavastorm is a much more powerful and flexible tool than these two. Lavastorm positions itself as an Anlaytical Platform, the plumbing behind visual front end tools like Qlik Sense, Tableau or Spotfire. Lavastorm provides the foundations and buliding blocks to build and maintain powerful data flow pipelines to transform and manipulate data before publishing the end result in a vendor specific format.

Self Service Data Preparation as a term is relatively new and is very closely associated to the Self Service BI wave that is dominating the Data Visualization market right now. Products like Qlik Sense and Power BI provide the user with excellent tools to bulid a dashboard or report which they then can distribute to both desktop and mobile devices. What these products do NOT give the user are easy to manage preparation tools from which they can easily build and maintain complex data flows. This is esentially what Self Service Data Preparation vendors are targeting, The white space between the raw data and the final visualisation.

Getting the Software

Getting the software and licence key was a breeze, as was installation. Simply go to this adress  , fill out the application and you will receive an e-post with your licence and install instructions.My philosophy when evaluating Software is first and foremost learning by doing. Great Software, for me, is Software that does not require the user to spend hours researching the product. The user interface should be intuitive, inticing and instructive. The user should be able to start using the product based on his/her prior experiences with similar Software or based on his/her area of skills.

After the, very simple, install procedure was complete i was able to start building and modelling complex lineages. Drag and drop functinality and a sparse canvas from which to work on are the main features in the Lavastorm desktop environment, the BRE. The concept of  Self Service is very apparent in the GUI design and feature choice. The Software invites you to explore the node library through visual cues placed in optimal points on the screen. The Human eye has a tendency to explore the corners of the screen, and in the bottom left hand corner you can find the nodes cleverly categorized and arranged for easy access.

nodeszoom

Whilst Self Service is the focal point for the product, advanced users are also catered for by providing interfaces to Java, Python and R. These features are accessed easily under the category Interfaces and Adapters.

interfaceszoom

Lavastorm BRE (Business Rules Engine)

The canvas area or worksheets in Lavastorm consist of graphs and libraries. Graphs are essentially the workflows in your solution wheras libraries are your global nodes. Libraries store nodes which can be configured and reused across the solution.The Library view is accessed on the top left hand side of the current worksheet. Once in the library, pre-compiled adapters can be be custom configured for reuse across your solution.

1zoom

The available libraries are visible on the left hand side of the pane. Click on Lavastorm or All to gain access to the pre-compiled nodes which you can simply drag into your own library (local library)

2zoom

Once you have dragged a node onto your Local library pane, you will be encouraged to provide a name for it. The DB Query node is a typical node that will be reused across a solution. By dragging this node into your local library pane and configuring it with the necessary credentials you will be able to reuse this node as often as you like in your solution.

3.1zoom

I was very impressed by the range and grouping of the nodes in lavastorm. The product really does enable access to a vast array of sources and formats and provides som very interesting custom transformations. What I was particulary impressed with was the specific output nodes for Qlikview, Tableau and Spotfire. With these output nodes Lavastorm puts itself in a very good position to provide backend support for these predominantly front end tools.

3zoom

Testing

A quick test of the product proved to be a very satisfactory experience. I wanted to test a common ETL scenario by importing som raw data, transforming it and loading it as structured data in the qvx format (for use in QlikView). I set up an ODBC System DSN on my machine and configured it against my SQL Server 2012 installation. I placed a DB_Qury node in my local library, configured it with the necessary authentication settings and ran it against one of my databases. The configuration windows in the DB_Query node are relatively straightfoward. The SQL query syntax is written in the SQL Query window  and the Connection window refers to the authentication settings for the node. The Optional window is what I experimented most with as it was here I could experiment with Lavastorms own scripting language BRAIN. I found BRAIN  very easy and appealing to use. The rules are few and the syntax is very straightforward. I have only spent a couple of hours with BRAIN, and there is heaps to learn, but In that short space of time I have grasped the fundamentals of the keywords: Emit, Rename, Override and Exclude.

Building a Dimension table

In the screenshot below I have used BRAIN to format the input, create a couple of hash values as checksums and omit some of the inbound fields. The Syntax for manipulating fields in BRAIN is verysimple:

FieldName.Function1().Function2().Function3().

4zoom

The Surrogate key for the table has been created with the RecordNumber() function. I have added 1 to this number as I do not want a Surrogate key of 0. The hash values have been created in 2 steps. The first step was to create a concatanation of the required values. The assigned field name for this new string was then sent into the hashcode() function to create the final checksum value. I found that I had to do this in 2 steps as the hashcode() function only accepts one argument. There may be other ways to achieve this in BRAIN with one function, if not then it would be a nice feature if the hashcode() could accept several arguments.

5zoom

The next step is to load the output into QlikView. From the output categories In the graph view, pull a QlikView output node onto the canvas and configure the output window with an appropriate filename. I configured the window for exception behavior by asking the node to raise an error if the file exits. All output in Lavastorm is clearly visible by clicking on the output indicator on each node.

6zoom

The completed data flow for the Dimension table is very simple. We read the data from the database, apply som filter logic and output the data to Qlikview (qvx) and csv.

14zoom

 Building a Fact table

After the Dimension table was created I wanted to build the fact table. Two  database reads and an inner join were required to get the data. This could of course have been achieved directly from one database read with the appropriate join syntax, but I wanted to test Lavastorms correlation nodes.

7zoom

I used the Inner Join node for my transformation and quickly found that the inbound fields are required to be sorted. Fortunatley this is easily acheiveable in Lavastorm with the radio button settings SortLeftInput and SortRightInput. It is important that both of these are set to true.

8zoom

The output rows are declared and formatted in the Output window for the inner join node.

9zoom

When loading the Fact table we want to make sure that the Business keys exist in the Dimension tables. To do this we use the Lookup function to exactly match the incoming Buisness keys to the Business keys in the the current dimension table. We repeat the lookup procedure for all Business key references in the Fact table. We can clearly see that the Lookup transformation returns 54 rows, thus exactly matching the rows in the current dimension table.

10zoom

On the General window the input keys or Lookup values are defined.

11zoom

The Output window is used to define the output from the Lookup. We exclude the referance to the matching key value in order to retain uniquness for the lookup value.

12zoom

The final step in in the Fact table data flow is to load the data. For this we use a Qlikview output node. The node is very easily configured by simply denoting a file path and an exception rule in case the file exists.

13zoom

The final Dataflow for the Fact table is slightly more complex and includes a couple of Lookups and filters before outpuuting the result to Qlikview(qvx) format.

15zoom

Viewing the final output in Qlik Sense is very straight foward. Simply create a connection to the folder where the qvx files reside, fetch the qvx files and read them into Qlik Sense.

sensezoom

Overall I spent a couple of hours on Lavastorm and was able to read a datasource, create a dimension table with change logic and create and load a Fact table. I had no prior knowledge of the product and was nonetheless able to start using it instantly and to effect. There is plenty to test and experiment with in Lavastorm and in the next post I will be looking at som of the more powerfull correlation nodes like X-ref and deep X-ref.

Links

http://www.lavastorm.com/

Fact tables in QlikView

Publicerades: Ingen kommentar

In my previous 3 blog posts I have discussed Kimball Principles in QlikView projects with a particular focus on Dimensions and how changes are managed in this type of table. In this post we will shift focus and focus on Fact tables and how these differ from Dimensions.

Fact tables are joined in a Star schema through Foreign key relationships. Wheras Dimensions contain descriptive attributes, Fact tables consist of Measurements, Metrics or Facts for a Business process. Fact tables are defined by the level of granularity for a particular Business process. Atomic grain refers to the lowest level at which data is captured by a particular Busines process. The grain of a SALES Fact table might be stated as ”Sales volume per Day per Article per Store”.The level of granularity is very important when designing Fact tables, as ultimately this level will help you to decide which Dimensions the Fact table will be connected to.

The contents of the Fact table, that is to say the measures, are usually categorized as follows:

  • Additive – Measures that can be added across any dimension.
  • Non Additive – Measures that cannot be added across any dimension.
  • Semi Additive – Measures that can be added across some dimensions.

Special care must be taken with Non additive measures, for example percentages and ratios. A general rule of thumb here is to calculate these measures in the Front end tools.

As we have seen, the Kimball toolkit is a valuable template for modelling Dimension and Fact tables. The toolkit provides us with the necessary metadata from which we can build our tables in QlikView.

1zoom

2zoom

 

Scripting the Fact table in QlikView is a simple process. We use the Metadata from the template to define our columns and apply the correct formatting. Our Foreign key relations to the dimension tables will be mapped to the Fact table with the help of mapping tables.

3zoom

 

4zoom

 

Once the fact table has been loaded it is easily viewable in QVD Viewer. The contents of the Fact Table are as we would expect. The surrogate keys (Playeridx and Dateidx) give us the relationships to the Dimensions DimPlayer and DimDate and the metrics are clearly distinguishable. The InsertAuditKey is also clearly visable, thus enabling traceability through the ETL process.

5zoom

 

In the datamodel view the Fact table sits comfortably between the two associated Dimensions.

6zoom

 

In this blog post we have discussed Fact tables, what data they contain and the importance of granularity. In the next post I will be looking at how we can build an EDW structure on the file system with help of the Qlik Deployment Framework (QDF).

Managing SCD type 2 in Qlikview

Publicerades: Ingen kommentar

In part 3 of my current series, applying Kimball principles to QlikView projects, I will be examining SCD type 2 and how we can manage these in QlikView. In my previous posts we have examined how Kimballs Dimensionall Modelling Toolkit is a useful tool during the Modelling process and how we use this tool to define our Business keys, Surrogate keys, Dimension tables and Fact tables. During the definition of our Dimension tables we even consider how values in these tables may change and to which degree we want to manage this change. My last post covered the manangement of SCD type 1 changes, where we simply overwrite the existing value and flag the field as updated with an UpdateAuditKey. In this post I will be tackling SCD type 2 Dimensions and how we mange these in QlikView.

 

SCD Type 2

 

Type 2 Dimensions differ from type 1 Dimensions through the fact that the history of change is preserved. A type 2 change does not overwrite the existing value but creates a new row for the new value. In addition, the type 2 change sets the original row to expired. Consider a typical Organisation comprised of Business Units, Departments and Functions. The Organisation keeps track of organizational transactions through Cost Centres which are recorded in the Financial applications of the Business. It is fairly common that organisational structures change over time, units move between Departments and Functions expire or become redefined. These changes are interesting for an Organisation to record as they are essential to the holistic view of the Organization. If we chose to disregard these changes then we would not be able to present a correct view of the Business over time.

 

Modelling SCD2 dimensions

 

In my first post I provided an introduction the Kimball toolkit, and how it is a useful aid when modelling Facts and Dimensions. The toolkit provides us with the necessary Metadata from which we can effectivatively build our data model. We define the SCD Type for each column in the Dimension and clearly name the columns that will be included in the SCD change logic.

1zoom

 

SCD 2 and QlikView

 

For our demo we will be using an SQL 2012 database as our source. Our database is a repository of  fictive Players, Matches and Performance. We want to see how Players develop over time and how their Positon affects their Performance. On the basis of this we have decided that the attributes Default Position and Age  will be treated as an SCD2 change.

The original databse table, without changes is as follows. The Player of interest here has ID 1. He currently plays on the left and is 9 years old.

 

2zoom

 

We have found that player 1 performs better on the right and has also recently had a birthday. We need now to make the appropriate changes to the database table.

 

3zoom

 

Hash values

 

So, how do we get QlikView to detect these changes? Firstly we need to implement some kind of comparative logic where we can detect the change. Firstly, let us consider what we are trying to achieve. We want to compare attribute values from 2 different tables. We want to compare the values for Age and Default position with the values for the same attributes in the dimension table. If these values are different then we know that one, or both of these attributes has been changed at the source.

4zoom

 

In a EDW solution SCD Logic is usually managed with checksums. CHECKSUM is a native function to SQL Server and  computes a hash value, called the checksum, over its list of arguments. QlikView does not posess a Checksum function but does include a Hash function. By using the Hash function in QlikView we can easily achieve the same result.

In QlikView, the following hash functions are available:

  • Hash128(): a 128 bit hash function that returns a 22 character string.
  • Hash160(): a 160 bit hash function that returns 27 character string.
  • Hash256(): a 256 bit hash function that returns a 43 character string.

The number of bits determines the output range of the function. A 128 bit hash can store 2^128 (or, 340.282.366.920.938.000.000.000.000.000.000.000.000) different combinations. 160 and 256 bit can store even more combinations (2^160 and 2^256 respectively).

The process for finding and reusing comparable values is relatively straightfoward. We start with an extract from the Data source. At the end of the Extract, we store the Business key and the calculated SCD2 change value (Ext_SCD2) in a csv file on disk.

 

5zoom

 

When the Dimension table is loaded we perfom the same step as during the Extract. We Load the table data, calculating a new SCD2 value (Dest_SCD2) in the load statement.

 

6zoom

 

We then store  the Business key and the SCD2 value in another csv file on disk.

 

7zoom

 

What we have achieved so far is that we have 2 comparable values. We have a Hash value for Default position and Age from the extracted data and a hash value for the same attributes from the Dimension table. We will now use these values when we read the data next time.

 

Finding SCD2 changes

 

The value for the last read hash values are now stored on disk, ready to be reused next time the data is loaded. So, how do we we reuse these values and compare them to what is currently being read? The solution for this is straightforward and involves a mapping table and a few resident loads.

The next time the data is read our first step is to read the data from source. This gives us a new SCD2 file for the extracted data. In order to find the changes, we load the data from this new file into a mapping table. Directly after that we read the Data from the SCD file generated for the Dimension table the last time it was loaded.  The next step is to map the extracted SCD2 value (SCD2_compare) into a new table, thereby giving us something to compare.

 

8zoom

 

9zoom

 

10zoom

 

We can see these changes clearly in the csv files that have been generated. This is the hash value for the extracted data with the changes.

11zoom

 

 

And this is the hash value for the same player id the last time the Dimension was loaded.

 

12zoom

 

Now that we have the hash values stored in a table it is very easy for us to find out how many rows have actually changed and the Business key for these changed rows.

 

13zoom

 

We use the variable declared at the end of the last statement as a condition to the sub call which manages the ETL of our script.

 

14zoom

 

The conditional If statement governs the direction of our dataflow by evaluating the variables passed in. The SCD2Rows sub will be called If the vL.SCD2ChangedRows > 0 and the vL.Counter variable is equal to the vL.MaxRecVersionNo variable. The 2 latter variables are used to determine the amount of rows in the existing dimension table and the rowcount in the extracted data. We expect these values to be equal when calling the SCD2 sub as we only want to manage changes, not insert new records.

 

Managing Row ID for Changed rows

 

The management of SCD2 changed rows requires that a new row be written to the dimension table in order to preserve history for this dimension and its related transactions. We also require that our Surrogate key be unique. A very simple way of achieving this is to count the rows in the existing Dimension table  data and assign this value to a Variable.

 

15zoom

 

We will use this Variable when loading the changed Data into the Dimension table.

 

16zoom

 

Loading the changed data

The first step is to Resident load the Business keys where changes have been detected.

 

17zoom

 

We then join this Data explicitly with the existing table and make the necessary changes to the ETL attributes.

 

18zoom

 

What we have now are the Original rows where changes have been detected. We have set these rows to expired with help of the RowIsCurrent and RowEndDate attributes. We now repeat the procedure, this time finding the updates for those rows that have been found. We load the Business key for the changed rows and explicitly join it to the latest extract. We ensure that the Surrogate key is kept unique with help of the vL.MaxRecVersionNo variable.

 

19zoom

 

What we have now is the changed rows and a unique Surrogate key. The final step in the process is to concatanate the updated rows with the remaining rows in the existing Dimension table. The expired rows are also concatanated onto the Dimension table, thus giving us our history.

 

20zoom

 

Once the script has executed the changes can be monitored in Qviewer. Note the changes that have been made to Player ID 1. The original row, with Playeridx 5 has been updated to inactual (0) and the new row, with Playeridx 25  is now flagged as the current row. The UpdateAuditKey for the inactual row has been recorded and the hash values in the Dest_SCD2 column clearly indicate that changes have been recorded.

 

21zoom

 

That concludes this post on Qlikview and SCD2 changes. In my next post I will be looking at Fact tables and how these differ from Dimension tables.

 

If you are new to this series, then parts 1 and 2 are available on the following links:

Part 1: Building QV Solutions with Kimball principles

Part2: Managing SCD1 logic in QlikView

Kom igång med Qlik Sense på 1-2-3-4

Publicerades: Ingen kommentar

Sommaren står inför dörren och detta är för många en lite lugnare period. Om du inte själv har semester så har kollegorna, övriga medarbetare, kunder och leverantörer det. Med ett lite lugnare tempo några veckor framöver, finns förhoppningsvis lite utrymme att hinna med sådant man skjutit framför sig ett tag. Många av våra läsare arbetar idag med QlikView och har inte inte riktigt haft tid att börja kika på årets stora nyhet Qlik Sense.

1. Qlik Sense desktop

Att ladda ned Qlik Sense Desktop är det bästa sättet för att komma igång. Du kan självklart använda ditt företags Qlik Sense server, men det finns två funktioner i desktopversionen som inte finns på servern, som underlättar i en inlärningsfas. Den första är möjligheten att konvertera QlikView-filer till Qlik Sense-format, den andra är drag-and-drop av datafiler som du vill läsa in.

2. Utforska introduktionsmaterial och skapa din första app

Qlik har producerat en mängd lättfattade filmklipp för att hjälpa dig att komma igång med Qlik Sense. Huvudfokus i dessa filmer ligger på design/layout då det är i gränssnitt-byggandet du som utvecklare först kommer se skillnaden.

Du hittar flera videointroduktioner via New to Qlik Sense Videos.

3. Hitta bra exempel för inspiration

Befintliga lösningar kan både ge dig inspiration och förbättra dig tekniskt.  Jag har lärt mig mycket genom att syna befintliga lösningar i sömmarna. De har gett mig nya idéer kring hur man kan visualisera och presentera data. Ibland har jag också lärt mig nya tekniker  för att använda objekt i applikationen. Även skriptdelen kan vara intressant för att se hur andra gör.

Qliks demo-applikationer

kpidashboardQlik har i många år haft en utmärkt demo-site för QlikView-applikationer. Under det senaste året har de även börjat bygga upp en fin samling Qlik Sense-applikationer. Utforska dem gärna: Qlik Sense Demo Hub.

Qlikshow app store

runkeeperdashboard

 

Patrick Tehubijuluw är lösningsarkitekt på Qlik i Holland och har en egen blogg med flera bra exempel där du lätt kan analysera din egen data. I skrivande stund finns personliga Qlik Sense applikationer för Twitter, Runkeeper, Skype och din dators eventlog. Du hittar mer information om dem här: App Store | Qlikshow.com

 

4. Utforska andra projekt

sankey

Qlik Sense har ett öppet API och är byggd på den senaste webbtekniken. Detta har öppnat upp möjligheter till att göra anpassningar, modifieringar och visualiseringar utöver det som Qlik själva skickar med i installationspaketen. Sådana projekt finns att hitta på olika ställen på internet, men Qlik Branch är en samlingsplats för de flesta av dem. Här kan du bl.a hitta nya visualiseringar gjorda på öppen källkod som du kan hämta hem och komplettera din Qlik Sense desktop med. Ett projekt som har fått mycket uppmärksamhet bland mina kollegor är visualiseringen D3 Sankey for Qlik Sense.

Du hittar flera Qlik Sense och QlikView-projekt här: Qlik Branch.

 

Övrigt

Qlik har släppt två e-böcker som kan vara värda att kika på. De handlar om hur du på olika sätt kan arbeta ned  BI samt hur du kan förbättra din organisations Business Discovery genom att låta användarna komplettera analyser med sitt eget data. Du hittar dem här:  Qlik Resources E-Books.

Utöver bi-effekten.se så finns många andra bra bloggar att läsa i sommar. De flesta skriver på engelska, men du hittar garanterat något intressant att läsa via askqv.com :s flöde från en samling av 37 bra Qlik-relaterade bloggar.

Lycka till med att förkovra dig i allt nytt kring Qlik Sense.  Har du själv tips på bra resurser för att komma igång så lägg gärna in en kommentar.

 

 

Vegar Lie Arntsen
QlikView-konsult på egbs consulting ab
LinkedIn Twitter

”Sense it” i nätläsaren

Publicerades: Ingen kommentar

För ett par månader sedan upptäckte jag, SenseIt, ett händigt plugin för Qlik Sense till nätläsaren Chrome. Det låter mig använda ”dra och släpp” från datatabeller som jag hittar på internet in i nya Qlik Sense-applikationer. Det är inte något jag använder varje dag, men den är smidigt att ha till hands när jag springer på intressanta dataset som  jag vill läsa och för demonstration och visa hur lätt det är att läsa in data till Qlik Sense.

Läsa in Premier League data från Wikipedia till Qlik Sense.

premierleague.wikipediaJag hittade sluttabellen för Premier League på Wikipedia-sidan  2014-15 Premier League.  Denna vill jag läsa in i en Qlik Sense applikation.

premierleague.wikipedia.senseit

Om du har installerat SenseIt till din browser så kommer du se ett Qlik-grönt stapeldiagram till höger om adressfältet i din nätläsare.  Klicka på ikonet medan du är på sidan med tabellen. Nu dyker en popup-ruta fram och du kan klicka och dra tabeller från websidan in denna.

premierleague.wikipedia.senseit.import

Den valda tabellen dyker upp i en förehandsvisning där det även är möjligt att göra några förehandsjusteringar av importen. Det är justeringar för rubrik-sättning, decimalseparator, tusentalsavgränsare och om tabellen skall transponeras eller inte vid importen.

Klicka på ”Create App” knappen för att skapa applikationen. Merk att du måste ha Qlik Sense desktop igång om du inte använder en serverinstallation.

Klicka på  ”Open App” knappen för att öppna applikationen.

Voila! Qlik Sense applikationen är skapat och laddat med tabelldatan. Nu är det bara att börja designa och analysera datan.

premierleague.wikipedia.senseit.result

SenseIt är skapat för Google Chrome, men jag har även lyckat installera den i min Opera-browser utan problem. För att få till det använde jag mig av ”Download Chrome Extension”. SenseIt är också tillgänglig som ett öppet källkodsprojekt på Qlik Branch.

 

Vegar Lie Arntsen
QlikView-konsult på egbs consulting ab
LinkedIn Twitter

Managing SCD1 logic in QlikView

Publicerades: 3 kommentarer

In my previous post we discussed Kimballs dimensional modelling and how It provides a structure and methodology to the Qlikview data modelling process. Understanding the Kimball model is relatively straightforward, but requires the QlikView developer to be well versed in such topics as Star schemes, Slowly changing dimensions, dimensions, facts and audit keys. In this bloggpost I will be discussing Slowly changing Dimensions Type 1, which are the simplest SCD type and certainly the most common.

Before we delve into SCD logic, and how we implement this in Qlikview I will take a quick look at New Rows and how these are managed.

Consider first a source table, which we use to populate our dimensions. Before a new row is added to the database we see that we have 19 rows

image1zoom

We add a new row

image2zoom

When we run the extract job the changes can clearly be seen in the qvd viewer (qvd viewer is not part of the default QlikView installation but can be fetched free of charge here)

image3zoom

When loading the dimension table we first need to establish any differences between the source table and the rows in the Dimension table. We will use our Business key (ID in the extract table) to find these changes. The Business Key will have been defined in the modelling stage and is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules. We name our business keys with the convention BK<keyname>.

A simple QlikView script will find the rowcount of the dimension table in its current state. We will assign the counted value to a local variable vL.MaxRecVersionNo for later use. Note that the variable name prefixes follow the QDF naming convention. I will cover QDF in detail in a later post, for now it is sufficient to say that vG prefix denotes a global variable wheras vL denotes a local variable.

image 4zoom

A similar script will give us the rowcount of the extracted data. Again, the counted value will be stored in a variable vL.Counter

image 5zoom

We now have 2 assigned variables vL.Counter and vL.MaxRecVersionNo. We will use these variables in the conditional syntax for the sub calls. If the counter variable is greater than the total rows of the actual dimension then the sub will be called.

image 6zoom

Appending an existing Qvd file is relatively straightforward. The source data is loaded and formatted in accordance with the metadata from the modelling process. Observe that we use the variable vL.MaxRecVersionNo to filter the rows that are new.

image 7pngzoom

The next step when appending rows to an existing Qvd is to simply concatanate the existing data onto the new data.

image 8zoom

Slowly changing dimensions (SCD)

A common scenario in EDW (Enterprise Datawarehouse ) environments is how changes to Dimension tables are managed. There are a number of definitions for managing SCD:s, referred to as Types ranging from 0-7. We will not cover all of these Types but will focus on the most common, Type 1 and 2. Type 2 will be covered in another post. For a detailed account of SCD:s please read here

In esscence the SCD  logic refers to the capturing of slow and unpedictable changes to our dimensions. Consider the following scenario: you have a patient database with all records of your patients. Your patient data is associated with the patient journal. When the data in your patient register changes, for example adress, postal area, name you will most likely want to record thses changes in order to preserve a history of your patients. This is what is called Slowly Changing dimesnions, and is a challenge for all BI vendors.

In my data model I have defined a Dimension table with the name Dim_Player. During my modelling stage I established that I needed to monitor changes to players address. I did not need to preserve history for any addresses, but wanted to know that a change had taken place. This type of change is called an SCD Type 1 and is possibly one of the most commen SCD cases.

In the Qvd viewer we can see the current state of the dimension table. Assume that we are going to change the adress details for Player 19. We can see that the current adress value for this player is Hemvägen.

image 10zoom

We now make the changes to the source table, changing the Adress field to Granitvägen.

image 11zoom

After we have extracted the data we can see that the new adress for Player 19 has been imported.

image 12zoom

The question now is how are these changes incorporated into the dimension table? Well, the main part of the solution is the Qlikview Hash function. We use this function first when we extract the raw data by applying the hash function to the columns we want to check for changes.

image 13zoom

If we check the contents of the csv file we can easily see the values that have been generated for the hash column, in this case Ext_SCD1.

image 14zoom

When we loaded the dimension table a sinilar SCD file was generated, to keep track of the curent SCD values in the dimension table. We will now compare the hash values of these 2 files to find any changes. First we create a mapping table, using the SCD file generated from the extract.

image 15zoom

We then load the SCD file created from the current dimension table.

image 16zoom

A quick review of the Dimension table SCD file, The column has been named Dest_SCD1, reveals that player ID 19 has been changed and as such we will expect to see a change in this row when we reload the dimension.

image 17zoom

We now map the 2 scd tables with help of the business key

image 18pngzoom

In order to manange the changes correctly we will compare the 2 SCD values and mark them accordingly.

image 19zoom

We find a count for the changed rows which will be used in the conditional syntax for calling the SCD1 load sub

image 20zoom

The value of vL.ChangedRows is passed as a condition into the SCD1 subroutine. If the value is > 0 then the sub will be called.

We load the Business key and the SCD value for the changed row.

image 21 zoom

We use an inner join on the extracted data to find the exact row that has been changed. This is the only row that will be loaded.

image 22zoom

The next step is to recreate the dimension table from the resident table just created. We hardcode a value for the RowChangeReason column.

image 23zoom

After loading we can clearly see the changes that have been made. The audit keys help us to keep track of the changes.

image 24zoom

Audit keys

Tracking changes in a Data Warehouse are a common practice and give us the ability to easily see what was changed and when. We use 3 types of Auditkey to track the main processes during the ETL stage, extrct, insert and update. The only requirement for the AuditKey is that it is unique for each time the job is run. A simple way of achieving this is by retreiving the time from the Now() function.

image 25zoom

This concludes my second post about QlikView and Kimballs Dimensional Modelling. In the next post I will be looking at SCD2 and how these dimension types are managed in QlikView.