I'm new to Microsoft SQL Server Business Intelligence and Analysis Service (but I've been programming for years with SQL Server). Can any one describe Measures and Dimensions in Cubes in simple words (If it's possible with images)?

I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change: 1. which object belongs to which 2. attributes of each object. Here is my schema: fact.Edge: the_date date parent_id int child_id int fact.Vertex: the_date date id int attribute1 int attribute2 int ... attributen int dim.attribute{1...n}: id int value1 nvarchar(64) value2 nvarchar(64) ... valuem nvarchar(64) These tables get new data once daily. If nothing changes, then there are two copies of the exact same data in the two fact tables with sequential dates. I would like to know if it is possible to define a parent child hierarchy in SSAS based on the fact.Edge table referencing itself (via child_id->parent_id) but also only when the_date = the_date. I am new to SSAS, but it seems only one attribute can be the parent attribute. Are there any workarounds? Additionally, is it possible to treat the vertex table as two "fact" related dimensions -- ie parent_vertex and child_vertex? Or else do I need to include edges with either a null parent_id or null child_id and choose the other to have the only vertex reference? If my questions don't quite make sense (likely due to my limited SSAS experience), is there an example cube definition that demonstrates best practices for this case? I'd appreciate any insights you might have!

We have a production server with two SSAS instances, 1 for user querying and 1 with empty templates where we do new releases and (full) processing of the cubes (and then backup and restore processed cubes to main instance, and delete the processed cubes from the 'processing' instance). This is to prevent any downtime on our instance used by clients. These processed cubes seem to be kept in memory, even though they are deleted after processing and backup. This processing instance holds no data as seen in ssms, but it keeps growing in memory (up to 40-50GB) untill it starts failing to process due to memory issues after a few days. About 95% of this memory is outside of the shrinkable/non-shrinkable data cleaner memory, so the memory limits are not doing anything to release this memory. After processing all cleaner memory drops to several 100 mb, while total memory usage for this instance stays high, and will keep growing untill we have failures. I don't believe the solution lies in any memory limits, since the used memory is not detected by the SSAS data cleaner. I have tested adjusting these memory limits, no effect. Doing a process clear before deleting the cubes also has no effect. The only thing that works is a manual restart of this instance every 2-3 days, but this is obviously not a proper/maintainable solution (automating this in a job step would require a proxy account with full admin rights on our production server, something we would like to avoid). All software is up-to-date (microsoft analysis server version 15.0.35.33), VertiPaqPagingPolicy = 1, server mode is Tabular and all cubes are in Import mode. I've been researching for a while now, but can't find the same issue anywhere, let alone a solution. Any help would be greatly appreciated!

I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where to start. My question are: - What is difference between Star Schema and data cube? - Where I have to start? From star schema or directly data cube? - Is data cube generated from star schema?

This question hurts my brain to talk about. Hopefully I can explain it properly. I have the following calculation defined in my cube: CREATE MEMBER CURRENTCUBE.[Measures].[52 Week Comp Net Sales Total] AS SUM( {[Dim Fiscal Weeks].[Week Id].CurrentMember.Lag(51) : [Dim Fiscal Weeks]. [Week Id].CurrentMember} ,[Measures].[Comp Net Sales]), FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 , DISPLAY_FOLDER = 'Sales / Trans / Units'; This *ought* to give me a 52-week trailing sum of sales, and it does... but ONLY when the cube is expanded to a weekly view in Excel. When it's rolled up to a Quarterly or YTD view, then it displays the sum of sales that occurred in the rolled-up-period *only.* Here is a screen shot explaining what I mean: What I don't understand is why the Quarter and Period (month) roll-ups don't include a full-year's worth of data. If a year's worth of sales trailing back from 1/1/2005 was 954,000, it doesn't make sense that Quarter 1, 2005 shows a year's worth of sales is only 252,000. Can someone help me understand what I'm seeing? Have I done something wrong? How do I write a rolling average calculation that is accurate even when rolled up?

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 properties dialog box of each one of them) to specify : 1. KeyNotFound : IgnoreError & 2. NullKeyNotAllowed : IgnoreError & 3. NullKeyConvertedToUnknown : ReportAndContinue & 4. CalculationError : IgnoreError My problem is now after setting the right dimension usage options and after adding an admin access, still when i try to browse the cube the measure returns no values (adding the dimensions as well to browse changes nothing). The cube processing task ends succesfully after mentioning the number of measure rows read in the partition, also the table views data explorer for the measure returns the data correctly. My question is how to make the cube browsing return the measure rows when i execute the sample query provided by VS2019 ? PS : the measure i defined has the **float** type. Hints : when i specify for the measure property KeyNotFound : ReportAndContinue the processing of cube stops with error message > saying reached limit number of errors Thanks for the help. UPDATE : FULL ERROR WHEN I PROCESS THE CUBE (translated from foreign language) > Errors in the OLAP storage engine: Attribute key not found during > processing of: Table: 'dbo_MyFacttable', Column: 'Field7', Value: > '7811'; Table: 'dbo_MyFacttable', Column: 'Champ14', Value: '3.64000'. > The attribute is 'Field1'. Errors in the OLAP storage engine: The > record was skipped because the attribute key could not be found. > Attribute: Field1 of the dimension: Customer Address of the database: > MyDB, Cube: Mycube, Group of measures: Facts, Partition: Facts, > Record: 1. Errors in the OLAP storage engine: The processing operation > is is complete because the number of errors that occurred during the > operation has reached the error limit. Errors in the OLAP storage > engine: An error occurred while processing the 'Facts' partition of > the 'Facts' measure group for the 'Mycube' cube from the MyDB > database.

