Microsoft Excel & AFIN.NET - Self-Service Business Intelligence

An analyst works in Excel - It's obvious. Excel means: workbooks, sheets, cells, functions, custom formulas, formats, charts and much more: ELASTICITY.

AFIN.NET offers (adds to Excel functionality without limiting it) a basic function: GETDATA() function.

GETDATA() is:

simply to use

powerful

universal

quickly implemented

very quickly calculated

CONNECTED TO ANY DATA SOURCE

NORMAL EXCEL FUNCTION

GETDATA() is SIMPLE TO USE:

Top

If you want to enter GETDATA() - just click the AFIN.NET Function Wizard (or the shortcut [F3]) and choose a businness dimension you want to use.

If you want to parametrize the created formula - use the AFIN.NET Parametrization Wizard (e.g. by the shortcut [Ctrl+F2])

If you want to know, what and how is being calculated, just look at the function arguments.

This is a very basic sample:

WEBCAST: How to enter a function?

429 546 165,90

=GETDATA("Financials.2.Access";"1%")

WEBCAST: How to parametrize text in formulas and functions?

ARTICLE: How to build your first simple report?

ARTICLE: How to parametrize text in formulas and functions?

What does it mean?

There are only two arguments in this case:

1. The dimension name (What business value do we want to get? In this case: Sum of account records, specified by custom query - here: account numbers starting with '1')

2. The text of the query, here is: '1%' (Take any account records with '1' and any characters to the right - normal SQL syntax)

Just look at it's definition:

[Measure Financials.2.Access]

About

=Financial data - a large account register (Access table)

Type

=ODBC

AddString

ConnectionText

=Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.folder]\Samples\Data\ODBC\FK2Access\AccessFinancials.mdb

QueryText

=SELECT SUM(kwotatr) AS Kwota FROM T1_TRANS WHERE kontotr LIKE '{AccountNumber}'

GetDataArguments

={AccountNumber}

SampleFunction

=GETDATA("Financials.2.Access";"1%")

It's definition is simple to create using one of several methods. Look at the chapter 'Create a new data source'

GETDATA() is POWERFUL:

Top

If you want to get data from ANY DATA SOURCE...

If you want to get data QUICKLY without demanding any IT reporting process...

If you want to get data DIRECTLY into a cell, with the possibility of calculating it at any moment, automatically or on demand..

This is a very strong sample:

WEBCAST: How to enter a function?

32 346,77

=GETDATA("Financials.3.Cub";"sw/401";"2007";"01")

ARTICLE: AFIN.NET Financial Query Language (AFQL) (PL)

What does it mean?

There are several arguments in this case:

1. The dimension name (here: Financial program, complicated query, specified in the AFIN.NET Financial Query Language for any account combinations) - OLAP Cube

2. The text of this query in AFQL

3. The year in time dimension

4. The month in time dimension

Just look at it's definition:

[Measure Financials.3.Cub]

About

=Every financial program (data converted) - OLAP, with AFQL

Type

=OLAP2

AddString

