Tuesday, August 30, 2016
The time for ROLAP has arrived:
Columnar databases to the rescue.
Next Steps: Build an SSAS ROLAP Cube using EXASOL
Monday, August 29, 2016
Error: Could not find or load main class org.apache.hadoop.mapreduce.v2.app.MRAppMaster
We have not seen this error, but wanted to include it in our listing of identified PolyBase errors. The user later noted that their solution required a change to the mapred-site.xml file, as follows:
<property> <name>mapreduce.app-submission.cross-platform</name> <value>True</value> </property>
You can find the full user thread here: Yarn ClassPath Value for Polybase Pushdown
Wednesday, August 17, 2016
Why we made the change
See: Parallel Data Warehouse (PDW) and ROLAP - Hilmar Buchta.
- Locate msmdsrv.ini
- Make a backup of the original, unaltered ini file. (backups are our friend)
- Locate <EnableRolapDistinctCountOnDataSource>
- Change the setting to 1
- Save the file and restart SSAS
Make the Change
FIX: SSAS 2012 to back-end SQL Server 2014
Tuesday, August 16, 2016
Monday, August 15, 2016
Data Change Table
With the TrackDataChange table in place, and your ETL processes configured to insert a row after inserting new fact table table, your polling query will look like this:
SELECT COUNT(*) FROM DWTEST.TRACKDATACHANGE;
You'll probably want more than just the basic columns. The key point is to run SSAS's polling query against an independent table dedicated to tracking fact table changes.
Thursday, August 11, 2016
They also have a free DLM tool, called DLM Dashboard. while it is part of the full SQL Toolbelt, you can download just the DLM Dashboard for free. Redgate is a great operation, putting a lot of effort into supporting SQL Server, including its sponsorship of http://www.sqlservercentral.com/.
You can find the DLM Dashboard here: http://www.red-gate.com/products/dlm/dlm-dashboard/.
Monday, August 8, 2016
Virtual Data Mart - using ROLAP
- Curate analytic data into targeted virtual analytic cubes (or single if need be)
- Add a layer of security, when needed
- Push processing down to the analytics database engine
We've already taken a look at SSAS ROLAP cubes using EXASOL. You can find the details here: Build an SSAS ROLAP Cube using EXASOL.
Friday, August 5, 2016
3: Under Options, on the Notification tab, we made the following adjustments:
- Scheduled polling - select this option
- Polling interval - we set this to one (1) minute.
You are after a balance between real time, and not overloading the database with excessive queries - there is no correct answer. And, this can be changed easily. See below.
- Polling query - here we simply used a count(*).
For our example it was: SELECT COUNT(*) FROM DWTEST.FACTTESTSALES;
against the fact table on the relational database. Note: for production, run the polling query against a track data change table - see ROLAP Polling Query - use a change tracking table.
Making Live Changes to our ROLAP Partition
- connect to the Analysis Server
- locate the target partition
- right click - properties
- Proactive Caching - select on the left menu bar
- Options Button - select
Build an SSAS ROLAP Cube using EXASOL.
Thursday, August 4, 2016
What we are looking for in this process:
- Ease of data population, updates and data maintenance- using SSIS
- Design and configuration of SSAS ROLAP cube using EXASOL
- Usability - the Analyst experience
Data population, updates and data maintenance
Design and configuration of ROLAP cubes
(1) I have been informed by Mathias Golombek, CTO, that they are working on an updated ADO.NET driver to resolve these issues.
Real-time ROLAP, with Options
Warming the Indexes
But I need my hands on the Optimizer control
Next Steps: Partition Settings for our SSAS ROLAP cube using EXASOL
Tuesday, August 2, 2016
Install the EXASOL ADO.NET Driver
EXASOL also has an ODBC driver, both 32 bit and 64 bit. You can opt to use this, rather than ADO.NET. Overall, the process is similar.
Data TypesThe data types in EXASOL have distinct differences from those in SQL Server - especially dates. So before you get started, you'll want to map out both the source and target data types, along with any transformations required. For a comparison of EXASOL and SQL Server data types, along with methods on transforming data see: Load EXASOL from SQL Server: Data types & Conversion.
In general, we found that it's best to use a view in SQL Server, or a SQL Command as part of the OLE DB source task. You can also use a Data Conversion Task to make your conversions. However, we believe using the database engine is more efficient for these types of transformations.
No tables or views could be loaded
Next Steps: ROLAP - now a viable option
Build an SSAS ROLAP Cube using EXASOL