Analysis :: How To Update Cube To Get MDX Working Fine

Oct 5, 2015

I have been trying to get the ValueR column of the following query through the MDX but instead getting ValueW as output of the MDX

    select 
    exp(Log(sum(MTMROR)+ 1 ))-1 as ValueW,
    exp(sum(Log(MTMROR + 1)))-1 as ValueR
    from 
    Temp_Performance
    where Rundate in ('2015-03-01','2015-03-02')

MDX written for the above query is

    With 
    Member [Measures].[LogValuePre]
    as ([Measures].[MTMROR] + 1)
    Member [Measures].[LogValuePre1]
    as VBA![LOG]([Measures].[LogValuePre])

[Code] ...

[MTMROR] measure has the aggregate function Sum. What i can get from this behavior is MDX is first aggregating the result and default aggregation function is Sum. When i try to see the value with more granular data by having the date dimensions on the row (un-commenting the date dimension) i get the correct log and exp log values. Its showing the correct value as date dimension is most granular level in the fact table. While trying to get the data at less granular level(Fund level), getting the sum function applied automatically.   

If i set AggregateFunction to none in the cube structure, i get null as the output.

How could i apply the log function before the sum function in the [MTMROR] measure?

View 4 Replies


ADVERTISEMENT

Analysis :: Timeline Is Not Working With Multidimensional Cube In Excel 2013

Jul 11, 2014

I have created one Multidimensional CUBE, while browsing(Analyze in Excel) it in Excel, I am unable to create TimeLine Slicer.It is giving me following error as: "We can't create Timeline for this report because it doesn't have field formatted as Date".

I have Dim_Date having Date as a column of Day level granularity. In PowerPivot, we do mark Dim_Date Table as a 'Date Table'.In the same way, do we have to set anything here so that Excel will come to the Date format for TimeLine Slicer?

View 7 Replies View Related

Perspectives Defined In Analysis Cube Not Working In Report Builder

Feb 20, 2007

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

Help!!!!

View 3 Replies View Related

Analysis :: Deploying In Order To Update The Cube?

Nov 2, 2015

Scenario: [**tableA**] plus [**tableDim1**] plus [**tableDim2**]. I create a DSW, a cube I deploy the [@@CUBE@@]..so connect the data using an excel file that show the data as [¬¬dashboard¬¬].

It works.

My question is: after three days the [**tableA**] is populated with new rows. In order to allow my colleagues to see the new rows I deploy the [@@CUBE@@] again. My colleagues can see the new rows in the [¬¬dashboard¬¬]. 

It works, ok. But do I really need to deploy the [@@CUBE@@] every time or it should be update automatically when you, for example, refresh the data in Excel. Do I miss something?

View 3 Replies View Related

Update An Analysis Services Cube From Stored Proc?

Jul 1, 2004

I'd like to be able to update an Analysis Services cube through a stored proc.

Currently I can:
- Make a DTS package that updates the cube
- run xp_cmdshell which runs dtsrun which runs the DTS package.

That is messy, easily broken, and hard to get good error info when an error occurs. Is there a better route?

View 1 Replies View Related

Analysis :: Application With Cube - Cannot Update Database Object

Jun 19, 2015

We have an application that takes an existing cube, clones it and then updates it in C#.

Database dbTarget = dbSource.Clone();
dbTarget.Name = databaseName_Target;
dbTarget.ID = databaseName_Target;
dbTarget.DataSourceImpersonationInfo = new ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount);
sSAS_CalculationServer.Databases.Add(dbTarget);
dbTarget.Update(UpdateOptions.ExpandFull);

We are receiving the following error when trying to Update the cube (the last line of code)Cannot update the 'Database' object 'DB Cube_Temp', it needs to be part of a connected Server object.

View 2 Replies View Related

Analysis :: Update Actual Database Tables With Changes Made In Cube

Oct 7, 2015

I am very new to SSAS. I have two queries:

1) As per my project requirement, if the changes in SSAS cube are approved, they should be committed back to the actual SQL Server 2012 tables. Is that possible, if yes how?

2) For rolling back to original data I truncate the relevant writeback table and process the cube.

View 4 Replies View Related

Analysis :: Dynamic Values In XMLA To Incrementally Update Cube Processing

Nov 10, 2015

I am trying to incrementally update a Cube to get near real time data for the end users. Currently we have a Sql server agent Job that does a FullProcess on the Cube. The Cube consists of a single Measure group which is simply one named query containing inner joins of all the dimensions and fact tables in the underlying relational database. The end users have a lot to upload during the day and they would like us to refresh the cube (near real time) to ensure the adjustments are loaded so that they could reconcile their daily PnLs. We have a MeasureId added which is an auto increment column in the Measures table.

