Questions tagged [olap]
On-Line Analytical Processing, a class of systems designed for fast calculation, reporting and analysis of aggregate figures.
100
questions
0
votes
0
answers
28
views
Queried cube only returns one single value : the total value of the measure
I have a database where not each dimension row is linked to a fact table row. For that issue, and in order for me to deploy the cube, i set the UnknownMember of the dimensions to be Visible and ignore ...
0
votes
0
answers
151
views
Cube won't deploy
I was able to deploy a large cube (with 2 dimensions and 1 measure) in Analysis services server but only after setting configuration error options for both dimensions and the measure (through the ...
0
votes
2
answers
266
views
What version of MSOLAP provider am i using?
We've just upgraded a Server from SQL Server 2016 to 2019.
We have linked servers connecting to an OLAP cube.
When I look under providers for MSOLAP the label of the dialog box shows:
Microsoft OLE ...
- 3,072
0
votes
0
answers
7
views
Can PowerBI run DAX queries directly on any database?
PowerBI DirectQuery mode translates DAX queries into SQL and runs them on a given DB. It looks like OLAP databases from MS also support running DAX queries. Is there any way to run DAX queries ...
- 245
0
votes
1
answer
40
views
Data modeling for Warehouse
I am learning about database warehouses and data modeling. I came across this blog post. In the section, "DIFFERENT MODELS HAVE DIFFERENT PURPOSES" the author mentions that depending upon ...
- 1
0
votes
0
answers
16
views
Is it possible to create a cube without measures in Schema Workbench?
Starting from a star schema without measures, is it possible to create a cube without measures in Schema Workbench?
- 1
3
votes
2
answers
107
views
CUBE and calculating the number of dimensions
if a data presented with 4-dimension in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into 4096 ways!
we know for cube ...
2
votes
2
answers
376
views
How does a column-oriented DBMS filter on multiple columns?
I'm learning how column-oriented DBMS / "columnars" work for OLAP situations.
Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to ...
- 161
0
votes
1
answer
41
views
How to combine Oracle RAC with in-memory database to isloate OLAP from OLTP
I am going through a document
https://www.oracle.com/technetwork/database/options/database-in-memory-data-sheet-2338744.pdf
There is a thing written "Oracle Real Application Clusters (RAC) can be ...
- 23
1
vote
1
answer
205
views
Defining OLAP or OLTP on table level in Oracle/postgres/SQLserver
Usually companies have separate databases for OLTP and OLAP (Row stores and column stores) where data is loaded through ETL jobs to another database for analytical processing. I am particularly ...
- 23
1
vote
0
answers
30
views
SSAS : are dimensions with only Id and Name attributes useless?
I wonder if it is worth creating a dimension with only one column (e.g. Name) in addition to its Id Column. This dimension would be referenced by a fact containing a reference to the Id attribute of ...
- 141
2
votes
0
answers
71
views
startup ask failed for service MSSQLServerOLAPService
I can't install SSAS (SQL Server analysis services) in multidimensional mode : I get an error at the end of the installation : startup ask failed for service MSSQLServerOLAPService. I was able at the ...
- 141
1
vote
1
answer
47
views
Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?
I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database ...
- 149
0
votes
1
answer
44
views
In a data warehouse, should a measure be based on a fact or a dimension?
Let's say there is a data warehouse created from a shop data. A fact is a single purchase of a product. There is a dimension that describes a customer.
There is a need to create a measure that ...
- 101
1
vote
1
answer
66
views
Do I still need OLAP cubes after a SQL Server Upgrade
After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in ...
- 113
0
votes
1
answer
168
views
Restore from .db folder for Analysis Services
There is an objective to recover cube from long time ago retired MSAS instance. Issue I'm currently standing with is that actual .abf backups are now in a cold storage and recovering them would take a ...
- 101
0
votes
1
answer
19
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 ...
- 149
1
vote
0
answers
61
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-...
- 149
15
votes
1
answer
2k
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 ...
- 407
1
vote
0
answers
773
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 ...
- 176
0
votes
1
answer
198
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 -...
- 407
2
votes
0
answers
1k
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 ...
- 21
0
votes
2
answers
1k
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 ...
- 101
1
vote
0
answers
118
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 ...
- 171
0
votes
0
answers
502
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 ...
- 199
0
votes
1
answer
27
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 ...
- 155
0
votes
1
answer
428
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 ...
- 155
2
votes
2
answers
1k
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 ...
- 121
2
votes
1
answer
1k
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.
- 31
2
votes
1
answer
2k
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 ...
- 149
1
vote
1
answer
104
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 ...
- 161
1
vote
0
answers
301
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 ...
- 199
6
votes
1
answer
2k
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
0
answers
41
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'...
- 11
2
votes
3
answers
4k
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 ...
- 1,475
3
votes
1
answer
1k
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
0
answers
68
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 ...
- 11
1
vote
1
answer
210
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?
- 13
-1
votes
1
answer
267
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, ...
- 143
2
votes
1
answer
754
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'...
- 143
0
votes
1
answer
281
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
3
answers
1k
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 ...
- 3,072
0
votes
1
answer
32
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 ...
- 101
1
vote
0
answers
136
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, ...
- 121
4
votes
1
answer
3k
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.
...
- 93
1
vote
1
answer
261
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. ...
- 123
2
votes
2
answers
3k
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 ...
- 165
1
vote
1
answer
99
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,344
1
vote
0
answers
257
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 ...
- 111
12
votes
3
answers
16k
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 ...
- 739