Adding a GIST index actually seems to make K-Nearest Neighbor (KNN) ORDER BY queries on cube columns *worse* in PostgreSQL. Why would that be, and what can be done about it? Here's what I mean. In a PostgreSQL database I have a table whose DDL is create sample (id serial primary key, title text, embedding cube) where the embedding column is an embedding vector of the title obtained with a Google language model. The cube data type is provided by the cube extension, which I have installed. Incidentally, these are titles of Wikipedia articles. In any case, there are 1 million records. I then perform a KNN query with the following query. This query defines distance using the Euclidean distance operator `, though results are similar for the other two metrics. It does an ORDER BY and applies a LIMIT` in order to find 10 Wikipedia articles with "similar" titles (the most similar being the target title itself). That all works fine.

select sample.title, sample.embedding  cube('(0.18936706, -0.12455666, -0.31581765, 0.0192692, -0.07364611, 0.07851536, 0.0290586, -0.02582532, -0.03378124, -0.10564457, -0.03903799, 0.08668878, -0.15357816, -0.17793414, -0.01826405, 0.01969068, 0.11386908, 0.1555583, 0.09368557, 0.13697313, -0.05610929, -0.06536788, -0.12212707, 0.26356605, -0.06004387, -0.01966437, -0.1250324, -0.16645767, -0.13525756, 0.22482251, -0.1709727, 0.28966117, -0.07927769, -0.02498624, -0.10018375, -0.10923951, 0.04770213, 0.11573371, 0.04619929, 0.05216618, 0.19176421, 0.12948817, 0.08719034, -0.16109011, -0.02411379, -0.05638905, -0.37334979, 0.31225419, 0.0744801, 0.27044332)') distance from sample order by distance limit 10;

What's puzzling to me, however, is that, if I put a GIST index on the embedding column, the query performance actually is *worse*. Adding the index, the query plan changes as expected, in the way expected, insofar as it uses the index. But...it gets slower! This seems to run contrary to the documentation for cube which states: > In addition, a cube GiST index can be used to find nearest neighbors using the metric operators , , and in ORDER BY clauses They even provide an example query, which is very similar to mine.

SELECT c FROM test ORDER BY c  cube(array[0.5,0.5,0.5]) LIMIT 1