I am trying to schedule the below XMLA query in Sql server agent Job and schedule it to run every 15mins or even less (if possible). However it seems to be not working and keep throwing all sorts of errors.

DECLARE @LastMeasureId AS INT, @myXMLA nvarchar(max)
SELECT @LastMeasureId = "[Measures].[Maximum Measures Id]" FROM
OpenRowset(
'MSOLAP',
'DATA SOURCE=L68F728326574; Initial Catalog=GMDR;',
'SELECT NON EMPTY {[Measures].[Maximum Measures Id]} ON COLUMNS FROM [GMDR]');

[Code] ....

View 2 Replies View Related

Analysis :: Creating Cube With AMO - Cube Has No Measure Groups?

May 19, 2015

I have problems creating a cube with AMO.

I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).

If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)

The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629

I can't find any documentation about this (or any other) error code in Microsoft documentation.

Here's my Code:

Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");

[code]....

View 2 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Before It Was Working Fine..but Now...

Mar 27, 2008

Hi,

I am having 'INSERT STATEMENT CONFLICTED' error since I try to configure sql server 2005 Full Text Search for my database. Before everything was going fine but after doing some changings I mess up my all DB's. None of my database is working. The error only occur while inserting the records using web form. But I can insert directly from Management Studio interface. here is part of my error:

ERROR:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Dept_LegalLaw_Dept_LegalMinistries1". The conflict occurred in database "LegalDB", table "dbo.Dept_LegalMinistries", column 'RegID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
...............AND SO ON


 INSERT QUERY:

INSERT INTO Dept_LegalLaw (
Agreement,Amendment,Attachment,Html,ID,IssueNo,LawDate,LawID,LawNo,Mistakes,Name,NameSearch,Pages,RegID,Scanned,SerialNo,Path) VALUES (
'1',NULL,NULL,'1','C0667','2666','5/26/2004 12:00:00 AM','2','22',NULL,N'تعميم بشأن عطلة ذكرى المولد النبوي الشريÙ?',N'مرسوم اميري رقم 29 لسنة 2001 بانشاء لجنة الاسكان والاعمار','1','RAC,',NULL,'5556','RCAB4505.htm')
--RETURN THE NEW IDENTITY VALUE
SELECT SCOPE_IDENTITY() MediaID

I really need urgent help. that how i can reverse this thing back.

Thanks

View 1 Replies View Related

SSIS Package Run Fine But Not Working When Running In A Job

Mar 15, 2006



I have a 64bit Ent Ed, Created a SSIS package, deployed it, its running when running from sql server but not running as a job

View 1 Replies View Related

Query Working Fine On 2000 But Not On 2005

Apr 25, 2008

Hello to all,

if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.

When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.

Here it is:







Code Snippet

SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2)



NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest)
Any ideas?

Thanx

Alex

View 7 Replies View Related

Opinion On Cube Analysis

Feb 7, 2008

Hi,

I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?

View 6 Replies View Related

Analysis Services: Deploy A Cube

Jun 21, 2007

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. Thanks

View 1 Replies View Related

Analysis Services Hiearchy Cube

Jun 30, 2007

I setup Hiarchy in the dimentions of my cube, however when I go and look at it via proclarity, I can't see the hiearchy there.

I have one table that has:

Category
Subcategory
Partnumber

And I have the hiearchy set from Subcategory to partnumber.

Then I go into proclarity and limit the category by "my catname" and I still see the 8000 partnumbers in the list.

Any ideas?

View 1 Replies View Related

Q: Analysis Services Cube Design

Jul 20, 2005

I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach

View 2 Replies View Related

Analysis :: Create Cube With 2 Table

May 6, 2015

i want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!

View 2 Replies View Related

Analysis :: Using Calculations In SSAS Cube?

May 21, 2015

how can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.

