Within the MDX "Action expression" of a url-action I need to replace some text from a string that is used in building the URL.
replace("hellowhatever","what","")
The above syntax errors out, is this type of formatting possible with MDX action expressions?
I have employee members like
"firstname , secondname" in my dimension
i want remove , and display it like "firstname secondname"
i can do the same in sql server but i have to do it in mdx
Hi,
I am having the requirement where I have to use string as measures. Is this possible in Analysis services 2K.
Any help on this will be greatly appreciated
I'm using SQL-Server 2008, Visual Studio 2013. I've got created Linked Object (Linked Measure) in Cube2 from Cube1. Everything was fine, but I edited Measure in Cube1, as I found documentation there is no ability to refresh Linked Objects so I deleted and recreated Linked Measure on Cube2. After It I can't process Cube2, receiving following errors:
MdxScript(Cube2) (10, 24) The dimension '[Dim]' was not found in the cube when the string, [Dim], was parsed.The END SCOPE statement does not match the opening SCOPE statement.
Is it possible to use a DMX query as an action in an OLAP cube? So when someone clicked on a product when browsing the cube, it called my DMX query with a product parameter, and the query would return a listing of associated products? I'm not asking how to do the query, I already have that, but just wondering if it's possible to even do that. Thanks!
View 3 Replies View RelatedI 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]....
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,
I have number of users which are under 6 Groups. We have 4 drill through actions. Now I would like to restrict 2 drill through actions for only some groups.
View 2 Replies View RelatedI want to create a commandline action. It is my understanding that commandline actions can only be created using ASSL. I cannot find the details required to build the xmla. My ultimate goal is to be able to open a file from a unc path via a cube action.
Using a URL action will not work since the path does not start with http or https. Prepending 'file:' to the unc, with however many hashes you like, does not work with the URL action either. I am hoping I can pass the unc to the commandline and render the file. In any case, it looks like the information needed to create a commandline action is not currently available.
I have table with 3 columns: record_id (int), car (varchar(50)), amount (decimal(18,4)).
I created dimension for drillthrough action. Also I set Amount's column FormatString to '##0.00' but when I use Drillthough column Amount values is shown like:
.0000
15.7900
18.4100
Is there any possibility to change that values would be shown as follow:
0,00
15,7900
18,4100
Guys ... got a puzzle kinda problem am stuck up with so asking for your help (after all .. you are the gurus)... What i need to do is append Src. to any column name that comes in the expression. Here we go ... and the last query is part of what I was coming up with ... not exactly working :) Create Table ColumnNames(ColumnName varchar(256))goInsert into ColumnNamesSelect 'name'UNIONSelect 'dbid'UNIONSelect 'sid'UNIONSelect 'mode'UNIONSelect 'status'UNIONSelect 'status2'UNIONSelect 'crdate'UNIONSelect 'reserved'UNIONSelect 'category'UNIONSelect 'cmptlevel'UNIONSelect 'filename'UNIONSelect 'version'goDeclare @Expression varchar(256)Select @Expression = 'dbid = 1 AND cmptlevel = 100' Select replace(@Expression,ColumnName,'Src.'+ColumnName) from ColumnNames Where charindex('Src.',replace(@Expression,ColumnName,'S rc.'+ColumnName)) <> 0
View 4 Replies View RelatedI have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
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?
I am writing an SQL query to find an replace data in a column. I have a table that is filled with
C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata
I would like to replace the c:usersXXXXX part with %userprofile%
The end result would be %userprofile%appdata
I know how to dot the replacement in powershell. it's quite easy
-replace "c:users[^]+","%userprofile%"
Basically how would transfer this into SQL...
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
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?
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 Relatedi want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!
View 2 Replies View Relatedhow 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])
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]...
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.
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.....
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..
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 RelatedI 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.
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?
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"?
I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty!
Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK.
But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up
I'm aware that I could do this with SQL but this is not the point...
Any ideas?
BR Jompe
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.
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?
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.
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.
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
[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?