Here's the query plan and timing info *before* dropping the index.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..6.30 rows=10 width=29)
   ->  Index Scan using sample_embedding_idx on sample  (cost=0.41..589360.33 rows=999996 width=29)
         Order By: (embedding  '(0.18936706, -0.12455666, -0.31581765, 0.0192692, -0.07364611, 0.07851536, 0.0290586, -0.02582532, -0.03378124, -0.10564457, -0.03903799, 0.08668878, -0.15357816, -0.17793414, -0.01826405, 0.01969068, 0.11386908, 0.1555583, 0.09368557, 0.13697313, -0.05610929, -0.06536788, -0.12212707, 0.26356605, -0.06004387, -0.01966437, -0.1250324, -0.16645767, -0.13525756, 0.22482251, -0.1709727, 0.28966117, -0.07927769, -0.02498624, -0.10018375, -0.10923951, 0.04770213, 0.11573371, 0.04619929, 0.05216618, 0.19176421, 0.12948817, 0.08719034, -0.16109011, -0.02411379, -0.05638905, -0.37334979, 0.31225419, 0.0744801, 0.27044332)'::cube)
(3 rows)

        title         |      distance      
----------------------+--------------------
 david petrarca       | 0.5866321762629475
 david adamski        | 0.5866321762629475
 richard ansdell      | 0.6239883862603475
 linda darke          | 0.6392124797481789
 ilias tsiliggiris    | 0.6996660649119893
 watson, jim          | 0.7059481479504834
 sk radni%c4%8dki     |   0.71718948226995
 burnham, pa          | 0.7384858030758069
 arthur (europa-park) | 0.7468462897336924
 ivan kecojevic       | 0.7488206082281348
(10 rows)

Time: 1226.457 ms (00:01.226)

And, here's the query plan and timing info *after* dropping the index.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=74036.32..74037.48 rows=10 width=29)
   ->  Gather Merge  (cost=74036.32..171264.94 rows=833330 width=29)
         Workers Planned: 2
         ->  Sort  (cost=73036.29..74077.96 rows=416665 width=29)
               Sort Key: ((embedding  '(0.18936706, -0.12455666, -0.31581765, 0.0192692, -0.07364611, 0.07851536, 0.0290586, -0.02582532, -0.03378124, -0.10564457, -0.03903799, 0.08668878, -0.15357816, -0.17793414, -0.01826405, 0.01969068, 0.11386908, 0.1555583, 0.09368557, 0.13697313, -0.05610929, -0.06536788, -0.12212707, 0.26356605, -0.06004387, -0.01966437, -0.1250324, -0.16645767, -0.13525756, 0.22482251, -0.1709727, 0.28966117, -0.07927769, -0.02498624, -0.10018375, -0.10923951, 0.04770213, 0.11573371, 0.04619929, 0.05216618, 0.19176421, 0.12948817, 0.08719034, -0.16109011, -0.02411379, -0.05638905, -0.37334979, 0.31225419, 0.0744801, 0.27044332)'::cube))
               ->  Parallel Seq Scan on sample  (cost=0.00..64032.31 rows=416665 width=29)
(6 rows)

        title         |      distance      
----------------------+--------------------
 david petrarca       | 0.5866321762629475
 david adamski        | 0.5866321762629475
 richard ansdell      | 0.6239883862603475
 linda darke          | 0.6392124797481789
 ilias tsiliggiris    | 0.6996660649119893
 watson, jim          | 0.7059481479504834
 sk radni%c4%8dki     |   0.71718948226995
 burnham, pa          | 0.7384858030758069
 arthur (europa-park) | 0.7468462897336924
 ivan kecojevic       | 0.7488206082281348
(10 rows)

Time: 381.419 ms

Notice: - With Index: 1226.457 ms - Without Index: 381.419 ms This very puzzling behavior! All of it is documented in a GitHub repo so that others can try it. I'll add documentation about how to generate the embedding vectors, but that *shouldn't* be needed, as in the Quick-Start I show that pre-computed embedding vectors can be downloaded from my Google Drive folder. **Addendum** It was asked in the comments below to provide the output of explain (analyze, buffers). Here that is, where 1. I re-create the (covering) index 2. I run the query with explain (analyze, buffers) 3. I drop the index 4. I run the query with explain (analyze, buffers) again