[measures].[test]'
select 
[measures].[abc] on 0,
[xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1
from
(
select
(tail([month].[month].[month].members,6))on 0
from
[cube])

View 3 Replies View Related

Analysis :: Measure Value Needs To Be Manipulated In Cube

May 18, 2015

The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.

DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3

[code]...

View 4 Replies View Related

Analysis :: How Is Cube Physically Stored

Nov 23, 2015

I know where the data files are etc, but what is the physical structure of a cube like on disk?

Logically we see the data as a star. Is the physical file akin to a star also? Or is a single file a measure group containing all the required member and measure data - thereby eliminating the need for physical join operators.

View 2 Replies View Related

Analysis :: Implement Security In Cube

Oct 14, 2015

I am planning to implement the security in my cube..

Total sales is 1000..

Customer Sales
A 100
B 200
C 300
D 400

Total Sales 1000

When i want to give access to the customer A, he can capabule of viewing only A value 100... But the problem is A is able to see total sales 1000.. How can i restrict the sales a value to 100 in the cube.. Where i have to set the property to fix this issue.....

View 3 Replies View Related

Analysis :: Only One Fact Table Per Cube?

Nov 22, 2015

Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now. 

According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..

View 3 Replies View Related

Analysis :: Distinct Count On Cube

Jun 12, 2006

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

View 17 Replies View Related

Analysis :: Cube XML Is Different Than Design View

Aug 4, 2015

I have a cube. Its xml is different at some point than its design view. Suppose for some dimension and its attributes, source table is different than what it showing in the properties window for them..

Is this possible? How to read cube xml because there are repeating tags in it. There are two type if dimension tags.. one has only attributed and other has all properties.

View 7 Replies View Related

Analysis :: More Than One Cube In One SSAS Database

Dec 13, 2010

Is it possible to have more than one cube under one SSAS database? For example I have a database "Test" and in this the cube exist is "TestCube", is iit possible to deploy another cube "TestCube2" under the Test databse?

If yes then what is the process to do that, the reason I am asing is there are some common dimensions used n both the cubes and I am not sure what is the best way so that I can use the shared dimension?

View 6 Replies View Related

Analysis :: Cube Attributes Translations

Nov 3, 2015

How can we select translated text of cube translations, like measures, dimensions, dimension attributes. I have below query which return translations of dimension attribute's memebers

WITH
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]

I am getting translations of product category members but I want to get translated text of "Category"?

View 2 Replies View Related

Analysis Services Help - Dim Builds, But Cube Fails.

Dec 8, 2005

If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.

Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.

I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.

Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.

View 5 Replies View Related

Analysis :: AttributeKey Not Found When Processing The Cube

Apr 21, 2015

I have designed a cube. It has two fact tables and some dimensions. Fact table to fact table is many to many relationship.

For example

FactMain
DataKey(PK), StartDateKey, PostCodeKey, TotalCost
FactBridge
DataKey(FK), ProductKey(FK), Position  - PrimaryKey on DataKey + ProductKey + Position
DimProduct
ProductKey(PK), ProductCode

Cube is built successfully, processed successfully.When I try to process the cube from agent job, I am getting error "Attribute key not found: tablename, value..." I have added a job step to run AnalysisServices Command. I have taken the command from cube process script(taken from manually process the cube and take script generated). I used ProcessAffectedObjects = "true" in the script. When I checked the tables, the key does exist. Why am I getting this error?

View 5 Replies View Related

Analysis :: Deploy Cube Without Overwriting Database

Jun 30, 2015

I have a TEST database which contains 1 cube.

I have created another cube and I want to add this cube to the TEST database also.

However when I deploy the cube it overwrites the database with the new cube and the old cube is gone.

How can I have the 2 cubes within the TEST database.

View 2 Replies View Related

Analysis :: Add Population Data In SSAS Cube

Aug 7, 2015

I want to implement population data in sales cube.

Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.

We want to add population data in fact table.

View 3 Replies View Related

Analysis :: Cube Restoration In Different Storage Location

Aug 6, 2015

I am in process of automating a cube migration from SSMS 2008 to SSMS 2012.

In this process iam deleting the existing cube databases and restoring them on a different location on the same server.
When i try to execute the restore command or restore using UI i get a wierd error message like this below:
 
"TITLE: Microsoft SQL Server Management Studio
------------------------------
 File system error: The following error occurred while opening the file 'DrivePath3F9D4D128D5E417FA6F2[CUBEDBNamepath].fact.map'.
Server: The current operation was cancelled because another operation in the transaction failed.
(Microsoft.AnalysisServices)

[Code] ....

View 5 Replies View Related

Analysis :: ABF File - Restoring SSAS Cube

Oct 25, 2010

I have a .abf file, which I am attempting to restore. I go to Management Studio and attempt to restore the cube.

However, whenever I attempt to restore the following error message occurs:

"File 'C:/.......' specified in restore command is damaged or is not an AS backup file.

The following error occured:

Access is denied (Microsoft SQL Server 2008  R2 Analysis Services)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved