Questions tagged [olap]
On-Line Analytical Processing, a class of systems designed for fast calculation, reporting and analysis of aggregate figures.
-1
votes
0answers
17 views
Database architecture for dancesport results
I am creating a dockerized website in PHP for dancesport results.
I am trying to figure out a database solution (probably a combination of databases) for storing the competitions and their results.
...
1
vote
0answers
17 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
14 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
191 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
537 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
votes
0answers
30 views
Slicing a partition by current year, previous year, and two years ago
I have a cube that has partitions of this year, last year, and the year before last.
Currently the partitions filter dates using a SQL query, but I'd like to replace that with slices.
Ideally, I'd ...
1
vote
0answers
80 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
116 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
184 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
491 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
476 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
350 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
52 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
169 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
660 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'...
2
votes
2answers
578 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 ...
4
votes
1answer
825 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
56 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
102 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
189 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
290 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
184 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
901 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
30 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
92 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
1k 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
139 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. ...
3
votes
2answers
1k 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
172 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
12k 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
164 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 ...
4
votes
1answer
508 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
312 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
3k 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
847 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
613 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
82 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
743 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
1answer
63 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 ...
0
votes
1answer
706 views
What's the point of SQL joins if you have OLAP?
I want to understand the purpose of using SQL joins from a business analysis perspective.
Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join ...
0
votes
1answer
428 views
How to change a web application that now needs to become OLTP and OLAP application?
Previously, I did a simple web application for an enterprise client using PHP and MySQL.
After some time, it has grown to a point where I realized after talking with people here that I need to treat ...
3
votes
2answers
4k views
OLAP or OLATP - How to determine
I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP.
There is no analysis server installed, the DB is ...