pgbench=# create index on sample using gist (embedding) include (title);
CREATE INDEX
Time: 51966.315 ms (00:51.966)
pgbench=# 
                                                                                                                                                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..4.15 rows=10 width=29) (actual time=3215.956..3216.667 rows=10 loops=1)
   Buffers: shared hit=1439 read=87004 written=7789
   ->  Index Only Scan using sample_embedding_title_idx on sample  (cost=0.41..373768.39 rows=999999 width=29) (actual time=3215.932..3216.441 rows=10 loops=1)
         Order By: (embedding  '(0.18936706, -0.12455666, -0.31581765, 0.0192692, -0.07364611, 0.07851536, 0.0290586, -0.02582532, -0.03378124, -0.10564457, -0.03903799, 0.08668878, -0.15357816, -0.17793414, -0.01826405, 0.01969068, 0.11386908, 0.1555583, 0.09368557, 0.13697313, -0.05610929, -0.06536788, -0.12212707, 0.26356605, -0.06004387, -0.01966437, -0.1250324, -0.16645767, -0.13525756, 0.22482251, -0.1709727, 0.28966117, -0.07927769, -0.02498624, -0.10018375, -0.10923951, 0.04770213, 0.11573371, 0.04619929, 0.05216618, 0.19176421, 0.12948817, 0.08719034, -0.16109011, -0.02411379, -0.05638905, -0.37334979, 0.31225419, 0.0744801, 0.27044332)'::cube)
         Heap Fetches: 0
         Buffers: shared hit=1439 read=87004 written=7789
 Planning:
   Buffers: shared hit=14 read=6 dirtied=2
 Planning Time: 0.432 ms
 Execution Time: 3316.266 ms
(10 rows)

