Questions tagged [olap]
On-Line Analytical Processing, a class of systems designed for fast calculation, reporting and analysis of aggregate figures.
86
questions
0
votes
1answer
13 views
Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?
The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed ...
0
votes
0answers
22 views
NoSQL database for analytics
Need NoSQL database for analytics.
Given:
Large, partitioned Postgres(9.4) tables, with next structure:
- device_id: UUID
- monitored_date: TIMESTAMP
- properties: jsonb (different keys)
(...
0
votes
0answers
11 views
Are OLAP / NoSql / In-Memory database mutually exclusive concepts or can there be overlap between them?
I'm a data scientist with little knowledge of DB technology. I keep hearing 3 types of databases mentioned as key technologies for analytics and modeling solutions:
NoSql databases
OLAP cubes
In-...
0
votes
0answers
8 views
Building a data cube in a data warehouse through Inmon architecture?
I have been reading about data cubes and Kimball.
Through building a star schema, one can then model a cube using its measures and dimensions of the star schema.
But then there is a different data ...
15
votes
1answer
1k views
What exactly is the 'online' in OLAP and OLTP?
I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
1
vote
0answers
72 views
Where to build the OLAP cubes? [closed]
I have built a data warehouse with multiple fact tables and dimension tables (with a MySQL server).
Now where could I build the OLAP cubes and write the MDX queries to produce results?
BTW, I have ...
0
votes
1answer
37 views
SQL to MDX translation?
An MDX (a language dealing with multidimensional data, in particularly dealing with the cube metaphor) statement can be translated to an SQL (a well-known language for the relational model) statement -...
1
vote
0answers
284 views
connection to remote Analysis Services from SSMS
On my company local SQL Server instance I was able to add a linked server to a remote AS instance (in Management Studio > Server Objects). I was also able to connect using Data Tools, as well with ...
0
votes
2answers
606 views
Cons of using a star schema in a columnar database
My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal. There are many causes for this but I think the key reason is ...
1
vote
0answers
92 views
Proactive caching vs. nightly cube reprocess
I'm working with an SSAS cube that's fully reprocessed every night by an SSIS job.
My current goal is to get data into the cube with less latency, and to do so I'm looking at implementing proactive ...
0
votes
0answers
145 views
Process an OLAP Cube as a daily job takes too long using `ProcessFull` option
I have two cubes which updates in sequence. These takes very long to update, upwards of 5 hours due to their sizes.
I have a SQL agent job which updates the cubes. The job has type SQL Server ...
0
votes
1answer
18 views
Using OLAP-specs to compute selective running totals
Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
0
votes
1answer
210 views
OLAP window functions in DB2
I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query
declare global ...
2
votes
2answers
581 views
Dealing with different types in a fact table
I'm working with dimensional modelling for the first time, trying to build a data warehouse that pulls in data from an OLTP database, and I'm having some trouble figuring out what to do with this type ...
2
votes
1answer
591 views
SSAS Realtime Processing
I created a cube in SSAS, how I can extract real time data from the data source to my cube?
I mean if any data modification (insert, update, delete) happens, SSAS should update the cube.
3
votes
1answer
454 views
Why do OLAP cubes perform better than Relational databases on certain tasks?
My team uses a Multidimensional OLAP database for a lot of it's analytics and predictive modeling. The standard line in all the documentation is "OLAP cubes are better suited for this type of ...
1
vote
1answer
56 views
MAT over a calculated measure [closed]
We have an OLAP SSAS cube with budget data on the fact table, and budget cathegory (sales, costs, salaries, ...) on a dimension. We have a bunch of formulas for our users but we are not able to create ...
1
vote
0answers
189 views
Reduce RAM required to process OLAP cube
I have a cube which I have created by using (in Visual studio) New Project -> Import from Server (Multidimensional and data mining).
This cube is indeed created from the metadata of an already ...
6
votes
1answer
765 views
SSAS OLAP Semi-Additive Measure over Time Dimension
Issue:
Help, I'm relatively new to building OLAP cubes in Microsoft's SQL Server Analysis Services and I need help with a semi-additive measure over a time dimension.
In other words, I want to show ...
1
vote
0answers
40 views
What schema if I have many measures
Forgive me if I misuse any words. I'm a c++ programmer with some basic DB knowledge.
I'm developing an analytics engine for simulation data for vehicle engines. OLAP seems like a good fit but I haven'...
3
votes
3answers
722 views
Should dates in dimensional tables use dimDate?
Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date?
For example, in ...
3
votes
1answer
902 views
speed up PostgreSQL calculations via OLAP warehouse?
So I have asked these three questions:
Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5
https://quant.stackexchange.com/questions/29572/building-financial-data-...
1
vote
0answers
59 views
Incorrect hierarchy relationship while joining with parent-child tables
I have two tables with 1:M relation in my DB:
GROUP (GroupId, GroupName, AccountId)
USER (UserId, UserName, GroupId, AccountId)
and ACCOUNT (AccountId, AccountName).
I added all needed links from ...
1
vote
1answer
109 views
Is OLAP cube duplication of data?
Is an OLAP cube a duplication of data or is it just references to original tables? Or can I control it?
-1
votes
1answer
205 views
SQL Server connection, session, command handling
I am trying to build an etl tool that tracks DMV data from SSAS Instance. To do that and in advance to record sql queries that users are request, i must know how the sql server handles the connection, ...
2
votes
1answer
353 views
SSAS Cache Levels
I am using SSAS for reporting statistics on a big amount of data.
Recently I began learning best practices for tuning and I am interesting in how SSAS caches the data.
I have done my research and I'...
0
votes
1answer
192 views
pglogical for OLAP queries
I am looking to setup a replica for OLAP queries. Currently in my prod I have a master (OLTP node) replicating to a slave via streaming replica. I don't want to use streaming replica for OLAP queries ...
2
votes
3answers
996 views
SSAS How to calculate Standard Deviation
I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average.
The data comes from many different "Data sources". These are are held in a ...
0
votes
1answer
31 views
Should I put OLAP and realtime web DBs on the same server?
I have several different workloads I'd like to consolidate into the same Sql 2012/2014 instance.
Cognos/OLAP is burst oriented and users are accustomed to a response delay
Web databases that require ...
1
vote
0answers
101 views
SSAS 2014, Relation OLAP - Poor performance, high resource usage, what did I do wrong?
Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, ...
3
votes
1answer
2k views
Star Schema from Relational Database
I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.
...
1
vote
1answer
149 views
OLTP to OLAP process causing deadlocks and impacting customers
I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. ...
2
votes
2answers
2k views
SSAS where are olap cubes stored
Getting started on creating data cubes with SSAS. I understand the various types of OLAP options ROLAP, HOLAP and MOLAP. However, when creating a data cube using SSAS (at least for 2008 version) I do ...
1
vote
1answer
85 views
SQL Server Table Design for Report - Is OLAP Schema Suitable
A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS).
He insists on creating a ...
1
vote
0answers
189 views
Is it possible to use Hibernate for OLAP-server?
I have a PostgreSQL database with Java Hibernate. I need a XMLA datasource for a pivot grid client component.
Is it possible to use Hibernate for a OLAP-server?
Which OLAP-server does support ...
11
votes
3answers
13k views
Good example of MDX vs SQL for analytical queries
Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results.
Wikipedia ...
0
votes
1answer
189 views
Adding templated Date-Time dimension to the cube
I have a database with FactSales table that contains Date column of type I4. I have created a cube and linked dimensions via foreign keys as usual, however, since I have no DateTime table in the ...
3
votes
1answer
539 views
After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also
This problem is with an SSAS 2012 SP3 (11.0.6020.0) multi-dimensional server, hosting a single cube database, containing 1 cube and 16 dimensions. Aggregations have been designed using SQL Management ...
6
votes
1answer
339 views
How do you index alphanumeric foreign keys on large fact tables?
I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not ...
3
votes
1answer
4k views
SSAS cube process time increased after making fact and dimension small
I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data.
For that, I have changed dimension and Fact views to ...
2
votes
1answer
2k views
SSAS cube fact and dimension relationship
I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension.
When I view the measures in excel from Table 1 with attributes from table 2, I ...
6
votes
1answer
876 views
Clustered columnstore index performance SQL Server 2014
I'm setting up an OLAP database using SQL Server 2014. The core fact table has about 40,000,000 rows, 225 columns with an average row size of 181 bytes. I've been toying around with the clustered ...
1
vote
1answer
1k views
Is processing required dimensions enough or to process the entire cube?
I have four large dimensions and one of the dimension is not going to be changed, also the particular dimension didn't need to be processed.
So, processing the three dimensions individually updates ...
0
votes
0answers
655 views
Handle Duplicate entries in Fact table in Data Cube
We have been constructing data cube for our project in which we are facing a problem like 1 to many relationship in the dimension tables and making entries in fact table (duplication).
...
1
vote
1answer
1k views
Having Non-Numeric values as measures in Fact table
According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one.
So I planned to maintain numeric values. My scenario was
"Employee's ...
2
votes
1answer
1k views
Update OLAP Cube data
We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis.
I need some suggestions about an optimal way for updating the data ...
0
votes
0answers
83 views
Mysql - too many Deletion - Insertion
I'm creating cron jobs for my OLAP system.
I have a cron job that deletes the records from the summary table first and then insert the records in it again:
Say Deleting 1 month records, and ...
0
votes
0answers
783 views
CREATE OLAP server
I have a MySQL database server along with replication server, i.e. OLTP system altogether.
As I have very heavy data fetching queries for the decision making reports. I need to create data-ware house ...
2
votes
2answers
5k views
Get distinct count on calculated member?
I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has ...
1
vote
0answers
64 views
How to keep archive table accessible for some users which is not frequently accessible?
I have partitioned my table based on some boundary ranges.
My Database is built at MSSQL server 2008 R2.
I have done partitioning for a table which has 40 million records in it. Now it's time to ...
