Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...