Time: 3318.333 ms (00:03.318)
pgbench=# drop index sample_embedding_title_idx;
DROP INDEX
Time: 182.324 ms
pgbench=# 
                                                                                                                                                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=74036.35..74037.52 rows=10 width=29) (actual time=6052.845..6057.210 rows=10 loops=1)
   Buffers: shared hit=70 read=58830
   ->  Gather Merge  (cost=74036.35..171265.21 rows=833332 width=29) (actual time=6052.825..6057.021 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=70 read=58830
         ->  Sort  (cost=73036.33..74077.99 rows=416666 width=29) (actual time=6002.928..6003.019 rows=8 loops=3)
               Sort Key: ((embedding  '(0.18936706, -0.12455666, -0.31581765, 0.0192692, -0.07364611, 0.07851536, 0.0290586, -0.02582532, -0.03378124, -0.10564457, -0.03903799, 0.08668878, -0.15357816, -0.17793414, -0.01826405, 0.01969068, 0.11386908, 0.1555583, 0.09368557, 0.13697313, -0.05610929, -0.06536788, -0.12212707, 0.26356605, -0.06004387, -0.01966437, -0.1250324, -0.16645767, -0.13525756, 0.22482251, -0.1709727, 0.28966117, -0.07927769, -0.02498624, -0.10018375, -0.10923951, 0.04770213, 0.11573371, 0.04619929, 0.05216618, 0.19176421, 0.12948817, 0.08719034, -0.16109011, -0.02411379, -0.05638905, -0.37334979, 0.31225419, 0.0744801, 0.27044332)'::cube))
               Sort Method: top-N heapsort  Memory: 26kB
               Buffers: shared hit=70 read=58830
               Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Seq Scan on sample  (cost=0.00..64032.33 rows=416666 width=29) (actual time=0.024..3090.103 rows=333333 loops=3)
                     Buffers: shared read=58824
 Planning:
   Buffers: shared hit=3 read=3 dirtied=1
 Planning Time: 0.129 ms
 Execution Time: 6057.388 ms
(18 rows)

Time: 6053.284 ms (00:06.053)

I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint. The entire “Foodmart” database can be downloaded from: http://didawiki.cli.di.unipi.it/lib/exe/fetch.php/bdd-infuma/foodmart_mysql.sql.zip In short, is my SQL syntax valid in interpreting the question correctly? I put the questions in reverse order from 4.3a 4.3b 4.2 and 4.1 so they decrease with difficulty. Question 4.3 Produce a report with information about the A) profit and ratioP of each product family, store country, year and gender; the ratioP is between profit and the store sales in the gender category (Male or Female) B) the profit of each subset of the above combination My syntax is: -- 4.3a select PC.product_family , ST.store_country , TBD.the_year as year , C.gender , (sum(store_sales) - sum(store_cost)) as total_profit , ((sum(store_sales) - sum(store_cost)) / sum(store_sales)) as perc_profit from sales_fact S left join product P ON S.product_id = P.product_id left join product_class PC ON P.product_class_id = PC.product_class_id left join store ST ON S.store_id = st.store_id left join time_by_day TBD ON S.time_id = TBD.time_id left join customer C ON S.customer_id = C.customer_id group by PC.product_family, ST.store_country, TBD.the_year, C.gender ; -- 4.3b select PC.product_family , ST.store_country , ST.store_city , TBD.the_year as year , TBD.the_month as month , TBD.the_day as day , C.gender , (sum(store_sales) - sum(store_cost)) as total_profit from sales_fact S left join product P ON S.product_id = P.product_id left join product_class PC ON P.product_class_id = PC.product_class_id left join store ST ON S.store_id = st.store_id left join time_by_day TBD ON S.time_id = TBD.time_id left join customer C ON S.customer_id = C.customer_id group by PC.product_family, ST.store_country, ST.store_city, TBD.the_year, TBD.the_month, TBD.the_day, C.gender ; select PC.product_family , ST.store_city , TBD.the_year as year , sum(store_sales) as total_sales , (sum(store_sales) - sum(store_cost)) as total_profit , ((sum(store_sales) - sum(store_cost)) / sum(store_sales)) as perc_profit from sales_fact S left join product P ON S.product_id = P.product_id left join product_class PC ON P.product_class_id = PC.product_class_id left join store ST ON S.store_id = ST.store_id left join time_by_day TBD ON S.time_id = TBD.time_id group by PC.product_family, ST.store_city, TBD.the_year ; Could there be any merit, necessity, or efficiency in using cube by or roll-up functions in this query? A university Professor suggests: You do not need cube by. The queries you mention have nothing to do with cubes or rollup They are simply group by and many combinations of the intermediate results. "They are simply group by and many combinations of the intermediate results." "With group by having and aggregate functions like max sum etc. can achieve what is asked. You just need to create a strategy (plan) on how to compute it and then do the select project join queries that do that." “With group by having and aggregate functions like max sum etc. you can achieve what is asked. You just need to create a strategy (plan) on how to compute it and then do the select project join queries that do that.” “It is not something done it 2 min … It needs some thinking (but that does not make it hard).” I have other questions below but am grateful for your reply or anyone else you deem suitable! Here are the other 2 questions on the same database with my syntax: 4.2 For every product family and customer country and gender return the customer with the highest store sales of the product family. If there are two or more customers with the same store sales, pick up any. -- 4.2 select distinct C.lname, C.fname, PC.product_family, C.country, C.gender, MAX(S.store_sales) from sales_fact S left join product P on S.product_id = P.product_id c left join product_class PC on P.product_class_id = PC.product_class_id left join customer C on S.customer_id = C.customer_id group by PC.product_family, C.country, C.gender ; The university Professor suggests only grouping by year?: you group by (product family, customer country, gender, customer) in the select clause you put product family, customer country, gender, customer, sum(sakes) Now you know the sales of each customer. Then you use that table, to identify the max sales and select the tuples that have such a value. Possible error? Is there also for example the requirement of the use of a ranking or anything else for that matter? 4.1 Return a table where you list product family, store city and year, with the corresponding total sales and profit, and the percentage of profit over the total sales of the year. -- 4.1 select PC.product_family , ST.store_city , TBD.the_year as year , sum(store_sales) as total_sales , (sum(store_sales) - sum(store_cost)) as total_profit , ((sum(store_sales) - sum(store_cost)) / sum(store_sales)) as perc_profit from sales_fact S left join product P on S.product_id = P.product_id left join product_class PC on P.product_class_id = PC.product_class_id left join store ST on S.store_id = ST.store_id left join time_by_day TBD on S.time_id = TBD.time_id group by PC.product_family, ST.store_city, TBD.the_year ; you simply group by year and then you do in the select clause sum(sales), sum(profit), sum(sales)/sum(profits) The from clause should have the join of all the tables containing the needed information That is all. I do not only group by year as I see also PC.product_family, ST.store_city in my syntax. Possible error? Could there also, for example, in the Select be the error of the inclusion of attributes not present in the group by? Many thanks people for this help, Tim

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 with n dimension in which none of dimension is > hierarchical we have 2^n summarizing ways, but in this case that each > dimension has hierarchical 3-level aggregate have 4^4=256 ways. Why does the above statement mention that there are 4096 ways? if I couldn't state my problem very well because of a flaw in my English usage, please see: > This is my formula, however my answer is very different from 4096!

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 the above dimension. Wouldn't be better to include directly the Name in the fact (the dimension is referenced only one time by the fact)? And my second question is am I expected to create an MDX query manually in the case of the name directly included in the fact ? Won(t be the queries more difficult to write? thank you.

Is storing them in a [CUBE](https://www.postgresql.org/docs/current/static/cube.html) type field and then getting euclidean distance, (using ``) the only way? I can not save them as a list of floats, since i get: *"can't adapt type numpyndarray"* Can not cast a type bytea to type cube, either. Cube extension is installed, i can convert the list to type bytea using psycopg2, but nothing seems to work. What am i missing? I'm using postgresql 10.4, python 3, postgresapp 2.1.4 and postico

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 database I create it through the wizard and it now shows in Dimensions folder. Now the question is: how do I link the Dimension to the cube? I have attempted to do so in Dimension Usage but to no avail. Please point me in the right direction to accomplish the task. It now shows in Excel OLAP design, but does not filter by dates - most likely because it is not linked to the column in FactSales.

While trying to Process a cube i go this error "Error 133 The following system error occurred: No mapping between account names and security IDs was done. 0 0" i know its some user which is available in the roles have been removed from active directory. the annoying part in this is the error doesn't indicate which user has been removed from active directory, our SQL is setup as windows authentication. and i have to go and check one by one. is there a way to identify the user, so i remove it and continue with the processing

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 - Mondrian is one example solution which provides such translation. I was wondering if there are any available solutions to provide a translation in the other direction, i.e. from SQL to MDX. If not, what would be involved in creating such a functionality?

DBCC on the Cube fails(SQL version 2014 SP2, 12.0.5000.0). My XMLA query: Error message: Executing the query ... The DBCC element at line 7, column 88 (namespace https://schemas.microsoft.com/analysisservices/2016/engine) cannot appear under Envelope/Body/Execute/Command. Run complete I can find anything wrong with a query.Tried 2003 and 2014. According to books online, it should just work: https://learn.microsoft.com/en-us/sql/analysis-services/instances/database-consistency-checker-dbcc-for-analysis-services?view=sql-server-2017

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 Excel. In these clients I am able to run custom DMX queries The issues that I face are: - In SSMS linked server, the explorer shows only dimension tables, no measures. - Same in Visual Studio Data Tools (no measures are displayed in the explorer UI), except that here I also see "columns" for each table (while in SSMS tables don't show any lower level object to explore). While in Excel OLAP pivot table I see all dimensions and measures, I therefore have access to all the cube's objects - or at least the ones allowed for the login used in the connection string. For all of the above mentioned connections, I successfully used the same string, which is something like: Provider=MSOLAP.6;Data Source=biserver.domain.com:1564;Persist Security Info=True; User ID=myusername;Password=myPassword;Initial Catalog="MyCatalogue Name"; Location=biserver.domain.com:1564;MDX Compatibility=1;Safety Options=2; MDX Missing Member Mode=Error The remote AS is located out of my company domain. I have no additional info on how the instance is configured, nor possibility of having more permissions granted on the login, nor establish a VPN access. Question is: if Excel lets me see the data I need (measures and dimensions with their hierarchies), why am I unable to see the same with other clients, using the same connection string? Best would be if I could connect directly with SSMS (not using the linked server), by selecting "Analysis Services" server type on the initial prompt. Of course I tried, but given that only Windows Authentication is allowed there, even if I pass the entire string on the "Additional Connection Parameters" tab, I always receive the following error: A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient). No such host is known (System) I also tried to create a local Windows login with same user and pw and open SSMS as that login, but of course that didn't work. Any help is really appreciated. Don't know if this can help: we have no local AS instances running on our servers. SQL Server Browser is running Thanks

I am trying to build an SSRS report with SSAS as the data source. It has 4 cascading parameters. I used the SSRS Query designer to generate MDX because I am not very familiar with Cubes/MDX. It works but takes too long to render the report (3 minutes or more in both Visual Studio and on the Reporting Server). I also tried to generate MDX using Excel OLAP Pivot table extensions and modified the MDX to use cascading parameters, but the performance isn't getting any better. If I use SQL queries and SQL server as the data source for the same report, I get the output in a few seconds. But implementing this using cubes should make this scale much better. Does anyone know what steps I can take to improve the performance of this report? This is the MDX from SSRS query designer - SELECT NON EMPTY { [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, NON EMPTY {{[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]} * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS, {[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]} * {[DimOrg].[DepartmentName].[All]}, ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS * [DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimOrgFacilityName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[SomeFlag].&[True] } ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS FROM ( SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS FROM [DW]))))))) WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED), [DimLoadDate].[LoadDate].currentmember ), IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS I built another SSRS report using cubes with the same tables with a higher granularity - used 3 parameters instead of 4 and that worked faster (only a few seconds) for the report generation. Here is the MDX of that - SELECT NON EMPTY { [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, NON EMPTY {{[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * {[DimLoadDate].[RunDate].[All]}, {[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]}, ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS *[DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [DimOrg].[FacilityName].&[someValue] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[SomeFlag].&[True] } ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS FROM ( SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS FROM [DW]))))))) WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED), [DimLoadDate].[LoadDate].currentmember ), IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Thanks, Gopika

My apologies if this is not the correct forum to be asking this in. I am having trouble finding information regarding the "Optimize Response" connection string attribute. The most promising source of information I have found is [here](https://learn.microsoft.com/en-us/sql/analysis-services/instances/connection-string-properties-analysis-services?view=sql-server-2017) , but it doesn't really make clear to me what the settings are, or how it will really impact an MDX query on the server side. There are two cases for optimization? One is to use the "NormalTupleSet", which I can't even find what that is anywhere. The other is for when the slicers are empty. What do each of these really do? Would a client use the second option for empty slicers, when they are instead passing a subquery to the FROM clause? Thank you for any help anyone can give me in this regard, especially if you can point me to accessible resources on the topic.

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 the cube or do I need to process the entire cube every time (which is processing all the dimensions) ?

I am new to SSAS. Here's a cube which is rebuilt by a SSIS package everyday but it seems to cause duplication. We manually 'Process' the cube using the GUI, it then updates this table with the correct date and time and the data is correct after we do this: SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES However, we have been trying to automate the 'Process' at cube level and have added this code to a SQL Agent Job which runs as type = SQL Server Analysis Services Command and as the SQL Server Agent Service Account: DBNAME CUBENAME ProcessFull UseExisting This does NOT fix the cube data and also doesn't update the data in table: SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES Is there anything else I can check or do to get the MDX query to work? I've also used the same code in an SSMS window to process the dimensions, then the measure group and then the ProcessFull on the Cube. To no avail. Any help will be appreciated.