Build your own OLAP cube with AFIN.NET! The OLAP technology has some pros: small files, very fast usage, viewing limitations for unauthorized users and so on. You should use it! There is simpliest OLAP cube definition as you have ever seen. It uses the best and well known user interface - the Excel pivot table. You needn't know the OLAP structures and basics, you ought to know Pivot Table usage only! General information Excel OLAP Cube Wizard There are some problems in creating and using Excel OLAP cubes - cube files (.cub) created by Excel OLAP Cube Wizard. This is a functionality added to Excel in version 2000 and has never been updated. Now it is erased from the standard "because of the lack on funcionality" - all the users are forced to implement SQL Server Analysis Services For example, the (Excel) wizard could not: - work on Excel 2007 and higher versions (the newest is 2010) - redesign cubes - all the building process has to be started every time from the beginning - parametrize usage of the cubes - one Excel 'data source' could (and still can) be connected to one cub file only. AFIN.NET.CubeBuilder, working in Excel 2003, 2007, 2010,.... This is a procedure, interpreting the base workbook as a complete cube file definition. The cube dimensions are defined as sheets (the name of the sheet is automatically the name of the measure) One time created cube definition (OQY file) doesn't require using AFIN.NET! The Excel alone can refresh the cubes. How to start? You should have: 1. The saved workbook 2. The (only one) query in the sheet named 'Query' 3. The sheet(s) with pivot tables with the dimensions definitions 4. The dimensions defined as ROW FIELDS in the pivot tables AND measures defined as DATA FIELDS (measures) there. and... 5. Start AFIN.NET / Tools/ Other / AFIN.NET.CubeBuilder 1. Create a new workbook and save it with the name 'MyTestCube1.xls' (xlsx, xlsm formats accepted) 2. Create a query from any data source of ODBC type - not complicated, simple table without criteria, groups, orders and so on Example: Query to the whole table 'faktura' in [AFIN.NET.Folder]\Samples\Data\ODBC\SalesInAccess.mdb 3. Rename the sheet where your query was placed to 'Query' 4. Add a new sheet and start there the standard 'Pivot Table Wizard' (Insert / Pivot Table in E2007, E2010). 5. When asked about data range click [F3] (insert name) and select the name of the query. 6. Then end the wizard in normal way - an empty pivot table is ready. 7. Make a dimension 7.1. Name this sheet with the name of your desired dimmension, eg 'Clients' 8. Create the next dimension with the pivot table (eg. by simple copying the current sheet) and repeat this point7.2. Get the field 'nazwa' and put it into 'Row area' in the pivot table 9. Make a measures sheet 9.1. Add a new sheet 9.2. Name it 'MyMeasure1' 9.3. Put the 'wart_net' field into data area in the pivot table - 'Sum of wart_net' Reapeat this point with Measures2, ..3 10. Start AFIN.NET / Tools/ Other / AFIN.NET.CubeBuilder Technical information Classified procedure errors: - Workbook not saved - No 'Query' sheet - No ODBC query in the 'Query' sheet - No pivot tables in the workbook - No row fields in any pivot table - No measures - Two (or more) measures in one pivot table - build so many pivot tables as you have measures or insert the measures into the pivot tables with dimensions' definitions. A dimension and a measure can co-exist in one pivot table, two measures cannot also two dimensions cannot, either. - More than one definition of the measure with the same name (one measure two times added under one name) in different pivot tables. You can add two different measures on one data field (sum, count, max, ...), two the same measures (but under different names), too |
|
More features... (!) >
AFIN.NET.CubeBuilder |