Core Architecture of ParAccel – Can refer the hyperlink PDF that talks in depth about ParAccel
The PADB architecture eliminates the need for physical indexes, tablespaces, partitions, and other disk structures that other databases force DBA’s to utilize to try and improve performance. PADB has three DDL directives that control how data is laid out on disk, and spread across the compute nodes.
– Distribution Key
– Sort Key
– Column compression
MicroStrategy handles them effectively even on their intermediate table generations (& you can even change the VLDB properties accordingly if needed at any point)
I would like to highlight the top 3 benefits that you get out of ParAccel
– Extreme Performance
– Extreme Scalability
– Extreme Agility
Short to say:
“ParAccel runs a 46-hour Oracle market basket analysis query in less than 30 seconds.”
Ref: ParAccel Website
About PADB
The ParAccel Analytical Database (PADB) is a massively parallel processing, columnar database that consists of multiple computers (nodes) with the following definitions:
1. Compute nodes that store the data for a database and perform the work of processing SQL statements.
2. A leader node that manages connections to the database and coordinates the activities of the compute nodes.
3. A dedicated PADB internal network for use only by the leader and compute nodes.
4. Failover nodes to take over for failed leader or compute nodes. The use of failover nodes is optional, but highly recommended for critical systems.
Above is a listing of only the major components of a PADB hardware configuration. The smallest configuration consists of one computer that acts as a combined leader and compute node, typically used for development, or 100’s to 1,000’s of nodes with multiple failover nodes and switches. PADB looks to an end user as one single database regardless of the underlying hardware setup.
PADB functionality and SQL language support is targeted primarily for analytics rather than OLTP (Online Transaction Processing). Analytics means optimized for working with large amounts of data, and complex operations such as aggregation and joins against disparate data, whereas OLTP largely focuses on operations performing single row insertions and deletions, transactions and locking, etc. In addition to standard SQL language support focused around functionality needed to perform analytics, that is, joining, aggregating, and sorting data, PADB is extended with many new SQL commands and functions to support analytic operations, various options to provide flexible ETL (extract, transform, and load) database methods, and extensibility with user-defined functions. With its primary focus on analytics and working with very large databases, PADB also provides numerous other features such as advanced query optimization, execution plan analysis, compiled code generation, and workload management to categorize and execute queries based on classification and priority.
For more information on PADB architecture please refer to the “ParAccel Architecture and System Overview Guide.”
Workload Management on PADB:
PADB offers workload management with the WLM feature. It provides the ability to create multiple service classes with individual pool sizes and queues to allow independent execution of certain query types (e.g. system queries, write queries, read queries). Eviction policies allow eviction of queries to another services class when specified threshold time limits are exceeded.
You can customize PADB workload management using the default WLM XML configuration file located at /home/paraccel/padb/rel/etc/padb_wlm.xml. More information on configuring and customizing PADB WLM configuration is provided in “Customizing the WLM configuration file” section in the ParAccel’s Administrator’s Guide and SQL Reference.
While PADB is able to handle up to 5000 concurrent connections, it will only process up to 20 concurrent queries concurrently (“up to 20 queries in flight”) with the remaining queries being queued. This limitation is independent of the PADB cluster size.
For this reason the total number of database connections from MicroStrategy to ParAccel should be limited to the total pool size configured in ParAccel, in any case less than 20 queries in flight.
Inside MicroStrategy – Workload management is taken care using job prioritizaation(high/medium/low) based on request types Element/Report – User Group/Application Type/Cost/etc..
Security
In ParAccel there are three primary security concerns:
Network security: Controlling leader node and compute node access and access via client connection
Database object security: Controlling which users have access to which database objects
Data encryption: Encrypting data columns within tables
MicroStrategy supports authentication to ParAccel using userid/password method.
You can always go for connection mapping in MicroStrategy to cater the rights based on the user groups..
MicroStrategy & ParAccel Certification:
ParAccel 4.0 is certified as warehouse platform with the MicroStrategy 9.3.0 release on various flavors of Windows and Linux. A subset of some of these platforms that have been certified with MicroStrategy 9.3.0 is below. For the full listing of all the certified/supported configurations, please refer to MicroStrategy’s documentation or contact MicroStrategy tech-support.
Operating System Recommended ODBC Driver Status
Windows 64-bit 32-bit ODBC Driver for ParAccel 2.0.1 Certified: WH
Linux 32-bit ODBC Driver for ParAccel 2.0.1 Certified: WH
Major ParAccel 4.0 Features
DECIMAL precision
ParAccel 3.5 is certified as warehouse platform with the MicroStrategy 9.3.0 release on various flavors of Windows and Linux. A subset of some of these platforms that have been certified with MicroStrategy 9.3.0 is below. For the full listing of all the certified/supported configurations, please refer to MicroStrategy’s documentation or contact MicroStrategy tech-support.
Operating System Recommended ODBC Driver Status
Windows 64-bit 32-bit ODBC Driver for ParAccel 2.0.1 Certified: WH
Linux 32-bit ODBC Driver for ParAccel 2.0.1 Certified: WH
Major ParAccel 3.5 Features
Support for STDDEV_POP and VAR_POP functions as both window functions and standard aggregate functions
Ranking
MicroStrategy also has all the below taken care wrt PADB;
– VLDB Properties
– Data types supported & Its mapping
– Data & Time Functions
– String Functions
– Null & Zero functions
– OLAP functions
– Statistical Functions
– Mathematical Functions
– Internal Functions
– Basic Functions
all the above with respect to the syntax of PADB is available in detail with the document given in last of this post.
Note: Unsupported Data types are
———————-
ParAccel Datatype
———————-
BOOLEAN
NCHAR AND NVARCHAR
BPCHAR
———————-
Reference : MicroStrategy with ParAccel – KB Article & PDF for reference
Cheers & Have a Good Day!!
Advance Merry Christmas wishes & Happy New Year to all!!
Have a Happy vacation Guys!!