=[Suma

ConnectionText

=[AFIN.NET.folder]\Samples\Data\OLAP\FK3olap\stany.cub,OCWCube,OCWCube

QueryText

={AFQL},[rok].[{Year}],[mc].[{Month}]

GetDataArguments

={AFQL},{Year},{Month}

SampleFunction

=GETDATA("Financials.3.Cub";"sw/401";"2007";"01")

Arg2

=WITH MEMBER measures.zero AS '[Rok].currentmember.name' SELECT {[Rok].children} ON 0, {{zero}} ON 1 FROM [OCWCube]

Arg3

=WITH MEMBER measures.zero AS '[Mc].currentmember.name' SELECT {[Mc].children} ON 0, {{zero}} ON 1 FROM [OCWCube]

It's not the simpliest sample but possible to create. And very, very, very(!) simply to use.

AFQL is specified in the article. The OLAP cube has to be very specially designed (See articles). Processing the cubes is done automatically.

Other samples of GETDATA(). These samples are predefined in Sample Data Model, included in the AFIN.NET package.

234,00

=GETDATA("Budget";"2006";"01";"001";"A";"poz1")

1 111,00

=GETDATA("Budget.2";"1";"W1";"PB1";"2008#01")

10 601,00

=GETDATA("Planning";"2008";"01";"01";"01";"1";"poz1")

111,00

=GETDATA("Excel.Link";"D:\AFIN\AFIN.NET\Samples\Data\Excel\LinkFunction";"Zeszyt1.xls";"Arkusz1";"A1")

Dane firmy A z 2008.01

=GETDATA("Excel.Link.Model";"A";"2008.01";"Zeszyt1.xls";"Arkusz1";"A1")

140,00

=GETDATA("Sales.Access";"A%";"'=2004";"'=01")

140,00

=GETDATA("Sales.Excel";"A%";"'=2004";"'=01")

140,00

=GETDATA("Sales.OLAP";"netto";"AREX")

929 402,31

=GETDATA("Financials.1.DBF.A";"sw";"1";"2007";"08")

929 402,31

=GETDATA("Financials.1.DBF.B";"sw/1";"2007";"08")

429 546 165,90

=GETDATA("Financials.2.Access";"1%")

32 346,77

=GETDATA("Financials.3.Cub";"sw/401";"2007";"01")

GETDATA() is UNIVERSAL:

Top

You can design YOUR REPORT IN A VERY CUSTOMIZED WAY...

You are able to design all the function arguments as fixed OR AS CUSTOMIZED.

This sample shows elasticity of queries set in GETDATA(). Your report has to be designed in your own customized way, taking values in the order you want.
If you prepared a dimension (here: 'Sales.Access') definition as a customized one (storing the operators in function arguments = not including them in dimension settings, here: {Year}, {Month}) you could set all the queries you want. And as parametrizable as in your Excel.
In comparition: the argument {Client} is set as a fixed one - the operator 'LIKE' is set in a definition not in a function argument.

Year /
Month
(All the clients)

=2004

=2005

>2004

<2005

BETWEEN '2004' AND '2005'

(...)

373,70

77,40

373,70

451,10

0,00

181,00

30,70

181,00

211,70

140,00

5,20

140,00

145,20

IN (1,3,5,7)

306,00

26,90

306,00

332,90

(…)

in B2:

373,70

=GETDATA("Sales.Access";"%";B$1;$A2)

Just look at it's definition:

[Measure Sales.Access]

About

=Local data (ODBC) - version A. (Access)

Type

=ODBC

AddString

=.Sales

ConnectionText

=Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.folder]\Samples\Data\ODBC\SalesInAccess.mdb

QueryText

=SELECT SUM(wart_net) AS Sales FROM faktura
WHERE nazwa LIKE '{Client}' AND YEAR(data) {Year} AND MONTH(data) {Month}

GetDataArguments

={Client},{Year},{Month}

SampleFunction

=GETDATA("Sales.Access";"A%";"=2004";"=01")

Arg1

=SELECT nazwa FROM odbiorca WHERE nazwa LIKE '%'

Arg2

=SELECT DISTINCT '='&YEAR(data) FROM faktura

Arg3

=01,=02,=03,=04,=05,=06,=07,=08,=09,=10,=11,=12,LIKE '%',BETWEEN 01 AND 03,BETWEEN 04 AND 06

What does it mean?

GETDATA() has no limit in increasing its elasticity. The only limit is your convenience. You decide if it should be fixed (faster defining) or customizable (elastic use).

Customizing requires having a basic knowledge about SQL. Your IT specialist can help you.

GETDATA() is QUICKLY IMPLEMENTED:

Top

If you want to GET YOUR REPORT IMMEDIATELY...

If you want to get data parametrizing a function AS A NORMAL EXCEL FORMULA…

If you don't know what couple of parameters gets data you intend to place into your report..

This is a sample of a small report, created in AFIN.NET. This is ONE FUNCTION, parametrized with column and row labels, not taking values from the list of Pivot Table or any other given list from the system BUT ACCORDING TO YOUR NEEDS. Remember: Reports are created, based on your needs (or external given projects) but not on the list of existing values in the system (e.g. any financial statement)

2007

