It is enormously useful to be able to quickly generate test data so that we can create QlikView applications and test different aspects of development and discover how different development methods work. By creating our own set of data, we can abstract problems away from the business issues that we are trying to solve because the data is not connected to those problems. Instead, we can resolve the technical issue underlying the business issue. Once we have resolved that issue, we will have built an understanding that allows us to more quickly resolve the real problems with the business data.
We might contemplate that if we are developers who only have access to a certain dataset, then we will only learn to solve the issues in that dataset. For true mastery, we need to be able to solve issues in many different scenarios, and the only way that we can do that is to generate our own test data to do that with.
Dimension tables will generally have lower numbers of records; there are a number of websites online that will generate this type of data for you.
For quite a while, I used http://www.generatedata.com to generate random data such as company names, and so on. However, in a recent blog entry by Barry Harmsen (Barry is the co-author of QlikView 11 for Developers, Packt Publishing) at http://www.qlikfix.com, he mentioned http://www.mockaroo.com as a resource for generating such tables.
The following screenshot demonstrates setting up a Customer extract in Mockaroo:
This allows us to create 1,000 customer records that we can include in our QlikView data model. The extract is in the CSV format, so it is quite straightforward to load into QlikView.
While we might often abdicate the creation of test dimension tables to a third-party website like this, we should always try and generate the Fact table data ourselves.
A good way to do this is to simply generate rows with a combination of the AutoGenerate() and Rand() functions.
For even more advanced use cases, we can look at using statistical functions such as NORMINV to generate normal distributions. There is a good article on this written by Henric Cronström on Qlik Design Blog at http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/26/monte-carlo-methods.
We should be aware of the AutoGenerate() function that will just simply generate empty rows of data. We can also use the Rand() function to generate a random number between 0 and 1 (it works both in charts and in the script). We can then multiply this value by another number to get various ranges of values.
In the following example, we load a previously generated set of dimension tables—Customer, Product, and Employee. We then generate a number of order header and line rows based on these dimensions, using random dates in a specified range.
First, we will load the Product table and derive a couple of mapping tables:
// Load my auto generated dimension files
Product:
LOAD ProductID,
Product,
CategoryID,
SupplierID,
Money#(CostPrice, '$#,##0.00', '.', ',') As CostPrice,
Money#(SalesPrice, '$#,##0.00', '.', ',') As SalesPrice
FROM
Products.txt
(txt, utf8, embedded labels, delimiter is '\t', msq);
Product_Cost_Map:
Mapping Load
ProductID,
Num(CostPrice)
Resident Product;
Product_Price_Map:
Mapping Load
ProductID,
Num(SalesPrice)
Resident Product;Now load the other dimension tables:
Customer:
LOAD CustomerID,
Customer,
City,
Country,
Region,
Longitude,
Latitude,
Geocoordinates
FROM
Customers.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Employee:
LOAD EmployeeID,
Employee,
Grade,
SalesUnit
FROM
Employees.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
Where Match(Grade, 0, 1, 2, 3); // Sales peopleWe will store the record counts from each table in variables:
// Count the ID records in each table
Let vCustCount=FieldValueCount('CustomerID');
Let vProdCount=FieldValueCount('ProductID');
Let vEmpCount=FieldValueCount('EmployeeID');We now generate some date ranges to use in the data calculation algorithm:
// Work out the days Let vStartYear=2009; // Arbitrary - change if wanted Let vEndYear=Year(Now()); // Generate up to date data // Starting the date in April to allow // offset year testing Let vStartDate=Floor(MakeDate($(vStartYear),4,1)); Let vEndDate=Floor(MakeDate($(vEndYear),3,31)); Let vNumDays=vEndDate-vStartDate+1;
Tip
Downloading the example code
You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Run a number of iterations to generate data. By editing the number of iterations, we can increase or decrease the amount of data generated:
// Create a loop of 10000 iterations
For i=1 to 10000
// "A" type records are for any date/time
// Grab a random employee and customer
Let vRnd = Floor(Rand() * $(vEmpCount));
Let vEID = Peek('EmployeeID', $(vRnd), 'Employee');
Let vRnd = Floor(Rand() * $(vCustCount));
Let vCID = Peek('CustomerID', $(vRnd), 'Customer');
// Create a date for any Time of Day 9-5
Let vOrderDate = $(vStartDate) + Floor(Rand() * $(vNumDays)) + ((9/24) + (Rand()/3));
// Calculate a random freight amount
Let vFreight = Round(Rand() * 100, 0.01);
// Create the header record
OrderHeader:
Load
'A' & $(i) As OrderID,
$(vOrderDate) As OrderDate,
$(vCID) As CustomerID,
$(vEID) As EmployeeID,
$(vFreight) As Freight
AutoGenerate(1);
// Generate Order Lines
// This factor allows us to generate a different number of
// lines depending on the day of the week
Let vWeekDay = Num(WeekDay($(vOrderDate)));
Let vDateFactor = Pow(2,$(vWeekDay))*(1-(Year(Now())-Year($(vOrderDate)))*0.05);
// Calculate the random number of lines
Let vPCount = Floor(Rand() * $(vDateFactor)) + 1;
For L=1 to $(vPCount)
// Calculate random values
Let vQty = Floor(Rand() * (50+$(vDateFactor))) + 1;
Let vRnd = Floor(Rand() * $(vProdCount));
Let vPID = Peek('ProductID', $(vRnd), 'Product');
Let vCost = ApplyMap('Product_Cost_Map', $(vPID), 1);
Let vPrice = ApplyMap('Product_Price_Map', $(vPID), 1);
OrderLine:
Load
'A' & $(i) As OrderID,
$(L) As LineNo,
$(vPID) As ProductID,
$(vQty) As Quantity,
$(vPrice) As SalesPrice,
$(vCost) As SalesCost,
$(vQty)*$(vPrice) As LineValue,
$(vQty)*$(vCost) As LineCost
AutoGenerate(1);
Next
// "B" type records are for summer peak
// Summer Peak - Generate additional records for summer
// months to simulate a peak trading period
Let vY = Year($(vOrderDate));
Let vM = Floor(Rand()*2)+7;
Let vD = Day($(vOrderDate));
Let vOrderDate = Floor(MakeDate($(vY),$(vM),$(vD))) + ((9/24) + (Rand()/3));
if Rand() > 0.8 Then
// Grab a random employee and customer
Let vRnd = Floor(Rand() * $(vEmpCount));
Let vEID = Peek('EmployeeID', $(vRnd), 'Employee');
Let vRnd = Floor(Rand() * $(vCustCount));
Let vCID = Peek('CustomerID', $(vRnd), 'Customer');
// Calculate a random freight amount
Let vFreight = Round(Rand() * 100, 0.01);
// Create the header record
OrderHeader:
Load
'B' & $(i) As OrderID,
$(vOrderDate) As OrderDate,
$(vCID) As CustomerID,
$(vEID) As EmployeeID,
$(vFreight) As Freight
AutoGenerate(1);
// Generate Order Lines
// This factor allows us to generate a different number of
// lines depending on the day of the week
Let vWeekDay = Num(WeekDay($(vOrderDate)));
Let vDateFactor = Pow(2,$(vWeekDay))*(1-(Year(Now())-Year($(vOrderDate)))*0.05);
// Calculate the random number of lines
Let vPCount = Floor(Rand() * $(vDateFactor)) + 1;
For L=1 to $(vPCount)
// Calculate random values
Let vQty = Floor(Rand() * (50+$(vDateFactor))) + 1;
Let vRnd = Floor(Rand() * $(vProdCount));
Let vPID = Peek('ProductID', $(vRnd), 'Product');
Let vCost = ApplyMap('Product_Cost_Map', $(vPID), 1);
Let vPrice = ApplyMap('Product_Price_Map', $(vPID), 1);
OrderLine:
Load
'B' & $(i) As OrderID,
$(L) As LineNo,
$(vPID) As ProductID,
$(vQty) As Quantity,
$(vPrice) As SalesPrice,
$(vCost) As SalesCost,
$(vQty)*$(vPrice) As LineValue,
$(vQty)*$(vCost) As LineCost
AutoGenerate(1);
Next
End if
Next
// Store the Generated Data to QVD
Store OrderHeader into OrderHeader.qvd;
Store OrderLine into OrderLine.qvd;Note
Barry Harmsen, co-author of QlikView 11 for Developers, Packt Publishing, recommends a slightly different method for generating seasonal variation. By using the Sin() or Cos() functions to generate a table containing the number of records to generate for each day, we can loop across this table and use these values to auto-generate rows for the fact table.