(...)

sw/401

32 346,77

70 110,10

116 062,77

157 907,01

202 785,74

sw/402

1 744,48

8 279,35

11 137,42

16 668,41

18 522,70

sw/403

39 731,20

65 721,42

96 081,42

102 101,42

114 017,02

sw/404

86 470,22

286 658,14

473 910,71

574 956,72

680 852,85

sw/405

33 943,46

90 718,87

141 347,66

190 127,21

228 196,23

sw/406

229 388,67

750 525,89

1 007 849,31

1 270 996,40

1 551 348,63

sw/407

71 756,37

213 671,23

304 588,12

389 165,79

528 788,04

sw/408

80 135,98

157 778,14

242 577,13

333 936,89

419 625,67

sw/409

13 623,78

26 609,31

46 825,87

61 084,00

81 138,64

sw/410

0,00

(…)

in B2:

32 346,77

=GETDATA("Financials.3.Cub";$A2;$A$1;B$1)

What does it mean?

You enter only ONE FUNCTION.

You can perform copy/cut/paste/paste_special and all the other Excel operations.

You can change all values and formulas in the sheet.

You can format anything as you want in a well-known way.

You are still in Excel. And you know it! Want a chart? No problem!

You are the boss. Not a part of the system.

GETDATA() is VERY QUICKLY CALCULATED:

Top

If you want to GET DATA IMMEDIATELY...

If you want to WORK ON-LINE…

The report, created above calculates in less than a half of a second (50 independant values from an external data source or different data sources)

What does it mean?

Your reports are being refreshed immediately corresponding to normal Excel calculation rules.

If you don't want automatic calculation, you can set it into 'on demand' calculation (We strongly recommend using this option!)

But, if it still does not satisfy you…

…YOU CAN INCREASE THE SPEED OF CALCULATIONS ABOUT 10 TIMES! (By using 'Archive' feature, described below.)

GETDATA() gets data from ALL THE DATA SOURCES:

Top

If you want to GET DATA from...

Your worsheets with thousands of links…

Hundreds of files in one report (or databases, too) … Your budgeting system is in hundreds of Excel files!

Access databases, Excel workbooks, SQL Server, SQL Server Express, Oracle, MySQL via the internet, DB2, all ODBC formats…

The only restriction: you have to know where to get the data from. Your IT specialist can give you parameters of the connection (connection string)

ADO, DAO, ODBC, OLAP Services supported, Web Services.

No one else has:

Excel workbooks are connectable on the Share Point Portal Server, by remote, too

PARAMETRIZABLE connectionstrings and query texts = parameters in Excel, data from anywhere

Excel is a client for ALL DATA SOURCES you can imagine.

What does it mean?

You can get data from anywhere you want.

GETDATA() is a NORMAL EXCEL FUNCTION:

Top

If you want to feel safe in your own well-known enviroment...

If you want to check any value on every processing level

If you want to manage your information system

If you hate black boxes

If you prefer simplicity over artificial complication

Use AFIN.NET functions.

What does it mean?

You can simply go on working as you have done upto now. No stress!

GETDATAODBC(), GETDATAOLAP(), GETDATALINK() functions

Top

There are some other functions GETDATAODBC(), GETDATAOLAP(), GETDATALINK() for autocreating for the first time in a very simple way (prototyping).

Start analysing your data immediately!

Just …

1a. Make a query, using the Microsoft Query™ built in your Excel or

WEBCAST: How to prepare a query for GETDATAODBC() function?

1b. Make a pivot table from an OLAP cube (local or from the analysis server) or

WEBCAST: How to prepare a pivot table for GETDATAOLAP() function?

1c. Make a standard Excel link to a cell in another workbook

WEBCAST: How to prepare a link for GETDATALINK() function?

(You have to do it with an elementary knowledge of … see: webcasts)

WEBCAST: How to make a first report based on your data?

2. On the object you created use a right mouse click to get the query (SQL query) / pivot cache (MDX query) / link (link string) definition into your function's arguments.

3. Parametrize the arguments with the cells' values (you can use the AFIN.NET Parametrization Wizard)

READY.